VBA & Macro Problems

How to Email a Filtered Access Report via VBA for the Current Record

Natalie Taylor Oct 20, 2026 868 views

Question details

The user wants to create a VBA button in Microsoft Access to filter a report based on the current form's record, convert it to a PDF, and attach it to a new email.

How to Email a Filtered Access Report via VBA for the Current Record

Product: Microsoft Access
Device & OS: not provided
Scenario: Automating the process of sending a specific vehicle inspection report via email directly from the data entry form.
Observed behavior: The user needs the correct VBA syntax to open the report with a filter, attach it to an email as a PDF, and leave the email window open for editing before sending.

Before you start

Ensure your default email client (like Microsoft Outlook) is properly configured on your computer, as the DoCmd.SendObject method relies on it to generate the outgoing email.

Solution 1Recommended

Use VBA to Filter, Export, and Attach the Report

This method opens the report in a hidden preview mode with a filter applied, attaches it to an email as a PDF, and then automatically closes the report.

By using DoCmd.OpenReport with a WhereCondition, you can isolate the specific record. DoCmd.SendObject then packages the currently open report as a PDF attachment.

The EditMessage:=True argument ensures the email window remains open so you can manually enter the recipient's address or modify the message body before clicking send.

Add a Command Button

Open your Access form in Design View. From the Form Design ribbon, select the Button control and place it on your form. Name it 'cmdEmailReport'.

Open the VBA Editor

Right-click the newly created button, select 'Build Event...', choose 'Code Builder' from the prompt, and click OK. This opens the VBA Editor for the button's Click event.

Insert the VBA Code

Enter the following VBA code inside the Click event: Private Sub cmdEmailReport_Click() On Error GoTo ErrorHandler Dim reportFilter As String Dim messageText As String reportFilter = "VehicleInspectionDBID = " & Me.VehicleInspectionDBID messageText = "Attached is the vehicle inspection report." DoCmd.OpenReport _ ReportName:="rpt_VehicleInspectionReport", _ View:=acViewPreview, _ WhereCondition:=reportFilter, _ WindowMode:=acHidden DoCmd.SendObject _ ObjectType:=acSendReport, _ ObjectName:="rpt_VehicleInspectionReport", _ OutputFormat:=acFormatPDF, _ To:="", _ Subject:="Vehicle Inspection Report", _ MessageText:=messageText, _ EditMessage:=True DoCmd.Close acReport, "rpt_VehicleInspectionReport", acSaveNo Exit Sub ErrorHandler: If CurrentProject.AllReports("rpt_VehicleInspectionReport").IsLoaded Then DoCmd.Close acReport, "rpt_VehicleInspectionReport", acSaveNo End If If Err.Number <> 2501 Then MsgBox Err.Description, vbExclamation, "Email Report Error" End If End Sub

Test the Automation

Save the VBA module and switch your form back to Form View. Navigate to a specific record and click the button. Your default email client should open with the PDF attached.

Use VBA to Filter, Export, and Attach the Report

Handling Text Fields: If your matching ID (VehicleInspectionDBID) is a text field rather than a number, you must enclose the value in single quotes in the filter string: reportFilter = "VehicleInspectionDBID = '" & Replace(Me.VehicleInspectionDBID, "'", "''") & "'"

Free Microsoft Office alternative

Discover WPS Office for Your Daily Productivity Needs

While Microsoft Access is designed for complex relational databases, WPS Office provides a free, lightweight, and highly compatible suite for your everyday Word, Excel, and PowerPoint files. With native VBA support in WPS Spreadsheet, you can automate data processing tasks effortlessly without the heavy overhead.

1. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
2. Install the Software: Run the downloaded executable file and follow the straightforward installation wizard.
3. Explore VBA Capabilities: Open WPS Spreadsheet, navigate to the Developer tab, and start writing VBA macros to automate your spreadsheet workflows.

Fully compatible with Microsoft Office formats (.docx, .xlsx, .pptx).Robust VBA and Macro support in WPS Spreadsheet for advanced data automation.Built-in PDF toolkit to easily view, edit, and convert PDF reports.Lightweight installation and a familiar tabbed user interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get "Error 2501: The SendObject action was canceled"?

This error naturally occurs if you close the new email window without sending the email. The error handler in the provided VBA code (`If Err.Number <> 2501 Then`) intentionally catches and ignores this specific error to prevent annoying system pop-ups when you decide to cancel a draft.

Can I populate the "To" field automatically using a field on my form?

Yes. In the DoCmd.SendObject method, simply change the `To:=""` argument to reference your form field containing the email address. For example, use `To:=Me.CustomerEmailAddress` to ensure the recipient is automatically filled.

Is it possible to add a dynamic date to the email subject line?

Absolutely. You can concatenate the current date into your string variables using VBA's Format and Date functions. For example: `Subject:="Vehicle Inspection Report - " & Format(Date(), "mm/dd/yyyy")`.

Why does the email attach all records instead of the current one?

This happens if you trigger DoCmd.SendObject directly without first opening the report with a filter applied. You must use DoCmd.OpenReport with a WhereCondition first, so the report is filtered in memory before SendObject attaches it to the email.

Excel & Data Problems