[VBA] How to Attach the Currently Open File in Outlook

Environment

• Windows Specification: Windows 10 Pro
• Version: 20H2

Background

I was able to send emails using VBA, but I also wanted to attach the file I was currently working on. I studied how to do this and found the solution.

Using “Attachments.Add”

Using Attachments.Add worked perfectly. Here is the code.

Sub SendEmail()

Dim objOutlook As Outlook.Application
Dim objMail As Outlook.MailItem
Dim wsMail As Worksheet

Set objOutlook = New Outlook.Application
Set wsMail = ThisWorkbook.Sheets(1)
Set objMail = objOutlook.CreateItem(olMailItem)

ActiveWorkbook.Save

    With wsMail
        objMail.To = .Range("A6") ' Email To
        objMail.CC = .Range("B6") ' CC
        objMail.Subject = .Range("C6").Value   ' Subject
        objMail.BodyFormat = olFormatPlain     ' Email Format
        objMail.Body = .Range("D6").Value  ' Body
        objMail.Attachments.Add ThisWorkbook.Path & "\" & ThisWorkbook.Name
        objMail.Send
    End With
  
End Sub

Code Explanation

(Blank lines are not counted in the line numbers below.)

• Line 1: Defines the macro name.
• Lines 2-4: Declares variables.
• Lines 5-7: Sets the variables. In Line 5, I reference “Sheet1” because that is where I have written information like the recipient address and subject.
• Line 8: Overwrites and saves the file. This ensures the attached file is up to date.
• Line 9: Starts the With statement. Although I reference cells for information, you can also enter addresses or text directly after the “=” sign.
• Line 10: “To” address. If there are multiple recipients, you can connect them with “&”.
• Line 11: “CC” address.
• Line 12: Email Subject.
• Line 13: Email Format.
• Line 14: Email Body.
• Line 15: This is the command to attach the file. It specifies the current workbook’s path and name.
• Line 16: Command to send the email.
• Line 17: Ends the With statement.
• Line 18: Ends the macro.

Future Improvements

The base program above works well. For further customization, you might want to add a message box to add comments or include an IF statement to prevent accidental transmission.

Lessons Learned

At first, I tried using SaveAs and explored various methods. I attempted to save the file in a different location using SaveAs and then send that new file, but it did not go well. There is probably a way to do it with SaveAs, but Attachments.Add proved to be the simpler solution for this purpose.