VBA & Macro Problems

Automate Access Query Export to Excel and Outlook Using VBA

Olivia Miller Oct 12, 2026 869 views

Question details

The user wants to automate the workflow of exporting a Microsoft Access query to an Excel file and subsequently attaching that file to a new Outlook email using VBA.

How to Automate Access Query Export to Excel and Send via Outlook

Product: Microsoft Access, Excel, Outlook
Device & OS: not provided
Scenario: Streamlining daily or weekly database reporting tasks by creating a one-click button that generates a spreadsheet report and prepares an email for distribution.
Observed behavior: The goal is to trigger a process that silently saves the query data as an XLSX file, then automatically opens an Outlook draft with the file attached so recipients can be added manually before sending.

Before you start

Ensure that your Access database is saved in a trusted location and that you have basic familiarity with the VBA Editor (pressing ALT + F11). For advanced automation, verify that the Microsoft Outlook Object Library is enabled in your VBA References.

Solution 1Recommended

Use TransferSpreadsheet and Outlook Application Object

This method offers the most flexibility, allowing you to save the Excel file to a specific folder path and customize advanced Outlook email properties before displaying the draft.

By combining DoCmd.TransferSpreadsheet with the Outlook Application object, you can tightly control where the exported file is saved on your hard drive and seamlessly attach it to a highly customized email.

Create a command button

Open your Access form in Design View. Drag a Command Button from the Controls gallery onto the form. If the Command Button Wizard appears, click Cancel to close it.

Open the Code Builder

Right-click the newly created button and select 'Build Event...'. Choose 'Code Builder' from the prompt and click OK. This opens the VBA Editor directly in the button's On Click event.

Add the export command

Inside the VBA subroutine, insert the code: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName", "C:\Path\To\ExportedFile.xlsx", True. Replace the query name and file path with your actual details.

Automate the Outlook draft

Below the export command, add VBA code to instantiate Outlook using CreateObject("Outlook.Application"). Create a new MailItem, use .Attachments.Add "C:\Path\To\ExportedFile.xlsx", and call the .Display method to open the email so you can add recipients manually.

Use TransferSpreadsheet and Outlook Application Object

Late Binding: Using CreateObject("Outlook.Application") is known as Late Binding. It prevents errors that might occur if different users are running different versions of Microsoft Office.

Solution 2

Use the Built-in DoCmd.SendObject Method

A simpler, built-in Access command that handles exporting and emailing in a single line of code, ideal if you do not need to save a permanent copy of the Excel file locally.

Free Microsoft Office alternative

Open Your Exported Data Seamlessly with WPS Office

While Microsoft Access is required to execute specific database VBA macros, you can handle, edit, and analyze the resulting exported Excel files effortlessly using WPS Office. It is a lightweight, fully compatible suite for all your document, spreadsheet, and presentation needs.

1. Download and Install: Visit the official WPS Office website and download the free installation package for your operating system.
2. Open Exported Spreadsheets: Locate the .xlsx file exported by your Access macro and double-click it. WPS Spreadsheet will open it instantly with perfect formatting.
3. Analyze and Share: Use WPS Spreadsheet's built-in formulas and charts to analyze your query data, then easily share the polished document with your team.

Seamlessly open, edit, and save exported Microsoft Excel (.xlsx) formats.Free, lightweight, and fast alternative to heavy productivity suites.Familiar tabbed interface ensuring a zero-learning-curve transition.Built-in PDF toolkit and advanced data analysis features.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'User-defined type not defined' error when running the Outlook VBA code?

This error occurs if you use Early Binding (e.g., Dim objOutlook As Outlook.Application) without enabling the Microsoft Outlook Object Library. To fix this, go to Tools > References in the VBA editor and check the box for 'Microsoft Outlook XX.0 Object Library', or switch to Late Binding using CreateObject("Outlook.Application").

Can I automatically format the Excel file before it gets attached to the Outlook email?

Yes, but you will need to add extra VBA code. After exporting using DoCmd.TransferSpreadsheet, you must use the Excel Object Library to silently open the .xlsx file, apply your desired formatting, save and close it, and only then proceed with the Outlook attachment code.

Is it possible to automatically send the email without having to click send manually?

Yes. If you are using the Outlook.Application method, simply replace the .Display command with .Send. If you are using the DoCmd.SendObject method, change the final argument (EditMessage) from True to False.

Why does DoCmd.SendObject sometimes fail to trigger Outlook?

DoCmd.SendObject relies on your Windows default mail client settings. If Outlook is not set as the default app for email on your computer, or if there is a conflict between 32-bit and 64-bit Office installations, the command may fail silently. Using the Outlook.Application object method is generally more reliable.

Excel & Data Problems