Wauvel

Excel like a finance pro.

← All VBA snippets

Email the workbook (or a PDF of it) from Excel

The last mile of every monthly routine, and the most-asked VBA task in finance.

1What it does

Automating Outlook from Excel is the request that comes up more than any other, and the version most people find online breaks on somebody else's machine. Two things make it robust. Use LATE binding — `CreateObject("Outlook.Application")` rather than a reference to a specific Outlook version — so it runs wherever Outlook is installed. And use `.Display` rather than `.Send` while you're building it, so you see the draft before it goes; switching to `.Send` is a one-word change you make when you trust it. Exporting the sheet as a PDF first means you're sending something nobody can accidentally edit.

2The code

Sub EmailReportAsPdf()
    Dim outApp As Object, mail As Object
    Dim pdfPath As String

    pdfPath = Environ$("TEMP") & Application.PathSeparator & _
              "Report " & Format(Date, "yyyy-mm-dd") & ".pdf"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfPath, _
                                    Quality:=xlQualityStandard

    ' LATE binding — no reference to a specific Outlook version, so this
    ' runs on a machine with a different Office build.
    On Error Resume Next
    Set outApp = CreateObject("Outlook.Application")
    On Error GoTo 0
    If outApp Is Nothing Then
        MsgBox "Outlook isn't available on this machine.", vbExclamation
        Exit Sub
    End If

    Set mail = outApp.CreateItem(0)          ' 0 = olMailItem
    With mail
        .To = "owner@example.com"
        .Subject = "Monthly report — " & Format(Date, "mmmm yyyy")
        .Body = "Attached is this month's report." & vbNewLine & vbNewLine & _
                "Anything look wrong, just reply."
        .Attachments.Add pdfPath
        .Display                              ' .Send when you trust it
    End With

    Set mail = Nothing
    Set outApp = Nothing
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Send the monthly pack without rebuilding the email every time.
  • Attach a PDF so the figures can't be edited in transit.
  • Draft one email per department from a list, ready to review.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Dim outApp As Outlook.Application ' needs a reference

Both work on the machine they were written on. Only one of them works everywhere else.

5Pitfalls

EARLY binding — `Dim outApp As Outlook.Application` with a library reference — fails on any machine with a different Outlook version, and the error names a missing reference rather than the real cause.

Fix: `CreateObject("Outlook.Application")` with `Dim … As Object`, as above.

`.Send` used during development, so half-finished emails actually go out.

Fix: `.Display` until you trust it. Changing one word is the last step, not the first.

The attachment is missing because the PDF path had a character Windows rejects.

Fix: Build filenames from yyyy-mm-dd and avoid slashes and colons — `Format(Date, "dd/mm/yyyy")` produces an illegal name.

Outlook's security prompt blocks the send, or IT policy blocks automation entirely.

Fix: `.Display` sidesteps the prompt because the user sends it. If policy blocks CreateObject there's no code-side fix — that's a conversation with IT.

6No-code alternatives

  • A mail-merge or scheduled report tool — Better for sending to many recipients on a schedule; this is for one pack from a file you already have open.
  • Save to a shared folder and send a link — Avoids attachments entirely, and everyone reads the same version.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.