The Customer Payment Reminder Tool in Excel VBA reads your open invoices, groups them by customer and creates one statement email per customer in Microsoft Outlook. It escalates across 4 reminder levels – Upcoming Due, Friendly Reminder, Second Reminder and Final Notice – and gives you 3 email actions: send directly, display for review, or save as drafts. The sample workbook holds 16 invoices for 8 customers, with 6 reminders due and 85,150 overdue.
Chasing unpaid invoices one email at a time takes hours every week, and it is easy to forget a customer or send the wrong tone. This customer payment reminder tool in Excel VBA turns the follow-up into a repeatable routine: update the Invoices sheet, check the reminder queue, click one button. Automated payment reminders go out from your own Outlook account, and every email is logged.
Important: the tool works only with the classic Microsoft Outlook desktop app on Windows, and Outlook must be open and signed in when you run it. It does not work with the new Outlook, Outlook on the web or Gmail. Need Gmail, another email system or a custom workflow? We offer customization – contact us.
Key Features of the Customer Payment Reminder Tool in Excel VBA
- One email per customer. Every open invoice of a customer is listed in one HTML statement with invoice date, due date, amount, paid, balance and days overdue, plus a total row and your payment details.
- Four escalation levels. Upcoming Due starts 5 days before the due date, Friendly Reminder from 1 day overdue, Second Reminder from 15 days and Final Notice from 30 days. The oldest open invoice decides the level.
- Send, display or draft. Choose to send immediately, open each email for review without sending, or save everything as Outlook drafts.
- Test mode. Enter your own address as the test-mode email and every email is redirected to you while you set the tool up.
- Cool-down days. A customer reminded within the cool-down period (default 7 days) is skipped unless the reminder level has gone up.
- Skip rules with reasons. Customers marked Do Not Remind, or with a missing email address, appear in the queue as skipped so nothing happens silently.
- Payment reminder email template per level. Each level has its own subject and message with placeholders such as {Customer Name}, {Total Due} and {Max Days Overdue}, and **bold** markup.
- Email Log and invoice stamping. Every email is logged; after a Send run each invoice is stamped with the last reminder level, the date and the reminder count.
Sheets Explanation
The workbook has six sheets: Dashboard, Invoices, Customers, Templates, Email Log and How to Use. The download also includes an illustrated PDF user manual.
Dashboard – Run Options and Actions
Run options cover the email action, statement as-of date (blank means today), cool-down days, show not-yet-due invoices, add Outlook signature, test mode email, company name, currency, payment details and closing text. The action buttons are Send Payment Reminders, Preview Selected Customer, Mark Selected Invoices Paid, Refresh Dashboard, Clear Email Log and Reset Reminder History.
Dashboard – Summary, Last Run, Ageing and Reminder Queue
Four summary cards show Customers to Remind (6), Total Overdue (85,150), Sent in this Run and Displayed / Drafted. The Last Run panel records the timestamp, process, email action and counts of sent, displayed, drafted and failed emails. The ageing panel splits the 104,050 open balance into Not yet due, 1-30, 31-60, 61-90 and over 90 days overdue, and the reminder queue shows who gets which level and for how much.
Invoices
One row per invoice. Balance, Days Overdue and Ageing are calculated, and Status can be Open, Disputed, On Hold or Paid, so disputed invoices are never chased by mistake. Select any row and use Preview Selected Customer or Mark Selected Invoices Paid directly on this sheet.
Customers, Templates, Email Log and How to Use
Customers holds the contact person, email, CC, phone, payment terms and the Do Not Remind flag. Templates holds the four levels with their start days, subject and message. Email Log records the timestamp, process, recipient, subject, number of invoices, action and status of every email. How to Use explains each step for anyone on your team.
The Overdue Invoice Reminder in Outlook
Here is a Final Notice for a customer with two overdue invoices: a personal greeting, the level message, the invoice table with days overdue in red, the total and the “How to pay” block.
Customer Payment Reminder Tool vs. Paid Excel Add-in vs. QuickBooks / Xero Reminders – Feature Comparison
| Feature | Customer Payment Reminder Tool | Paid Excel / Outlook Add-in | QuickBooks / Xero / Chaser |
|---|---|---|---|
| Cost | ✅ $9.99 one-time | Annual licence | Monthly subscription |
| Platform | Excel (Windows) + classic Outlook | Excel + Outlook | Web app |
| Setup time | ✅ Under 15 minutes | Install and configure | Hours (import customers and invoices) |
| One statement email per customer | ✅ Yes | Varies | ✅ Yes |
| Escalation levels | ✅ 4 editable levels | Varies | ✅ Yes |
| Review before sending | ✅ Display or drafts | Varies | Limited |
| Data stays on your PC | ✅ Yes | ✅ Yes | No (cloud) |
| Mobile access | No | No | ✅ Yes |
| Year-1 cost at 5 users | ✅ $9.99 | Per-seat licences | Subscription x 12 months |
For small finance teams that want automated payment reminders without a new accounting platform or a monthly fee, the Customer Payment Reminder Tool sits in the sweet spot.
Who Should Use This Template
Perfect for:
- Accounts receivable teams that already keep invoices in Excel and email from classic Outlook
- Small businesses, wholesalers, agencies and service firms with tens to a few hundred customers
- Bookkeepers who run collections for several clients and need a consistent routine
Not a fit if:
- You use the new Outlook, Outlook on the web, Gmail or a Mac (ask us about a customized version)
- You need online payment links or reminders triggered automatically from an accounting system
Real-World Use Cases
Maria runs accounts receivable at a 25-person wholesale distributor. Each Monday she pastes the open invoice list into the Invoices sheet, checks the queue and sends about 30 statements in one click instead of writing each overdue invoice reminder by hand. The Email Log gives her manager a record of every chase.
James owns a small design agency. He keeps the Display option on so he can read every email before it goes out. Long-standing clients get a friendly tone, and only genuinely late accounts reach Final Notice.
Priya is a freelance bookkeeper for four clients. She keeps one workbook per client with that company’s name, currency and bank details, and uses Save as drafts so each client can approve the batch.
Advantages of the Customer Payment Reminder Tool
- Hours saved every week – one click replaces dozens of hand-written emails.
- Consistent escalation – every customer is on the right level, based on the oldest open invoice.
- No double reminders – cool-down days and skip rules prevent annoying repeat emails.
- Safe testing – test mode and the Display option mean nothing is sent until you are ready.
- One-time cost – no subscription and no per-user fee.
Opportunities for Improvement
- It needs the classic Outlook desktop app on Windows; Gmail and the new Outlook need a customized version.
- Emails include bank or payment details you type in, not an online payment link.
- Invoices are entered or pasted into the workbook; there is no direct sync with an accounting system.
Best Practices
- Always do the first run with a test-mode email and the Display option.
- Mark disputed or on-hold invoices in the Status column before each run.
- Record part-payments in Amount Paid so balances stay correct.
- Keep the cool-down at 7 days or more so customers have time to pay.
- Review the Email Log weekly and clear it at the start of a new cycle.
Want to understand how Excel talks to Outlook? See Microsoft’s Outlook VBA object model reference, or our tutorial on how to send bulk emails using VBA and Outlook if you want to send email from Excel VBA yourself.
Explore Relevant Templates
- Email Reminder Scheduler in Excel – scheduled reminder emails for any date-based task.
- Outlook Calendar Meeting Creator in Excel VBA – create Outlook meetings from Excel rows.
- Outlook Calendar Events Automation in Excel VBA – push events from a sheet into Outlook.
- Accounts Receivable KPI Dashboard in Excel – track collections alongside your reminders.
- VBA Management Systems Mega Pack – five premium Excel VBA systems in one bundle.
Frequently Asked Questions
Does the Customer Payment Reminder Tool work with Gmail or the new Outlook?
No. The Customer Payment Reminder Tool works only with the classic Microsoft Outlook desktop app on Windows, which must be open and signed in. It does not work with the new Outlook, Outlook on the web or Gmail. We can customize it for Gmail or another email system – contact us.
Can the tool be customized for my business?
Yes. We offer paid customization of the Customer Payment Reminder Tool, such as connecting it to Gmail or other email systems, adding fields, changing reminder rules or building a custom collections workflow. Contact us with your requirements.
How is the reminder level chosen?
The oldest open invoice of each customer decides it: Upcoming Due from 5 days before the due date, Friendly Reminder from 1 day overdue, Second Reminder from 15 days and Final Notice from 30 days. You can change these thresholds and the payment reminder email template on the Templates sheet.
Can I review emails before they are sent?
Yes. Choose Display emails for review to open every email in Outlook without sending, or Save as drafts. Preview Selected Customer opens one customer’s email, and the test-mode address sends everything to you while testing.
How does it compare to Chaser or QuickBooks payment reminders?
Cloud tools charge a monthly fee and hold your customer data online. The Customer Payment Reminder Tool is a one-time Excel VBA purchase that keeps data on your PC and sends through your own Outlook, but it has no online payment links or mobile app.
How long does setup take?
Most users have the Customer Payment Reminder Tool running in under 15 minutes: enable macros, enter company and payment details, add customers and invoices, then do a test run with the Display option. The PDF manual covers every step.
About the Author
Built by PK – Microsoft Certified Professional with 15+ years of Excel, Google Sheets and Power BI experience. Founder of NextGenTemplates, reaching 300K+ subscribers across YouTube channels. Every template is hand-built and tested before release.
Conclusion
The Customer Payment Reminder Tool in Excel VBA gives a small finance team a dependable collections routine: one clear statement per customer, the right escalation level every time, and a full log of what was sent – all from Excel and classic Outlook.
Click here to Purchase the Customer Payment Reminder Tool in Excel VBA
✅ Instant download · One-time payment · No subscription
Watch more Excel VBA tutorials at Youtube.com/@PKAnExcelExpert
Last updated: October 2026
