Sending excel email, then deleting it

[Matt]

Hi all:

I have an excel workbook that has 18 worksheets. After I have completed
a task, I copy 2 of the worksheets to a new workbook to email to a
person that stores it in an access database (I'll call her the database
keeper.) The database keeper wants the subject line to read a certain
way and so I have written the code in the following way...I have
written the code so that after I copy the two worksheets I need to a
new workbook, I remove all the equations (with a copy, paste special
value, values) and then save the workbook to the c:\ directory. I then
put that into an email using outlook and send it to the database
keeper. After that, I want to delete this new file on the c:\ I have
just made as I don't need it anymore. Here is where my problem arises.
After I send the email, I have a kill myfile code line. When the
database keeper opens the file, a box comes up that reads 'The system
cannot find the file specified.' If I take out the kill myfile code
line, the database keeper can open the file (but then the file stays on
my harddrive and I don't want that to happen.) Is there anyway, that I
can rewrite this code or alter this code so that I can delete this file
but still get it in the attachment. Below is the code that I am
referring to. I have added all the code (which is run from a command
button) as I am not sure what someone needs to help out. Any help is
greatly appreciated. Thanks in advance. ~Matt

The code...

Private Sub CommandButton1_Click()
' Macro recorded 7/13/2006 by Matt Sonnier
Dim sbj As String
Dim sCustomer As String
Dim sField As String
Dim sWellNo As String
Dim sSO_No As String
Dim sTreatment As String
Dim sPE_EngrName As String
Dim MyFile As String
Dim myOlApp As Variant
Dim myitem As Variant
Dim myAttachments As Variant
Dim myAttachment As Variant
Dim olMailItem As Variant
Dim email_msg As String
Dim email_address As String
Set myOlApp = CreateObject("Outlook.Application")
Set myitem = myOlApp.CreateItem(olMailItem)


' Finds cell named Customer, Field, Well, PE_SalesOrderNo, Treatment,
PE_EngrName and saves the contents in memory
Sheets("SC Database").Activate
Application.Goto Reference:="Customer"
sCustomer = ActiveSheet.Range("Customer")
'MsgBox "Is this correct?1", vbOKOnly, "Is this correct?"
sField = ActiveSheet.Range("Field")
sWellNo = ActiveSheet.Range("Well")
sSO_No = ActiveSheet.Range("PE_SalesOrderNo")
sTreatment = ActiveSheet.Range("Treatment")
sPeEngr1 = ActiveSheet.Range("PeEngr1")
' This puts the name of the subject line into one line.
sbj = sCustomer & "- " & sField & " " & sWellNo & " (SO #" & sSO_No
& ") " & sTreatment

' Copies the "Sum" and (SC Database" worksheet to a new workbook
Sheets(Array("Sum", "SC Database")).Select
Sheets(Array("Sum", "SC Database")).Copy
ActiveWorkbook.Sheets("SC Database").Select
ActiveWorkbook.Sheets("Sum").Select
ActiveWorkbook.Sheets("SC Database").Select
ActiveWorkbook.Sheets("Sum").Select
ActiveSheet.Range("A1:J58").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
Application.CutCopyMode = False
Sheets("SC Database").Select
ActiveSheet.Range("A1:G92").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

Application.CutCopyMode = False
ActiveSheet.Range("A1").Select
ActiveWorkbook.Sheets("Sum").Select
ActiveSheet.Range("A1").Select
Sheets("SC Database").Visible = True

' Saves the open workbook to C:\ directory
ActiveWorkbook.SaveAs Filename:="c:\" & sbj & ".xls",
FileFormat:=xlNormal
' Closes New Active Workbook
ActiveWorkbook.Close
' Add a message box to open Outlook!
MsgBox "Make sure Microsoft Outlook is open." & vbCrLf _
& "If it isn't, open it before hitting the ok button!" _
, vbOKOnly, "Is Microsoft Outlook open?"

' The following code was added by Mike Pettee on 18Jul06
' This gets the information for the body of the email.
email_msg = "I have finished the report."

email_address = InputBox("Input the email address you" & vbCrLf &
"wish to send attachment to.", ,")
myitem.to = email_address
myitem.Subject = sbj
myitem.body = email_msg
' Inserts the attachment file that is created
myitem.Attachments.Add "c:\" & sbj & ".xls", olByValue, ,
"Attachment"
With myitem
.send
End With
On Error Resume Next 'On hitting errors, code resumes next code
'Sends a message box saying the email was sent!
MsgBox "The email was sent to:" & vbCrLf & vbCrLf & email_address,
vbOKOnly, "The email was sent!"
' Deletes file on c:\ that was created
MyFile = "c:\" & sbj & ".xls"
Kill MyFile
ActiveWorkbook.Sheets("SC Database").Select
ActiveSheet.Range("A1").Select
ActiveWorkbook.Sheets("Input").Select
ActiveSheet.Range("A1").Select

End Sub

 

[Ron de Bruin]

Hi Matt

Maybe you have links in other cells outside the range

Test this

Sub Mail_SheetsArray_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets(Array("Sum", "SC Database")).Copy

Worksheets.Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False


Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub