Create an Email with Attachment using VBA and Outlook
In Send an email with VBA and Outlook we saw how to create an email using VBA and Outlook. Here we show how the macro can be extended to add an attachment to the email using the Code VBA add-in.
Below is a part of the code from the previous article we will extend. (Note: the complete code is at the bottom of the article.)
With mimEmail
.To = strEmailAddess
.Subject = "Taskforce meeting"
.Body = strBody
'<cursor>'
.Display
End With
1. Place the cursor in the code after .Body = strBody
. (in code above)
2. On the Code VBA toolbar select Object » mimEmail » Attachments » Add...
A dialog pops up to let you specify the attachment.
In the dialog we select in the Source property popup to have a new variable strSource
created.
Having a variable makes the solution more flexible because it delays the decision which file to attach.
Finally, specify the filepath. An easy way to specify the filename
after strBody =
is using the tool under Code VBA toolbar > Name > File Select which opens a file selection dialog.
Sub EmailDemo()
Dim strEmailAddess As String
Dim strFirstName As String
Dim strLastName As String
Dim appOutlook As Outlook.Application: Set appOutlook = New Outlook.Application
strEmailAddess = "t.axen@rivm.nl"
strFirstName = "Thomas"
strLastName = "Axen"
Dim mimEmail As Outlook.MailItem
Dim strBody As String: strBody = "Dear " & strFirstName & " " & strLastName & "," & vbNewLine & _
"Just a reminder that our meeting to discuss the environment " & _
" is later this week. See you Thursday!"
Set mimEmail = appOutlook.CreateItem(olMailItem)
With mimEmail
.To = strEmailAddess
.Subject = "Taskforce meeting"
.Body = strBody
Dim strSource As String: strSource = "C:\temp\FileToSend.xlsx"
Dim att As Outlook.Attachment
Set att = mimEmail.Attachments.Add(Source:=strSource)
.Display
End With
End Sub
The resulting email.