Access VBA: Export Excel Reports and Email Them Automatically with Outlook
Question details
The user needs an Access VBA procedure that exports query results to formatted Excel workbooks, saves them, and attaches each workbook to an Outlook email for each record in a recordset.
- Product
- Microsoft Access, Microsoft Excel, Microsoft Outlook
- Device & OS
- not provided
- Scenario
- The user is automating report distribution by looping through an Access recordset, exporting each query result to Excel, and emailing the saved workbook separately.
- Observed behavior
- The expected behavior is that each exported workbook is created, formatted, saved, closed, and then attached to a new Outlook mail item and sent or displayed automatically.
Verify that Microsoft Outlook is installed and configured with the email account you intend to use, and confirm that your Access VBA project can create Excel and Outlook objects with CreateObject before running the loop.
Export, Format, Save, and Email Each Workbook Inside the Access Recordset Loop
Use this complete VBA loop when you need to process each record separately, create a uniquely named Excel file, attach it to a new Outlook message, and send or display it.
This late-binding approach uses CreateObject for Excel and Outlook, so you do not need to set Excel or Outlook object library references before running the code. Each workbook must be saved and closed before it is attached because an open workbook may be locked or missing recent changes.
In the VBA procedure, declare object variables and use Set xlApp = CreateObject("Excel.Application") and Set olApp = CreateObject("Outlook.Application") before opening the recordset.
Open the recordset, then for each record set the query criteria or SQL parameter to the current key and use DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExport", filePath, True to export the result to a uniquely named workbook.
Set xlWorkbook = xlApp.Workbooks.Open(filePath), apply formatting such as bold headers or AutoFit, then use xlWorkbook.Save and xlWorkbook.Close to save and release the workbook before attaching it.
Create a new Outlook mail item, set the To address directly from the current record unless multiple recipients are required, add a Subject and Body, and use olMail.Attachments.Add filePath; then call olMail.Send to send immediately or olMail.Display to review before sending.
Loop to the next record in the recordset, build a new unique file path, and repeat the export, save, close, and attachment process for each record.
WPS Office for Excel-Compatible Reporting and Document Tasks
WPS Office cannot run Microsoft Access VBA or replace the Access database engine, but it is a free, lightweight Office alternative for the Excel-compatible reports, Word-compatible documents, and presentations in your workflow. It opens and saves Microsoft Office formats with a familiar ribbon interface, so you can handle everyday spreadsheet and document tasks without a subscription.
- 1. Download WPS Office: Download and install WPS Office from the official WPS website to start using the free Office alternative.
- 2. Open your Excel-compatible workbook: Launch WPS Office Spreadsheets and open the .xlsx or .xls file you need to edit or review.
- 3. Save and share in Microsoft-compatible formats: Use the familiar ribbon interface to edit, save, and share .xlsx, .docx, and .pptx files without changing your everyday Office workflow.
Frequently Asked Questions
Does WPS Office include Microsoft Access or run Access VBA code?
WPS Office does not include Microsoft Access or run Access VBA. For Access automation, you need Microsoft Access; however, WPS Office can open and edit Excel, Word, and PowerPoint compatible files as a free alternative.
Why must the Excel workbook be saved before it is attached in Outlook?
Saving and closing the workbook before attaching ensures the file contains all exported and formatted data and avoids attaching a locked or incomplete file from an open Excel instance.
How do I make each exported Excel file unique inside the recordset loop?
Build a unique file path for each record by appending a record ID, order number, or timestamp to the workbook name, then use that same path when opening, saving, and attaching the workbook.
Can I send the Outlook email automatically without displaying it?
Yes. Use the MailItem.Send method after attaching the workbook. Make sure Outlook is configured to send immediately when connected, or use Display instead to review each message before sending.
