Attach file to e-mail using excel macro
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. |
Attach file to e-mail using excel macro
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 |
Attach file to e-mail using excel macro
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? "arjen van..." wrote: 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 |
All times are GMT +1. The time now is 02:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com