Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can you attach an e-mail into an excel worksheet? | Excel Worksheet Functions | |||
Mail worksheet in Body AND Attach File | Excel Programming | |||
e-Mail File out of Excel 2003 Macro | Excel Programming | |||
E-mail macro - how do I attach multiple attachments? | Excel Discussion (Misc queries) | |||
How do I attach an excel document to a word one for mail merge? | Excel Discussion (Misc queries) |