VBA & Macro Problems

Automatically Email Individual Excel Reports as PDF Attachments

Nimra MalikNimra Malik Nov 18, 2026 868 views

Question details

The user wants to email each recipient a PDF containing only their own weekly sales report, along with a standard message.

Automatically Email Individual Excel Reports as PDF Attachments
Product
Microsoft Excel and Outlook
Device & OS
not provided
Scenario
A weekly sales workbook contains reports for multiple recipients who must receive their own pages by email.
Observed behavior
The desired workflow selects each recipient's data, exports the corresponding report as a PDF, and attaches it to an individual email.
Before you start

On a Windows PC, use desktop Excel and classic Outlook with an email account configured. Set up a report sheet that changes its displayed data when you enter a recipient identifier in cell B2, and verify that its print area excludes other recipients' data.

Solution 1Recommended

Create Individual PDF Email Drafts with Excel VBA

Use a macro to recalculate the report for each recipient, export it as a PDF, and open an Outlook message for review.

This example requires desktop Excel and classic Outlook on Windows. It creates drafts for review rather than sending them automatically.

The report must already be designed to show only the recipient identified in Report!B2. The macro changes that cell; it does not create the filtering formulas or report layout.

1
Prepare the recipient list

Create a worksheet named Recipients. Enter a unique report identifier in column A and the corresponding email address in column B, starting on row 2. Each identifier must match the value expected by the report in cell B2.

2
Check the report output

On the Report worksheet, enter several identifiers in B2 and confirm that the displayed report changes correctly. Use Page Layout > Print Area > Set Print Area, then check File > Print to ensure the PDF will contain only that person's report.

3
Add the macro

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Press Alt+F11, choose Insert > Module, and paste the following code: Sub PrepareReportEmails() Dim wsList As Worksheet, wsReport As Worksheet Dim outlookApp As Object, mail As Object Dim lastRow As Long, r As Long Dim personID As String, emailAddress As String, pdfPath As String Set wsList = ThisWorkbook.Worksheets("Recipients") Set wsReport = ThisWorkbook.Worksheets("Report") Set outlookApp = CreateObject("Outlook.Application") lastRow = wsList.Cells(wsList.Rows.Count, "A").End(xlUp).Row For r = 2 To lastRow personID = Trim(CStr(wsList.Cells(r, "A").Value)) emailAddress = Trim(CStr(wsList.Cells(r, "B").Value)) If personID <> "" And emailAddress <> "" Then wsReport.Range("B2").Value = personID Application.CalculateFull pdfPath = Environ$("TEMP") & "\SalesReport_" & Format(Now, "yyyymmdd_hhnnss") & "_" & r & ".pdf" wsReport.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath Set mail = outlookApp.CreateItem(0) With mail .To = emailAddress .Subject = "Your weekly sales report" .Body = "Hello," & vbCrLf & vbCrLf & "Please find your weekly sales report attached." & vbCrLf & vbCrLf & "Thank you." .Attachments.Add pdfPath .Display End With End If Next r End Sub

4
Test and review the drafts

Start with a small test list using addresses you control. In Excel, press Alt+F8, select PrepareReportEmails, and click Run. Open each attached PDF in the Outlook message and verify that its contents match the address in the To field before clicking Send.

5
Run the weekly process

Update the Recipients list and sales data each week, then run PrepareReportEmails from Alt+F8. After testing, you can replace .Display with .Send in the VBA editor for automatic sending, but do so only after validating the report filtering and recipient mappings.

Create Individual PDF Email Drafts with Excel VBA
Scheduling and privacy: The macro does not schedule itself, and .Send sends messages without a draft review. Exported PDFs remain in the Windows temporary folder; remove them according to your organization's data-handling policy.
Free Microsoft Office alternative

Prepare Sales Reports with WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets and exporting reports to PDF. Its Microsoft Office format compatibility helps you move existing files into WPS; the Excel VBA and Outlook automation above remains a Microsoft Office workflow.

Work with sales spreadsheets and export report pages as PDFs.Open and edit common Microsoft Excel formats, including XLSX.Use a familiar spreadsheet interface that makes migration easier.Create and review documents with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

How do I make sure each PDF contains only one person's sales figures?

Configure the Report sheet so cell B2 controls the recipient-specific data, then check several identifiers and use File > Print to inspect the output. The macro changes B2 and exports the sheet; it does not independently verify the filtering.

Can the macro send the emails without opening each draft?

Yes. After testing the recipient mappings and PDF contents, replace .Display with .Send in the VBA module. This removes the opportunity to review each message before it is sent.

Why do I need to save the workbook as an XLSM file?

An .xlsm workbook can store VBA macros. Use File > Save As and select Excel Macro-Enabled Workbook before adding or running the code.

Will this macro run automatically every week?

No. The example runs when you select it under Alt+F8 and click Run. A separate scheduling setup would be needed for unattended weekly execution.

Where are the exported PDF files stored?

The macro saves them in the Windows temporary folder identified by the TEMP environment variable. Open that folder by entering %TEMP% in File Explorer, and handle the files according to your organization's privacy policy.