📎 This article includes 1 downloadable practice file ↓

Sending the same report to twenty people with slightly different numbers is exactly the kind of job VBA was made for. This macro reads a list in Excel and creates one Outlook email per row. It has a preview mode so you can check everything before anything is sent.

Set up the sheet

The macro

Option Explicit

Sub SendMailMerge()
    Const PREVIEW_ONLY As Boolean = True   ' change to False to send
    Dim ol As Object, mail As Object
    Dim ws As Worksheet, r As Long, lastRow As Long

    Set ws = ThisWorkbook.Sheets("Mail")
    Set ol = CreateObject("Outlook.Application")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    For r = 2 To lastRow
        If ws.Cells(r, "F").Value <> "Sent" Then
            Set mail = ol.CreateItem(0)   ' 0 = olMailItem
            With mail
                .To = ws.Cells(r, "B").Value
                .CC = ws.Cells(r, "C").Value
                .Subject = "Your monthly statement"
                .HTMLBody = "<p>Hi " & ws.Cells(r, "A").Value & ",</p>" & _
                            "<p>Your balance this month is <b>&#8377;" & _
                            Format(ws.Cells(r, "D").Value, "#,##0") & "</b>.</p>" & _
                            "<p>Regards,<br>Atul</p>"
                If Len(ws.Cells(r, "E").Value) > 0 Then
                    If Dir(ws.Cells(r, "E").Value) <> "" Then
                        .Attachments.Add ws.Cells(r, "E").Value
                    Else
                        ws.Cells(r, "F").Value = "Missing attachment"
                        GoTo NextRow
                    End If
                End If
                If PREVIEW_ONLY Then .Display Else .Send
            End With
            ws.Cells(r, "F").Value = IIf(PREVIEW_ONLY, "Previewed", "Sent")
        End If
NextRow:
    Next r
End Sub

Why it is written this way

• Late binding (CreateObject) means you do not need to tick the Outlook reference, and the file works on PCs with different Office versions.
• PREVIEW_ONLY opens each email instead of sending it. I never run a mail macro for the first time without it.
• The Status column makes the macro safe to re-run: rows already marked “Sent” are skipped.
• Dir() checks the attachment exists before adding it, so one missing PDF does not stop the whole batch.

FAQ

Can I send from a shared mailbox?

Yes: set .SentOnBehalfOfName = "[email protected]" (you need “send on behalf” permission).

Does this work with the new Outlook for Windows?

No. The new Outlook does not support COM automation. It works with classic Outlook desktop.

Try it yourself: step by step

1. Download mail-merge-list.xlsx and send-mail-merge.bas. Save the workbook as .xlsm.
2. Replace the example.com addresses with your own address in every row for the first test.
3. Alt+F11 → File → Import File → send-mail-merge.bas.
4. Run SendMailMerge (Alt+F8). With PREVIEW_ONLY = True four draft emails open — nothing is sent, and column F says “Previewed”.
5. Clear column F, set PREVIEW_ONLY = False and run again to send to yourself. Only then put real recipients in.