Automating Access Reports with VBA
Stop manually opening and printing reports. VBA lets you automate report generation, apply filters on the fly, export to PDF, and email output — all without user intervention.
If someone in your organization opens Access every Monday morning, runs the same three reports, exports them to PDF, and emails them to a distribution list — that is a job for VBA.
Report automation is one of the most practical uses of VBA in Access. The techniques are not complicated, and the time savings add up fast. Here is how to do it.
Opening a Report with DoCmd
The foundation of report automation is DoCmd.OpenReport. It opens a report in Print Preview, Design View, or sends it directly to the printer.
' Open in Print Preview
DoCmd.OpenReport "rptMonthlySales", acViewPreview
' Send directly to printer
DoCmd.OpenReport "rptMonthlySales", acViewNormal
' Open in Report View (on-screen, no print dialog)
DoCmd.OpenReport "rptMonthlySales", acViewReport
The third argument is a WHERE clause filter — you can pass SQL criteria to filter the report without changing its underlying query:
' Show only orders from this month
Dim strFilter As String
strFilter = "OrderDate >= #" & Format(DateSerial(Year(Date), Month(Date), 1), "mm/dd/yyyy") & "#"
DoCmd.OpenReport "rptMonthlySales", acViewPreview, , strFilter
This is powerful: one report, filtered differently each time it runs, without any changes to the report design.
Exporting to PDF
Access 2010 and later can export reports directly to PDF using DoCmd.OutputTo:
Dim strOutputPath As String
strOutputPath = "C:\Reports\MonthlySales_" & Format(Date, "YYYYMM") & ".pdf"
DoCmd.OutputTo acOutputReport, "rptMonthlySales", acFormatPDF, strOutputPath, False
The False at the end suppresses the "open after export" prompt. The file is saved silently.
To apply a filter before exporting, open the report first with a WHERE clause, then export the open report:
Dim strFilter As String
strFilter = "SalesRegion = 'Northwest'"
' Open filtered (hidden)
DoCmd.OpenReport "rptMonthlySales", acViewReport, , strFilter, acHidden
' Export the open report
DoCmd.OutputTo acOutputReport, "rptMonthlySales", acFormatPDF, _
"C:\Reports\NW_Sales.pdf", False
' Close the report
DoCmd.Close acReport, "rptMonthlySales", acSaveNo
The acHidden window mode opens the report without displaying it — useful when you are exporting silently in the background.
Exporting Multiple Reports in a Loop
If you need to generate one report per region, per salesperson, or per month, loop through a recordset and export each one:
Sub ExportRegionalReports()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strFilter As String
Dim strPath As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT SalesRegion FROM tblOrders")
Do While Not rs.EOF
strFilter = "SalesRegion = '" & rs!SalesRegion & "'"
strPath = "C:\Reports\" & rs!SalesRegion & "_Sales.pdf"
DoCmd.OpenReport "rptMonthlySales", acViewReport, , strFilter, acHidden
DoCmd.OutputTo acOutputReport, "rptMonthlySales", acFormatPDF, strPath, False
DoCmd.Close acReport, "rptMonthlySales", acSaveNo
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "All regional reports exported.", vbInformation
End Sub
This generates a separate PDF for each region in your orders table — automatically named, automatically filtered, no manual steps.
Sending Reports by Email
Combine PDF export with Outlook automation to email reports directly from Access:
Sub EmailMonthlySalesReport()
Dim strPath As String
Dim olApp As Object
Dim olMail As Object
' Export the report to PDF
strPath = Environ("TEMP") & "\MonthlySales.pdf"
DoCmd.OpenReport "rptMonthlySales", acViewReport, , , acHidden
DoCmd.OutputTo acOutputReport, "rptMonthlySales", acFormatPDF, strPath, False
DoCmd.Close acReport, "rptMonthlySales", acSaveNo
' Create and send the email
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0) ' 0 = olMailItem
With olMail
.To = "manager@company.com; director@company.com"
.Subject = "Monthly Sales Report - " & Format(Date, "MMMM YYYY")
.Body = "Please find the monthly sales report attached." & vbCrLf & vbCrLf & _
"This report was generated automatically."
.Attachments.Add strPath
.Send
End With
Set olMail = Nothing
Set olApp = Nothing
MsgBox "Report emailed successfully.", vbInformation
End Sub
This requires Outlook to be installed and configured on the machine running Access. For server-based automation, you would use a different email approach — but for desktop Access databases, Outlook automation is reliable.
Scheduling Automation with AutoExec
Access does not have a built-in scheduler, but you can trigger automation on database open using an AutoExec macro or the Workbook_Open equivalent in Access — the AutoExec macro.
Create a macro named AutoExec that runs a VBA function on startup:
' In a standard module
Function RunScheduledReports()
' Only run on Mondays
If Weekday(Date) = vbMonday Then
ExportRegionalReports
EmailMonthlySalesReport
End If
End Function
Then create an AutoExec macro with one action: RunCode, calling RunScheduledReports().
Every time the database opens on a Monday, the reports run automatically. Not a true scheduler, but effective for databases that are opened regularly.
For true unattended scheduling, use Windows Task Scheduler to open the database at a set time — Access will run the AutoExec macro automatically.
Handling Errors Gracefully
Report automation runs unattended, so errors need to be caught and logged rather than displayed as dialog boxes that freeze the process:
Sub ExportReportSafe(strReportName As String, strPath As String, strFilter As String)
On Error GoTo ErrorHandler
DoCmd.OpenReport strReportName, acViewReport, , strFilter, acHidden
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strPath, False
DoCmd.Close acReport, strReportName, acSaveNo
Exit Sub
ErrorHandler:
' Log the error instead of showing a dialog
Dim intFile As Integer
intFile = FreeFile
Open "C:\Reports\error_log.txt" For Append As #intFile
Print #intFile, Now() & " | " & strReportName & " | Error " & Err.Number & ": " & Err.Description
Close #intFile
End Sub
This writes errors to a log file instead of stopping execution — important when you are generating a batch of 20 reports and one has a data issue.
The Migration Perspective
Report automation in Access VBA is genuinely useful, and if it is working well for you, there is no reason to change it immediately. But it is worth knowing what the equivalent looks like on modern platforms.
In Power BI, reports refresh automatically from the data source — no VBA, no scheduled task, no Outlook dependency. In Power Automate, you can trigger a report export and email on a schedule with a few clicks and no code.
If your report automation is becoming complex — many reports, many recipients, conditional logic about who gets what — that complexity is a signal that a more capable platform might serve you better. The logic you have built in VBA is real business logic; it just deserves better infrastructure.
Work With Access Evolved
If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.
Want to automate your Access report generation and distribution? Schedule a free call and explore modern reporting automation options.
Thinking about migrating?
Ready to move beyond Microsoft Access?
Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on VBA & Macros
Building an Audit Log in Access with VBA
Knowing who changed what and when is essential for compliance and troubleshooting. Here is how to build a reliable audit trail in Access using VBA form events and a log table.
VBA Debugging Techniques Every Access Developer Needs
The VBA debugger is one of the most powerful tools in Access — and one of the most underused. Here is how to use breakpoints, the Immediate window, and watch expressions effectively.
VBA Class Modules: Write Cleaner Access Code
Class modules bring object-oriented structure to VBA. Learn how to use them in Access to reduce repetition, improve maintainability, and write code that scales.