Sending a Workbook as an Email Attachment in Excel VBA

The SendMail() method allows you to send the active workbook as an email attachment easily. The example email addresses used below should be adjusted to your own addresses so you can verify the email delivery.

Example 1: Sending to a Single Recipient

Sub SimpleSend1()
    ThisWorkbook.SendMail "moyofranck@gmail.com", "Test"
End Sub

The user must choose whether to ALLOW or DENY access to Outlook. This security prompt appears for many subsequent applications that try to access Outlook elements.

If permission is granted, the email with the workbook as an attachment is placed into the Outlook Outbox.

Note that in Excel 2016, the subject line was not correctly passed with SendMail(). This issue was fixed in Excel 2019. Regardless, the actual sending of the workbook as an attachment works correctly in all versions.

Example 2: Sending to Multiple Recipients

Sub SimpleSend2()
    Dim outlookApp As Object
    Dim mailItem As Object
    Dim recipients As String
    recipients = "moyo.yannick@gmail.com; mbeu.borel@gmail.com"
    Set outlookApp = CreateObject("Outlook.Application")
    Set mailItem = outlookApp.CreateItem(0)
    With mailItem
        .To = recipients
        .Subject = "Test"
        .Body = "Ceci est un test d'envoi à plusieurs destinataires via Outlook."
        .Display ' Ou .Send pour envoi direct
    End With
End Sub

Explanation of Both Procedures:

• The SendMail() method has one required and two optional parameters.
• The first parameter specifies the recipient(s):
  • A single string for one recipient.
  • An array of strings for multiple recipients.
• The second parameter allows you to specify the email subject.
• The optional third parameter, if set to True, requests a read receipt. The success of this request depends on the recipient’s email client supporting and honoring read receipts.
• The generated email, including the active workbook as an attachment, is placed into the Outlook Outbox for sending.