VBA Send Email From Excel: Auto-Email AR Aging via Outlook

This guide is part of the FinDataPro Excel VBA Workbook Events series.

The Outlook Email Loop

Run this against an AR aging sheet with a customer, email, amount, and days-overdue column:

Option Explicit

Sub EmailAgingReportsToCustomers()
    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("ARAging")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim i As Long, olMail As Object
    For i = 2 To lastRow
        Set olMail = olApp.CreateItem(0) ' 0 = olMailItem

        With olMail
            .To = ws.Cells(i, "B").Value
            .Subject = "Outstanding Balance Reminder — " & ws.Cells(i, "A").Value
            .Body = "Dear " & ws.Cells(i, "A").Value & "," & vbCrLf & vbCrLf & _
                    "Our records show an outstanding balance of " & _
                    Format(ws.Cells(i, "C").Value, "Currency") & _
                    ", now " & ws.Cells(i, "D").Value & " days overdue."
            .Display ' Use .Send only once reviewed — see this post's notes
        End With
    Next i
End Sub

CreateObject("Outlook.Application") requires no library reference — it late-binds to whatever Outlook is installed on the machine.

Where the Code Goes, and What It Requires

This lives in a standard module. Unlike every other macro in this series, it has a hard external dependency: Outlook must be installed and configured with a working mail profile on the machine running it. It cannot run in a browser-only Microsoft 365 session, and it cannot be tested in a sandboxed environment with no desktop Outlook — this is one to run and test locally, not to trust blind on someone else's machine without checking first.

Finance Use Case: Aging Reminders Without a CRM

Chasing overdue receivables by manually opening Outlook, composing an email, and copying the balance across from Excel does not scale past a handful of customers. This loop turns an AR aging sheet directly into a batch of drafted, correctly-addressed reminder emails — reviewed once in Outlook's own interface before anything actually sends.

.Display vs .Send — Test Safely

.Display opens each drafted email in Outlook for manual review — nothing sends until you personally click Send inside Outlook. Only swap to .Send once the recipient list, subject, and body are fully verified, and never test .Send against real customer addresses in a demo or shared copy of the workbook.

Common Mistakes

• Calling .Send straight away without ever reviewing drafted emails with .Display first.
• Leaving real customer email addresses in a workbook that gets shared or demoed — use obviously fake addresses in any sample or template copy.
• Assuming this will run unattended on a server — it requires an active Outlook session on a real desktop, and may trigger Outlook's Object Model Guard security prompt.
• Not wrapping the loop in proper error handling — a single malformed email address should not silently stop every remaining customer from being emailed.

Related Guides on This Series

Pair this with Export Range to PDF to attach a generated statement to each email, or On Error GoTo so one bad row does not silently halt the whole batch.

Full reference: Microsoft Learn Outlook Application object reference.

Frequently Asked Questions

Conclusion

A late-bound Outlook.Application loop turns an AR aging sheet directly into a batch of reviewed, ready-to-send reminder emails. Test with .Display first, always, and pair this with AR Intelligence Manager if the aging calculation itself is still manual.

Prashant Panchal• ACA | FMVA® | 19 Years in Finance

Prashant Panchal is a Chartered Accountant (ACA) and Financial Modelling & Valuation Analyst (FMVA®) with 19 years of experience in finance, FP&A, and financial modelling across the GCC region. He is the founder of FinDataPro.