VBA & Macros

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.

A
Anthony Passeri, MBA
••6 min read
Automating Access Reports with VBA

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.

Schedule a Free Call →

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.

A

Written by

Anthony Passeri, MBA

Access Expert and Editor

Bridging the gap between Access and the Future

Explore Topics

#VBA#reports#automation#PDF export#Access development

Keep reading

More on VBA & Macros