Deep-Dive Tutorial · MS Access VBA Reporting
MS Access Report Automation: Stop Clicking Export Every Month
If someone on your team still manually opens a query, clicks Export, renames the file, and pastes it into an Outlook draft every single month — this guide is for them. Or for you. No judgment.
Below is the exact MS Access report automation blueprint I've refined across 15 years of client engagements — from small manufacturing operations in the Midwest to regional banks on the East Coast. It covers the VBA patterns that actually hold up in production, the mistakes that silently wreck month-end numbers, and the checkpoints that will keep your finance team from ever receiving an empty PDF at 7 AM on the first of the month.
Why Manual Reporting Is Costing You More Than You Think
Before I get into the VBA, I want to make a business case — because in my experience, this is what gets leadership to actually prioritize the project.
A manual monthly report workflow typically costs 45–90 minutes of a skilled analyst's time per report cycle. But the hidden costs are worse: one fat-finger on an export filter, one wrong date range, one overwritten file — and you've got a CFO making decisions on bad numbers. I've seen it happen. It's expensive and embarrassing in equal measure.
Good Access report automation doesn't just save time. It removes the human from the critical path on routine, repeatable tasks — so your team can spend their brains on analysis, not export logistics.
Step 1: Build Your Query Foundation Before Touching VBA
This is the step most DIY guides skip — and it's why their automation breaks six months later.
Every automated report needs a single canonical saved query as its data source. Not inline SQL embedded in VBA. Not a query-inside-a-query-inside-a-form. One named, saved query in the Access navigation pane. Name it predictably: qryRpt_MonthlySales, qryRpt_RegionalInventory. The prefix tells any future developer (or your future self at 11 PM before a board meeting) that this query feeds a report and should not be deleted.
Store your runtime parameters — period-end date, branch code, currency, output folder path, distribution list — in a table called tblConfig. Your VBA reads from this table at runtime. When IT moves the network share (and they will), you change one row in a table, not 40 lines of VBA across six modules.
ConfigKey (Text, Primary Key) ConfigValue (Text)
─────────────────────────────────────────────────────────────
ExportRootPath \\SERVER01\Reports\
ReportingPeriodEnd 2025-04-30
DistributionList ops@yourco.com; cfo@yourco.com
RunLogTable tblRunLog
SlackWebhookURL (optional — for failure alerts)If your underlying data is slow — queries timing out, joins taking 30+ seconds — fix that before you automate. Automation amplifies instability. A slow query running once a month manually is annoying. That same slow query running inside a 2 AM scheduled job that times out and locks the backend is a database repair situation. Sort the query performance first.
Step 2: Open the Report with DoCmd.OpenReport
Here's the part that trips up most people who learn Access VBA from outdated blog posts: you need to call DoCmd.OpenReport before you export. Opening the report in acViewPreview forces Access to run the report engine — applying your filters, triggering the NoData event if there are zero records, and populating calculated fields and groupings. Skip this step and your DoCmd.OutputTo will export the report in its "design default" state — which usually means every record in the table.
Option Compare Database
Option Explicit
' ─────────────────────────────────────────────────────────
' Helper: read a value from tblConfig by key
' ─────────────────────────────────────────────────────────
Private Function GetConfig(ByVal cfgKey As String) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"SELECT ConfigValue FROM tblConfig WHERE ConfigKey = '" & cfgKey & "'", _
dbOpenSnapshot)
If Not rs.EOF Then GetConfig = rs!ConfigValue
rs.Close: Set rs = Nothing: Set db = Nothing
End Function
' ─────────────────────────────────────────────────────────
' Step 2: Open the report — filter to current period
' ─────────────────────────────────────────────────────────
Public Sub Open_MonthlySalesReport()
Dim periodEnd As Date
Dim periodStart As Date
Dim strWhere As String
' Read period end from config table — never hard-code dates
periodEnd = CDate(GetConfig("ReportingPeriodEnd"))
periodStart = DateSerial(Year(periodEnd), Month(periodEnd), 1)
' Build the WHERE clause — Access will apply this inside the report engine
strWhere = "OrderDate >= #" & Format(periodStart, "mm/dd/yyyy") & "# " & _
"AND OrderDate <= #" & Format(periodEnd, "mm/dd/yyyy") & "#"
' Open in Preview mode — Access resolves filters, NoData, calculated fields
' Use acViewNormal (print directly) only after validating in Preview first
DoCmd.OpenReport _
ReportName:="rptMonthlySales", _
View:=acViewPreview, _
WhereCondition:=strWhere
End SubNotice we're reading ReportingPeriodEnd from tblConfig. This means when month-end rolls over, one person updates one table row — no VBA edits, no source control commits, no "which copy of the database is production" debates.
Step 3: Export to PDF with DoCmd.OutputTo
Once the report is open and filtered (Step 2), export it. Build your filename from the period parameters so your archive folder sorts chronologically and no file ever gets silently overwritten.
' ─────────────────────────────────────────────────────────
' Step 3: Export the open report to PDF
' IMPORTANT: Call Open_MonthlySalesReport() first (Step 2)
' ─────────────────────────────────────────────────────────
Public Sub Export_MonthlySales_ToPDF() As String
Dim exportRoot As String
Dim periodEnd As Date
Dim strFolder As String
Dim strFile As String
Dim strPath As String
On Error GoTo ErrHandler
exportRoot = GetConfig("ExportRootPath") ' e.g. \SERVER01Reports periodEnd = CDate(GetConfig("ReportingPeriodEnd"))
' Build a sub-folder per year-month so root doesn't get cluttered
strFolder = exportRoot & Format(periodEnd, "yyyy-mm") & ""
If Dir(strFolder, vbDirectory) = "" Then MkDir strFolder
' Timestamped filename — never overwrites a previous run
strFile = "MonthlySales_" & Format(periodEnd, "yyyy_mm_dd") & ".pdf"
strPath = strFolder & strFile
' Export the *currently open, filtered* report to PDF
DoCmd.OutputTo _
ObjectType:=acOutputReport, _
ObjectName:="rptMonthlySales", _
OutputFormat:=acFormatPDF, _
OutputFilename:=strPath, _
AutoStart:=False ' Don't pop open Acrobat — this is automation, not a preview
' Quick sanity check — make sure the file was actually written
If Dir(strPath) = "" Or FileLen(strPath) < 1024 Then
Err.Raise vbObjectError + 1, , "Export produced empty or missing file: " & strPath
End If
Debug.Print "✓ Exported: " & strPath
Export_MonthlySales_ToPDF = strPath ' Return path for downstream steps (email, log)
Exit Function
ErrHandler:
MsgBox "PDF export failed: " & Err.Description & vbCrLf & _
"Check that the Exports folder exists and you have write access.", _
vbExclamation, "Export Error"
Export_MonthlySales_ToPDF = ""
End FunctionStep 4: Write a Run Log (Most People Skip This — Don't)
This is the step that separates professional-grade automation from "it works on my machine" VBA. A run log is a simple table where you record every execution: what ran, when, with what parameters, how many rows exported, where the file landed, and whether it succeeded.
When your CFO calls and says "the April numbers don't match what I got in the email," you open the run log, find the April 30th entry, and immediately know the exact period dates, row count, and file path used. That conversation takes five minutes instead of two days.
' ── Create this table in Access ──────────────────────────────────────────────
'
' tblRunLog
' LogID AutoNumber (PK)
' RunDate Date/Time
' ReportName Text(100)
' PeriodStart Date/Time
' PeriodEnd Date/Time
' RowCount Long Integer
' ExportPath Text(500)
' RunStatus Text(20) — "Success" / "Failed" / "NoData"
' ErrorDetail Memo
'
' ─────────────────────────────────────────────────────────────────────────────
Public Sub Log_RunResult( _
ByVal reportName As String, _
ByVal periodStart As Date, _
ByVal periodEnd As Date, _
ByVal rowCount As Long, _
ByVal exportPath As String, _
ByVal runStatus As String, _
Optional ByVal errorDetail As String = "")
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblRunLog", dbOpenDynaset)
rs.AddNew
rs!RunDate = Now()
rs!ReportName = reportName
rs!PeriodStart = periodStart
rs!PeriodEnd = periodEnd
rs!RowCount = rowCount
rs!ExportPath = exportPath
rs!RunStatus = runStatus
rs!ErrorDetail = errorDetail
rs.Update
rs.Close: Set rs = Nothing: Set db = Nothing
End SubCall Log_RunResult at the end of every automation routine, whether it succeeded or failed. Log failures especially — a failed run with no log entry is the worst outcome possible.
Step 5: Email the PDF Automatically via Outlook
Once you have the PDF path returned from Step 3, you can attach it to an Outlook email and send without any human interaction. We use late binding — creating Outlook.Application via CreateObject — so your Access database doesn't require an Outlook reference to open on machines where the version differs.
' ─────────────────────────────────────────────────────────────────────────────
' Email the exported PDF via Outlook — late binding, no reference required
' pdfPath: full path to the file returned from Export_MonthlySales_ToPDF()
' ─────────────────────────────────────────────────────────────────────────────
Public Sub Email_MonthlySalesPDF(ByVal pdfPath As String)
If pdfPath = "" Then
MsgBox "No PDF path supplied — email cancelled.", vbExclamation
Exit Sub
End If
If Dir(pdfPath) = "" Then
MsgBox "PDF not found at: " & pdfPath & vbCrLf & "Email cancelled.", vbExclamation
Exit Sub
End If
Dim ol As Object ' Outlook.Application
Dim mi As Object ' Outlook.MailItem
Dim recipients As String
On Error GoTo ErrHandler
' Read distribution list from config table — never hard-code email addresses in VBA
recipients = GetConfig("DistributionList") ' semi-colon separated
Set ol = CreateObject("Outlook.Application")
Set mi = ol.CreateItem(0) ' olMailItem = 0
mi.To = recipients
mi.Subject = "Monthly Sales Report — " & GetConfig("ReportingPeriodEnd")
mi.Body = "Team," & vbCrLf & vbCrLf & _
"Attached is the automated Monthly Sales Report for the period ending " & _
GetConfig("ReportingPeriodEnd") & "." & vbCrLf & vbCrLf & _
"This report was generated automatically. Do not reply to this email." & vbCrLf & _
"Contact the ops team for questions about the data."
mi.Attachments.Add pdfPath
' ⚠️ TESTING: Use .Display instead of .Send so you review before it goes out
' Swap to .Send only after you've confirmed the output is correct
mi.Send
Set mi = Nothing
Set ol = Nothing
Debug.Print "✓ Email sent to: " & recipients
Exit Sub
ErrHandler:
MsgBox "Email failed: " & Err.Description & vbCrLf & _
"Check that Outlook is installed and your security policy allows programmatic Send.", _
vbExclamation, "Email Error"
Set mi = Nothing: Set ol = Nothing
End SubStep 6: Schedule Overnight Runs with Windows Task Scheduler
The cleanest production setup for US business environments is a dedicated "runner" Access database — a separate lightweight .accdb file that does nothing except call your automation routines. This keeps your production UI responsive during business hours and means the scheduled job doesn't load every form and report object you've built.
' In your runner .accdb — add this to the Startup / AutoExec module.
' Task Scheduler launches Access with /x AutoExec — this runs on open.
Option Compare Database
Option Explicit
Public Sub AutoExec()
On Error GoTo ErrHandler
' Ensure we're running as the expected service account
' (optional but adds governance in regulated environments)
' If Environ("USERNAME") <> "svc_access_runner" Then GoTo ErrHandler
' Call the main orchestration routine in your linked production backend
Call Run_MonthlySalesReport() ' Step 2 — open + filter
Dim pdfPath As String
pdfPath = Export_MonthlySales_ToPDF() ' Step 3 — export to PDF
Call Email_MonthlySalesPDF(pdfPath) ' Step 5 — email
Call Log_RunResult("rptMonthlySales", _ ' Step 4 — log it
DateSerial(Year(Date), Month(Date), 1), _
CDate(GetConfig("ReportingPeriodEnd")), _
0, pdfPath, "Success")
DoCmd.Quit acQuitSaveNone ' Close Access when done — don't leave it sitting open
Exit Sub
ErrHandler:
Call Log_RunResult("rptMonthlySales", Date, Date, 0, "", "Failed", Err.Description)
DoCmd.Quit acQuitSaveNone
End SubIn Windows Task Scheduler, set the action to run:
Program: "C:Program FilesMicrosoft Office
ootOffice16MSACCESS.EXE"
Arguments: "\SERVER01DatabasesRunner_Monthly.accdb" /x AutoExec
' Requirements:
' - Machine must be powered on
' - A Windows user session must be active (or use "Run whether logged on or not"
' with a service account password saved in the task)
' - The service account must have WRITE access to the export folderThe 6 Mistakes That Break MS Access Report Automation in Production
I've inherited broken Access automation from other developers more times than I care to admit. The same six mistakes show up over and over:
Hard-coded file paths
"C:\Users\John\Desktop\Exports" is not a production path. IT will change the server share. John will leave. Use tblConfig and a UNC path.
No NoData handling
If your WHERE clause returns zero records, Access will generate an empty PDF. Without a NoData event, your automation happily emails a blank report. Add a NoData handler to your report that cancels export and logs the condition.
Calling OutputTo without OpenReport first
This is the #1 bug in DIY Access VBA guides. OutputTo without OpenReport exports the design-default dataset — not your filtered view. Always open first.
No run log
When numbers look wrong, you need to prove what parameters were used for a given run. Without a log table, that's impossible. Add one — it takes 20 minutes and saves days of forensic work.
Overwriting output files
Exporting to the same filename every run means you have no history. Timestamp your filenames (Report_2025_04_30.pdf). Disk space is cheap. Wrong numbers are expensive.
Testing on a personal machine, deploying to a server
Outlook policies, folder permissions, and Access runtime behavior differ between your dev laptop and the production server. Always validate the full automation chain in the target environment before calling it done.