Weekly Report Preparation Hours Eliminated
3 hours of Friday afternoon report generation, personalisation, and email dispatch replaced by an unattended Thursday overnight run - reports in recipient inboxes before Friday 8am.
Case study
3 Hours Every Friday, Automated Away: Scheduled Report Distribution for a Financial Services Group
A financial services group produced 12 performance reports every Friday afternoon and emailed them to 40 internal and client recipients - each recipient receiving a different subset of reports with their own data filter applied. One analyst spent 3 hours on this task weekly. We built a VBA scheduler that runs the entire process unattended overnight Thursday, so reports arrive in inboxes before the working day starts.
Reports Generated Per Run
12 performance reports generated from live SQL data, formatted in Excel templates, and exported as PDFs in under 25 minutes - zero manual steps.
Recipients Served
40 internal and client recipients each receiving their personalised report package - correct reports, correct data filter applied - without any manual recipient management per run.
Recipient Configuration Time
Adding a new recipient, changing their report subscription, or updating their data filter takes 2 minutes in the configuration table - no VBA edits required.
Report Delivery Consistency
Reports delivered to all 40 recipients simultaneously by 7am Friday. Previously, distribution order was ad hoc - some recipients received reports at 2pm, others at 5pm.
First-Year ROI
400% - 3 hours weekly recovered at analyst rates, plus client relationship value from consistent, early-morning Friday delivery that clients now use to prepare for their own Monday reviews.
The Problem: Three Hours Every Friday for No Technical Reason
This financial services group manages investment portfolios and advisory relationships for institutional and private clients. Every Friday, a performance analyst generated 12 reports from the portfolio management system - position summaries, performance attribution, risk metrics, transaction history, and compliance checks - and emailed them to 40 recipients across internal teams and client contacts.
The 40 recipients did not all receive the same reports. Relationship managers received client-facing performance and transaction summaries. Risk managers received the risk and compliance reports. Regional directors received performance summaries filtered to their region. Each of these permutations required the analyst to generate the report, apply the right data filter, and address the email to the right recipient individually.
For 12 reports and 40 recipients - some receiving 2 reports, some receiving 8 - this was approximately 3 hours of highly repetitive, low-value work every Friday afternoon. If the analyst was on leave, reports went out late or not at all. If a recipient was added to a report, it required the analyst to remember for every subsequent Friday.
What We Built
We built a VBA report scheduling and distribution system with four modules: a SQL data retrieval engine, a report formatting engine, a personalisation and distribution engine, and a configuration management layer.
The SQL retrieval engine uses ADO connections to the portfolio management database. Each report has a defined base query with parameterisable filter clauses. When a report is generated for a recipient, the macro substitutes the recipient's filter criteria - region code, client reference, or portfolio identifier - into the query's WHERE clause and retrieves only that recipient's data.
The formatting engine loads each report's Excel template, writes the query results into the data range, applies the template's conditional formatting and chart refreshes, and exports the completed sheet as a PDF. Templates are maintained by the analytics team - the macro reads whatever template is current without requiring code changes.
The distribution engine reads the recipient configuration table and builds a personalised email for each recipient - correct report attachments, correct covering email subject line, and the recipient's name in the body. The Outlook batch is queued and dispatched in sequence. All 40 recipients receive their package within 25 minutes of the macro starting.
Windows Task Scheduler triggers the run at 11pm Thursday. By 7am Friday, all reports are in recipient inboxes. The analyst's Friday morning now starts with a confirmation email from the scheduler - run completed, 12 reports generated, 40 recipients sent - not a three-hour production task.
System Capabilities
Scheduled Unattended Run
Windows Task Scheduler triggers the full run at 11pm Thursday. No user logged in, no Excel open. Reports delivered before the working day starts.
Parameterised SQL Queries
Recipient-specific data filters applied as SQL WHERE clauses - each of 40 recipients receives a report generated from their own data scope, not a manually filtered copy.
Template-Based Report Formatting
Analytics team maintains Excel report templates independently. VBA reads the current template each run - template changes take effect automatically without code updates.
Personalised Email Distribution
40 personalised emails dispatched - correct report attachments per recipient, personalised subject line and body, consistent delivery within one batch run.
Configuration Table Management
All recipient subscriptions, filters, and report assignments in a structured Excel table. Operations team adds, removes, or modifies recipients in 2 minutes without VBA knowledge.
Run Confirmation and Error Notification
Scheduler sends a confirmation email on successful completion and an error notification if the run fails - analyst knows whether reports were delivered without manually checking.
After Go-Live: What Changed
The analyst no longer works on reports on Friday afternoon. The run confirmation email arrives in her inbox at approximately 11:25pm Thursday night - visible Friday morning. If the scheduler confirms success, her Friday starts with zero report-related tasks.
Client recipients noticed the change within the first month - not because they were told about it, but because reports consistently arrived before 8am Friday rather than between 2pm and 5pm. Three client contacts mentioned in their next quarterly reviews that they now use the Friday morning reports in their own Monday briefing preparation - a use case that was not possible when reports arrived Friday afternoon.
Two recipients were added in the three months after deployment. Each took 2 minutes to configure in the table - including their report subscription, data filter, and email address. The analyst did not touch the VBA code.
“Friday afternoons used to mean three hours of generating reports and sending emails. I was still doing it at half four sometimes. Now I come in Friday morning and my inbox has a confirmation that everything went out at 11:25 the night before. Clients get their reports before they start their day. I use Friday afternoons for actual analytical work now.
Tech Stack
Excel VBA - SQL query execution, report formatting engine, PDF export, and Outlook distribution
ADO (ActiveX Data Objects) - parameterised SQL Server connections with recipient-specific WHERE clause substitution
Excel report templates - maintained by analytics team; VBA reads current template each run without code changes
Windows Task Scheduler - Thursday 11pm trigger for fully unattended run; no user session required
Recipient configuration table - all subscriptions, data filters, and email addresses in structured Excel table; no VBA edits for recipient changes
Outlook object model - personalised batch email dispatch; confirmation and error notification emails to operations analyst
Frequently Asked Questions
How does VBA generate reports from a SQL database and schedule them without human intervention?
The VBA macro connects to the SQL Server database via ADO (ActiveX Data Objects), executes the relevant query for each report, loads the results into Excel, applies the report template formatting, and exports to PDF. Windows Task Scheduler triggers the macro at the defined time each week. The macro runs on a background Windows session - it does not require anyone to be logged in or Excel to be open. Each report runs sequentially; the full set of 12 reports completes in under 25 minutes.
How are recipient-specific data filters applied across 40 different recipients?
A recipient configuration table in Excel stores each recipient's email address, the reports they should receive, and their data filter criteria - for example, a regional director receives reports filtered to their region code. When the macro processes each report, it reads the recipient's filter from the configuration table and applies a WHERE clause to the SQL query before generating that recipient's version. Recipients receiving the same report but different data subsets each get a personalised PDF. The configuration table is maintained by the operations team without VBA knowledge.
What happens if the SQL database is unavailable when the scheduled run fires?
The macro includes connection retry logic - it attempts the SQL connection three times with a 2-minute interval between retries. If the connection fails on all three attempts, it logs the failure, sends a notification email to the operations manager flagging that the Friday report run has failed and reporting will be delayed, and exits cleanly. No partial reports are sent to recipients. The operations manager can trigger a manual rerun once the database is available by running the macro directly from the control workbook.
Can the distribution list and report schedule be changed without modifying VBA code?
Yes - all configuration lives in a structured Excel table. The operations team can add or remove recipients, change which reports a recipient receives, update their data filter, change the delivery email address, or suspend a recipient by setting an active flag to No. Report schedules and the global send time are also stored in the configuration table. Changes take effect on the next scheduled run without any VBA edits. This was a deliberate design decision - the VBA code should only need to change if the report structure itself changes.
Next step
Want similar results in your operations?
Book an Automation Opportunity Assessment. We map workflows and scope a practical build plan.
- No Onsite Visit Required
- No Technical Specification Required
- Assessment Before Commitment
- Clear Scope Before Work Begins
Questions before you book? Speak with our team at +1 385 386 3860
