Expert guide

Email Excel Reports Automatically with Outlook VBA

Refresh, export and send the weekly report to every manager with one click

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: a macro refreshes the workbook (RefreshAll), exports the report sheet to PDF with ExportAsFixedFormat, creates an Outlook email with the PDF attached and a short HTML summary in the body, and sends or displays it. For per-manager versions, loop a distribution table, filter, export and send each one, logging the result. Classic Outlook is required; for scheduled cloud runs, use Power Automate. We build reporting pipelines like this as part of Excel reports and forms.

The weekly report is ready on Monday at 9. By 10 someone has refreshed it, saved a PDF, written an email, attached the file, forgotten one manager, resent it and filed the copy. Every week. A distribution macro does all of it in under a minute, the same way every time.

Step 1: Refresh and Export

Function ExportReportPdf(ByVal sheetName As String, ByVal label As String) As String
    Dim path As String
    path = Environ("TEMP") & "\" & label & " " & Format(Date, "yyyy-mm-dd") & ".pdf"
    ThisWorkbook.Worksheets(sheetName).ExportAsFixedFormat _
        Type:=xlTypePDF, Filename:=path, Quality:=xlQualityStandard, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
    ExportReportPdf = path
End Function

Sub RefreshEverything()
    ThisWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone
    Application.Calculate
End Sub

Set the print area and page layout on the report sheet once (orientation, fit to one page wide), and every export looks the same.

Step 2: Put Key Numbers in the Email Body

Function RangeToHtml(ByVal rng As Range) As String
    Dim r As Long, c As Long, html As String
    html = "<table style='border-collapse:collapse;font-family:Segoe UI,Arial;font-size:13px'>"
    For r = 1 To rng.Rows.Count
        html = html & "<tr>"
        For c = 1 To rng.Columns.Count
            html = html & IIf(r = 1, "<th", "<td") & _
                   " style='border:1px solid #ddd;padding:4px 8px;text-align:" & _
                   IIf(IsNumeric(rng.Cells(r, c).Value) And r > 1, "right", "left") & "'>" & _
                   Replace(rng.Cells(r, c).Text, "&", "&amp;") & _
                   IIf(r = 1, "</th>", "</td>")
        Next c
        html = html & "</tr>"
    Next r
    RangeToHtml = html & "</table>"
End Function

Using .Text instead of .Value keeps the number formatting you see in the sheet ($12,400, 6.2%). Keep the body to a small summary range; the detail belongs in the attachment.

Step 3: Create and Send the Email

Sub SendWeeklyReport()
    Dim ol As Object, mail As Object, pdf As String
    On Error GoTo Fail

    RefreshEverything
    pdf = ExportReportPdf("Report", "Weekly Sales")

    Set ol = CreateObject("Outlook.Application")       ' classic Outlook required
    Set mail = ol.CreateItem(0)
    With mail
        .To = "sales-managers@yourcompany.com"
        .Subject = "Weekly sales report – week ending " & Format(Date - 1, "d mmm yyyy")
        .HTMLBody = "<p>Hi all,</p><p>This week's headline numbers:</p>" & _
                    RangeToHtml(ThisWorkbook.Worksheets("Report").Range("B3:E8")) & _
                    "<p>Full report attached.</p>" & .HTMLBody   ' keeps default signature
        .Attachments.Add pdf
        .Display                                      ' change to .Send once tested
    End With
    Exit Sub
Fail:
    MsgBox "Could not send the report: " & Err.Description, vbExclamation
End Sub

Appending the existing .HTMLBody after Display-style creation preserves the user's Outlook signature in many setups; if the signature does not appear, call .Display before setting HTMLBody and then add your content in front of it.

Step 4: A Different Version for Each Manager

ColumnDistribution table example
ManagerPriya Shah
Emailpriya.shah@yourcompany.com
RegionWest (the filter value)
CCOptional
ActiveY/N
Sub SendRegionalReports()
    Dim lo As ListObject, r As ListRow, pdf As String, region As String
    Set lo = ThisWorkbook.Worksheets("Distribution").ListObjects("tblDistribution")
    RefreshEverything
    For Each r In lo.ListRows
        If r.Range(1, 5).Value = "Y" Then
            region = r.Range(1, 3).Value
            ThisWorkbook.Worksheets("Report").Range("C1").Value = region   ' report filters from C1
            Application.Calculate
            pdf = ExportReportPdf("Report", "Sales " & region)
            SendOne r.Range(1, 2).Value, "Sales report – " & region, pdf
            LogSend r.Range(1, 2).Value, region, pdf
        End If
    Next r
End Sub

The report sheet's formulas or PivotTable filter read the region from cell C1, so changing one cell produces each manager's version. SendOne and LogSend are small helpers following the pattern above; logging to a sheet gives you a record of who received what and when.

Scheduling Options

OptionHowLimits
Button the analyst clicksRun the macro on Monday morningNeeds a person, but simplest
Windows Task SchedulerOpens the workbook; Workbook_Open runs the macro and closesNeeds a logged-in session and classic Outlook
Power Automate + Office ScriptsCloud workbook refreshed and emailed on a scheduleDifferent code (TypeScript); some features differ
Power BI subscriptionsReport emailed from the Power BI serviceReport must be in Power BI

For the cloud route, see Office Scripts vs VBA. Adding an AI-drafted commentary paragraph to the email body is covered in automating report commentary with AI; personalised per-recipient text in personalized emails from Excel.

Checklist Before Going Live

  • Test with .Display for a few weeks, then switch to .Send.

  • Confirm every recipient sees only their own data.

  • Handle refresh failures: if a query fails, stop instead of sending stale numbers.

  • Store distribution lists in the workbook table, not in code.

  • Keep a copy of every sent PDF in a dated folder for reference.

FAQ

Emailing Excel reports FAQ

PDFs, HTML bodies, distribution and scheduling

On Windows with classic Outlook, a VBA macro can refresh the workbook, export the report sheet to PDF or a values-only copy, create an Outlook MailItem, attach the file, and send or display it. For cloud workbooks, Power Automate with Office Scripts can do the same on a schedule.

Convert the range to HTML and assign it to the MailItem's HTMLBody. A common method is publishing the range to a temporary HTML file with Excel's PublishObjects and reading it back, or building a simple HTML table in VBA from the range values.

Keep a distribution table with manager name, email and the filter value (region, branch). Loop through it: filter the report or PivotTable to that value, export the sheet to PDF, and email it to that manager. Log each send.

Desktop Excel and Outlook automation need a logged-in Windows session. Windows Task Scheduler can open a workbook with an Auto_Open macro, but only while the user session exists. For true unattended scheduling, use Power Automate with cloud workbooks or a server-based process.

No. The new Outlook does not support VBA or COM automation. Macros that call CreateObject("Outlook.Application") need classic Outlook installed. Alternatives are Power Automate, Microsoft Graph, or SMTP.