Automated email with attachment from Outlook

Concept

1. Requirements — read first

Windows + classic Outlook desktop app, set up with your email account.
The new Outlook for Windows and Outlook on the web do not support VBA/COM automation. If your PC uses new Outlook, switch back to classic Outlook, or use Power Automate (Module 6) or Python (Module 5) instead.
Mac Outlook doesn't support this either.
Your company may show a security prompt or block programmatic sending. Test with one email first.

2. The smallest working example

Sub SendTestMail()
    Dim olApp As Object, mail As Object
    Set olApp = CreateObject("Outlook.Application")
    Set mail = olApp.CreateItem(0)              ' 0 = mail item

    With mail
        .To = "rahul@example.com"
        .Subject = "Test from Excel"
        .Body = "Hello! This email was created by a macro."
        .Display                                 ' opens it for review
    End With
End Sub

.Display opens the email so you can check it. Replace with .Send to send directly.

CreateObject (late binding) works without adding any reference, so the file works on other PCs with different Outlook versions.

3. Mail merge with attachments

Use the Customers list from Lesson 2 and the PDFs it created.

Sheet "Customers": A = CustomerID, B = Name, C = Email, D = Status (filled by the macro).

Option Explicit

Sub EmailStatements()
    Const SEND_NOW As Boolean = False           ' False = Display for review, True = Send
    Dim ws As Worksheet, olApp As Object, mail As Object
    Dim lastRow As Long, i As Long, sent As Long
    Dim folder As String, pdfPath As String
    Dim custID As String, custName As String, email As String

    On Error GoTo ErrHandler
    Set ws = ThisWorkbook.Worksheets("Customers")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    folder = ThisWorkbook.Path & Application.PathSeparator & "PDF_" & Format(Date, "yyyy-mm")

    Set olApp = CreateObject("Outlook.Application")

    For i = 2 To lastRow
        custID = Trim(CStr(ws.Cells(i, "A").Value))
        custName = Trim(CStr(ws.Cells(i, "B").Value))
        email = Trim(CStr(ws.Cells(i, "C").Value))
        pdfPath = folder & Application.PathSeparator & custID & "_" & custName & ".pdf"

        If email = "" Or InStr(email, "@") = 0 Then
            ws.Cells(i, "D").Value = "Skipped: no valid email"
        ElseIf Dir(pdfPath) = "" Then
            ws.Cells(i, "D").Value = "Skipped: PDF not found"
        Else
            Set mail = olApp.CreateItem(0)
            With mail
                .To = email
                .Subject = "Account statement - " & Format(Date, "mmmm yyyy")
                .Display                                   ' loads your default signature
                .HTMLBody = "<p>Dear " & custName & ",</p>" & _
                            "<p>Please find attached your statement for " & Format(Date, "mmmm yyyy") & ".</p>" & _
                            "<p>Regards,</p>" & .HTMLBody     ' text first, then signature
                .Attachments.Add pdfPath
                If SEND_NOW Then .Send
            End With
            ws.Cells(i, "D").Value = IIf(SEND_NOW, "Sent ", "Drafted ") & Format(Now, "dd-mmm hh:mm")
            sent = sent + 1
        End If
    Next i

    MsgBox sent & " emails " & IIf(SEND_NOW, "sent.", "opened for review."), vbInformation
    Exit Sub

ErrHandler:
    MsgBox "Stopped at row " & i & ": " & Err.Description, vbExclamation
End Sub

4. The important details

SEND_NOW = False first. Run it, look at 2–3 emails, close them without sending. When everything is right, change to True.
Signature trick: .Display loads your default signature into .HTMLBody; we put our text before it.
Status column D is your log: who got it, who was skipped and why. Rerun safely by filtering out rows already marked "Sent".
CC / BCC: add .CC = "accounts@yourshop.com".
Multiple attachments: call .Attachments.Add once per file.

5. Be a good sender

Outlook and your mail provider have sending limits. For more than ~50 emails, add a short pause between sends:

Application.Wait Now + TimeValue("00:00:02")

And only email people who expect to hear from you.

Common mistakes

Testing with .Send on the full list. Using the new Outlook (no COM support). Plain .Body together with a signature (formatting is lost). File name in the email macro not matching the export macro.

What you'll learn