Monthly Invoicing Admin Eliminated
15 hours of monthly invoice preparation - copying fee data into Word templates, saving PDFs, and emailing each client individually - replaced by a 12-minute automated batch run.
Case study
15 Hours of Monthly Invoicing Eliminated: Auto-Invoice Generator for an Accounting Firm
An accounting firm's billing staff manually typed 80-120 client invoices every month from fee agreements and time logs - copying figures into Word templates one by one. We built a VBA macro that generates, formats, and emails all invoices in under 12 minutes.
Invoices Generated per Run
80-120 formatted Word invoices generated as PDFs, named correctly, and emailed to clients in a single batch - each one identical in quality to a manually prepared invoice.
Format Errors Since Deployment
Zero. Manual preparation regularly produced formatting inconsistencies - wrong currency symbol, incorrect tax rate, misaligned line items. Automated template population eliminated all of them.
Invoice Templates Supported
3 invoice template types - fixed fee, time-and-materials, and retainer - selected automatically from a client type field in the billing register. No manual template selection.
Invoice Dispatch Time
All invoices dispatched to clients within 12 minutes of month-end billing register finalisation - previously a two-day process of typing, checking, and individual emails.
First-Year ROI
350% - 15 hours recovered monthly at senior billing staff rates, faster invoice dispatch improving cash collection by an average of 3 days, and format errors eliminated.
The Problem: Typing the Same Information Into a Different Template
This accounting firm bills clients on fixed monthly retainers, project fees, and time-and-materials arrangements. Every month, the billing coordinator opened the fee agreement for each client, cross-referenced it with the time log, and manually typed the fee details into a Word invoice template - client name, engagement description, hours if applicable, rate, tax, and total.
For 80 to 120 clients, this took approximately 15 hours spread across the last three working days of the month and the first day of the new month. It was entirely manual, repetitive, and error-prone. The same data existed in the billing register Excel file that the billing coordinator maintained. She was, in effect, re-entering it by hand into a Word document for each client.
Common errors included wrong tax rates applied to international clients, incorrect fee amounts when the register had been updated after the invoice was already prepared, and inconsistent formatting across templates that had drifted from the original master over time.
What We Built
We built a VBA automation suite with three components: an invoice generation engine, a PDF naming and filing routine, and an Outlook email dispatch module.
The billing register was restructured with a consistent column layout and a client type field mapped to one of three invoice templates. This restructure took one afternoon and did not change the coordinator's data entry process - the new fields were additions to columns she was already maintaining.
The VBA macro iterates through each billing register row marked for the current period. For each row, it reads the client type, opens the matching Word template, populates the bookmarked fields - client name, address, invoice number, fee lines, tax, total, due date - saves the completed document as a PDF with a standardised filename, and adds the invoice to the email batch queue.
After all invoices are generated, the Outlook dispatch module sends each PDF as an attachment to the client email address stored in the billing register. The covering email uses a template with the client name personalised. The whole batch - 80-120 invoices - is sent within 12 minutes of the macro starting.
An exceptions log captures any rows where the macro encounters a data problem - missing email address, blank fee line, unrecognised client type. The batch completes for valid rows; exceptions appear in the log for the coordinator to resolve and reprocess. In the first month, three exceptions appeared; in subsequent months, typically zero or one.
Automation Capabilities
Multi-Template Invoice Generation
Three invoice template types (fixed fee, T&M, retainer) selected automatically from the client type field. Each template correctly formatted for its billing structure - no manual selection.
Automatic Invoice Numbering
Sequential invoice numbers generated from a control counter, written to the billing register row, and applied to the invoice document and PDF filename - consistent and auditable.
PDF Generation and Filing
Each invoice saved as a PDF with a standardised filename (YYYY-INV-NNNN-ClientName) in a dated monthly folder - no manual save-as, no inconsistent filenames.
Personalised Email Dispatch
Outlook sends each PDF to the client's billing contact with a personalised covering email. 120 clients contacted individually in one batch run without the coordinator touching Outlook.
Exception Logging
Rows with data issues logged to an exceptions sheet rather than stopping the batch. Failed invoices identified by client name and error type for quick resolution and reprocessing.
Billing Register Audit Trail
Invoice number, generated timestamp, and email sent confirmation written back to the billing register row - complete per-invoice audit trail without a separate system.
After Go-Live: What Changed
The billing coordinator now runs the invoice batch on the last working day of the month - a 15-minute task including the exceptions review. The 15 hours of manual invoice preparation no longer exist in the billing cycle.
Invoices reach clients on the last day of the month consistently. Previously, some clients did not receive their invoice until the 3rd or 4th of the following month. The earlier receipt moved average payment receipt date forward by 3 days - a cash flow improvement measurable in the firm's debtor days metric.
The formatting consistency change was noticed by clients before the firm told them about the automation. Three clients independently mentioned that the invoices looked "more professional" compared to earlier months - which was actually the result of removing the human variation that had accumulated across 120 manual productions per month.
“I used to spend the last three days of every month typing invoices. Same information, over and over, into the same template. Now I click the macro, review the exceptions list, and it's done in fifteen minutes. The partners noticed that invoices are going out faster and clients are paying sooner. It's been a completely different end of month.
Tech Stack
Excel VBA - billing register processing, invoice generation engine, exception logging, and audit trail
Word object model - template population via bookmarks; three template types for fixed fee, T&M, and retainer billing
PDF generation - Word to PDF conversion per invoice with standardised filename convention and dated folder filing
Outlook object model - personalised email dispatch with PDF attachment; 120 clients emailed in single batch run
Invoice counter and control sheet - sequential numbering with audit log of each monthly run
Frequently Asked Questions
How does VBA generate a formatted Word invoice from an Excel billing register?
The macro reads each row of the billing register in Excel - client name, fee description, hours, rate, and tax code - and uses the Word object model to open a template document, navigate to named bookmarks within the template, and insert the field values. It then saves the completed Word document as a PDF with a filename built from the client name and invoice number, and closes the Word file without saving the editable version. For a typical invoice with 3-8 line items, this takes about 4 seconds per invoice. 120 invoices complete in around 8 minutes.
Can the system handle different fee structures and invoice layouts for different client types?
Yes - the billing register includes a client type field that maps to one of three invoice templates: fixed fee, time-and-materials, and retainer. The VBA macro selects the correct template based on this field before populating it. Each template has its own bookmark structure and layout. Adding a new template type requires creating the Word template with the correct bookmarks and adding the template name to the mapping table - no VBA code change needed.
How are invoice numbers generated and tracked?
Invoice numbers are generated sequentially from a counter stored in a dedicated control sheet in the billing register workbook. The macro reads the current counter, increments it, writes the new number to the invoice record row, and updates the counter. The counter sheet also stores the last run date and the number of invoices generated in each run - providing an audit trail without a separate system. The format is configurable: this client uses YYYY-INV-NNNN.
What happens if the macro encounters an error partway through a batch?
The macro uses error handling that logs any failed invoice to an exceptions sheet rather than stopping the batch entirely. A failed invoice record shows the client name, the error type, and the row number in the billing register. The successful invoices are sent; the failed ones appear in the exceptions log for manual review. In practice, most errors are caused by a missing client email address or a malformed fee entry - identifiable and correctable in minutes. The macro can be re-run for exceptions-only after the data is corrected.
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
