Attach file to e-mail using excel macro

[JT1977]

I'm writing a macro in Excel to connect to a button on an MS Excel 2007
spreadsheet. When clicked the button copies some data from the file and uses
the data to create a new .txt file. Since the macro is creating the file I
can control the file name and storage location. Once created I want to
e-mail the text file to a standard e-mail address. I'm using the code below
to open an e-mail editor and compose the message. I'm using some variables
to to dictate the list of receipients, subject line, etc. based on some
criteria in the file itself.

ActiveWorkbook.EnvelopeVisible = True


With ActiveSheet.MailEnvelope
.Introduction = Range("T22")
.Item.To = Range("T15")
.Item.CC = Range("T16")
.Item.Subject = "CST CARRIERS"
.Item.Send
End With

What I would like to do is be able to attach the .txt file to the e-mail
through the code of the macro. I was hoping there was some simple code like
..Item.Attachment = "blah" that would attach the file. I can't find the code
to do this. Can you tell me what the code is to do this?

I know the e-mail generated by the above code is capable of including
attachments because when I put a "stop" line in the code and run it, the
e-mail editor that opens has a button to attach files. If I end the debugger
at this point I can manually seacrh for and attach the file to the note using
this button.

 

[arjen van...]

Try something like this:

Sub NewMail()

ActiveWorkbook.EnvelopeVisible = True

Dim strAttachment As String
strAttachment = "C:\customers.txt"

With ActiveSheet.MailEnvelope
.Introduction = Range("T22")
.Item.To = Range("T15")
.Item.CC = Range("T16")
.Item.Subject = "CST CARRIERS"
.Item.Send
.Item.Attachments.Add strAttachment
End With

End Sub

Though I think creating the string variable isn't essential, you could just
put the path directly after .Add

 

[JT1977]

Thanks for the help. This was what I was looking for. However if I execute
this code multiple times it is almost as if the file to be attached is saved
to a clipboard. Each subsequent time I execute the code the previously
attached file is also added. i.e. If I run the code a second time it not
only attaches the file selected by the code but also the file selected by the
code when run previously. Is there a way to "clear" the clipboard so only
the one file is attached?