Macro to Email the Workbook Through Outlook

These VBA macros create an Outlook email with the workbook, or the active sheet as a PDF, already attached. You check it and click Send.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Email the workbook

Sub EmailWorkbook()
    Dim m As Object
    If ActiveWorkbook.Path = "" Then MsgBox "Save the workbook first.": Exit Sub
    ActiveWorkbook.Save
    Set m = CreateObject("Outlook.Application").CreateItem(0)
    With m
        .To = "someone@example.com"
        .Subject = "Report: " & ActiveWorkbook.Name
        .Body = "Hi," & vbLf & vbLf & "Please find the report attached." & vbLf & vbLf & "Regards"
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
End Sub

.Display opens the email so you can check it. Change it to .Send to send immediately, but test with your own address first.

Email the active sheet as a PDF

Sub EmailSheetAsPDF()
    Dim f As String, m As Object
    f = Environ("TEMP") & "\" & ActiveSheet.Name & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=f
    Set m = CreateObject("Outlook.Application").CreateItem(0)
    With m
        .To = "someone@example.com"
        .Subject = ActiveSheet.Name
        .Body = "Please find the PDF attached."
        .Attachments.Add f
        .Display
    End With
End Sub

The PDF is saved in Windows’ temporary folder, so your folders don’t fill up with copies.

Without a macro

  1. Go to File → Share (or File → Save & Send in older versions).
  2. Choose Send as attachment or Send as PDF.

Things to know

  • These macros need classic Outlook for Windows. The new Outlook for Windows and Outlook on the web don’t support macros controlling them.
  • Outlook may show a security prompt when a macro creates an email. Choose Allow.
  • Separate several addresses with semicolons: .To = "a@x.com; b@y.com". Use .CC and .BCC for copies.

Related macros