How to add an attachment to an email using VBA in Excel

Question

I have the following code but it is not working. I am fairly new to VBA as well. The code works to populate the email template but as soon as I add the .Attachment.Add it does not work.

Sub CreateMail() Dim objOutlook As Object Dim objMail As Object Dim rngTo As Range Dim rngSubject As Range Dim rngBody As Range Set objOutlook = CreateObject("Outlook.Application") Set objMail = objOutlook.CreateItem(0) With ActiveSheet Set rngTo = .Range("E2") Set rngSubject = .Range("E3") Set rngBody = .Range("E4") .Attachments.Add "Z:\PHS 340B\Letters of Non-Compliance\..Resources\W9 Form\VPNA W-9 01 09 2017" End With With objMail .to = rngTo.Value .Subject = rngSubject.Value .Body = rngBody.Value .Display 'Instead of .Display, you can use .Send to send the email _ or .Save to save a copy in the drafts folder End With Set objOutlook = Nothing Set objMail = Nothing Set rngTo = Nothing Set rngSubject = Nothing Set rngBody = Nothing End Sub

The error says "Object doesn't support this property or method" — Twinkievizzio11
– Twinkievizzio11, Commented Feb 13, 2018 at 21:42

Ziggus · Accepted Answer · 2018-02-13 21:40:42Z

Try this:

Sub emailtest() Dim objOutlook As Object Dim objMail As Object Dim rngTo As Range Dim rngSubject As Range Dim rngBody As Range Set objOutlook = CreateObject("Outlook.Application") Set objMail = objOutlook.CreateItem(0) With ActiveSheet Set rngTo = .Range("E2") Set rngSubject = .Range("E3") Set rngBody = .Range("E4") End With With objMail .To = rngTo.Value .Subject = rngSubject.Value .Body = rngBody.Value .Attachments.Add "Z:\PHS 340B\Letters of Non-Compliance\..Resources\W9 Form\VPNA W-9 01 09 2017" .Display 'Instead of .Display, you can use .Send to send the email _ or .Save to save a copy in the drafts folder End With Set objOutlook = Nothing Set objMail = Nothing Set rngTo = Nothing Set rngSubject = Nothing Set rngBody = Nothing End Sub

You need to use the .Attachments.Add when working within Outlook not Excel.

What does that mean? I am using Outlook to create the email which works fine its only when I try to add an attachment.
@Twinkievizzio11 Try copying and pasting the code as I posted, it worked when I tested it. You had Attachments.Add within your "With Activesheet", you needed it under "With objMail". .
@Twinkievizzio11, please be sure to mark Ziggus's answer as the accepted one. Just click the checkmark below the down vote arrow on his post. Also, I wanted to add that the reason this works is because you mistakenly had the attachment being added under the wrong area of code. The object ActiveSheet doesn't support .Attachments.Add, so it needed to be moved to the With objMail area. Just a little clarification for anyone still confused. 😊

ASH · Accepted Answer · 2018-02-16 05:06:55Z

This simple script should illustrate the point of how to add attachments to an email, and then send the email.

Sub Mail_workbook_Outlook_1() 'Working in Excel 2000-2016 'This example send the last saved version of the Activeworkbook 'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .to = "[email protected]" .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ActiveWorkbook.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Send 'or use .Display End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End Sub

https://www.rondebruin.nl/win/s1/outlook/amail1.htm

Collectives™ on Stack Overflow

How to add an attachment to an email using VBA in Excel

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related