Create and Email Store PDF Reports with Excel VBA
Question details
Automate the creation and emailing of a separate PDF report for each store.

- Product
- Microsoft Excel and Microsoft Outlook
- Device & OS
- not provided
- Scenario
- A workbook uses a store selector to display a report, and each store's report must go to its corresponding email recipient.
- Observed behavior
- Each store should have its own recalculated report exported as a PDF and addressed to the correct recipient.
Confirm that changing the store selector updates the report and that the store list contains the correct email addresses. The example below requires desktop Excel and classic Outlook for Windows; it prepares messages for review rather than sending them automatically.
Use Excel VBA to export each store report and prepare Outlook emails
Loop through a store list, update the report selector, export a PDF, and open an addressed Outlook message for each store.
This example assumes a sheet named Stores has store names in column A and email addresses in column B, starting in row 2. A sheet named Report uses cell B2 as its store selector. Change these names and the cell reference in the code to match your workbook.
The macro recalculates Excel formulas after changing the selector. If the report depends on external queries or asynchronous refreshes, make sure those refreshes finish before exporting.
On the Stores sheet, enter each store name in column A and its recipient's email address in column B. On the Report sheet, verify that B2 selects the store and that the sheet's print area and page layout produce the intended PDF.
In desktop Excel, press Alt+F11, choose Insert > Module, and paste the following code: Sub CreateStoreReports() Dim listSheet As Worksheet, reportSheet As Worksheet Dim outlookApp As Object, mail As Object Dim lastRow As Long, i As Long Dim storeName As String, recipient As String Dim folderPath As String, pdfPath As String, safeName As String Dim invalidChar As Variant On Error GoTo ErrorHandler Set listSheet = ThisWorkbook.Worksheets("Stores") Set reportSheet = ThisWorkbook.Worksheets("Report") lastRow = listSheet.Cells(listSheet.Rows.Count, "A").End(xlUp).Row folderPath = Environ$("TEMP") & "\StoreReports" If Dir(folderPath, vbDirectory) = "" Then MkDir folderPath Set outlookApp = CreateObject("Outlook.Application") For i = 2 To lastRow storeName = Trim$(CStr(listSheet.Cells(i, "A").Value)) recipient = Trim$(CStr(listSheet.Cells(i, "B").Value)) If storeName <> "" And recipient <> "" Then reportSheet.Range("B2").Value = storeName Application.CalculateFull safeName = storeName For Each invalidChar In Array("/", Chr(92), ":", "*", "?", Chr(34), "<", ">", "|") safeName = Replace(safeName, invalidChar, "_") Next invalidChar pdfPath = folderPath & Chr(92) & CStr(i) & "_" & safeName & ".pdf" reportSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath Set mail = outlookApp.CreateItem(0) With mail .To = recipient .Subject = "Store report: " & storeName .Body = "Hello," & vbCrLf & vbCrLf & "Please find your store report attached." .Attachments.Add pdfPath .Display End With End If Next i MsgBox "Reports prepared in " & folderPath Exit Sub ErrorHandler: MsgBox "Could not complete the reports: " & Err.Description, vbExclamation End Sub
Choose File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). With Outlook configured, run CreateStoreReports from Developer > Macros using a small store list first.
In each Outlook message opened by the macro, check the recipient and attached PDF, then click Send. The PDFs are saved in the StoreReports folder under the Windows temporary folder.

Manage Store Report Workbooks with WPS Spreadsheet
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel files to work with store data and make a seamless move between common Office formats. The Outlook VBA automation described above still requires a compatible Microsoft Office setup.

Frequently Asked Questions
How do I change the store selector used by the macro?
In the VBA editor, change reportSheet.Range("B2") to the cell containing your store selector. Also update the Report sheet name if your worksheet uses a different name.
Will the macro send emails automatically?
No. The example uses Outlook's .Display method to open each message for review. Check the recipient and attachment, then click Send in Outlook.
Why might a PDF show the wrong store's data?
Check that the store name in the Stores sheet matches a valid selector value and that changing Report!B2 updates the report. If the report uses external data, its refresh may need to finish before ExportAsFixedFormat runs.
Where are the PDF reports saved?
The macro creates a StoreReports folder in the Windows temporary folder, using the TEMP environment variable. It shows the folder path when the loop completes.
Can WPS Office run the Outlook macro in this example?
The example is intended for desktop Excel and classic Outlook for Windows. VBA and Outlook automation support can vary in other applications, so do not assume it will run unchanged in WPS Office.




