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...

menu add attachment to mailitem

A dialog pops up to let you specify the attachment.

attachment to mailitem builder dialog

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.

email with attachment