Send Personalized Emails from Excel with AI and Outlook

Step 1: Structure the Sheet

Step 2: The Drafting Prompt

You write one paragraph for a business follow-up email.
Sender: Alex from BrightCare Equipment.
Purpose: remind the customer their service plan renews soon and offer a call.
Rules:
- 2–3 sentences, under 60 words, friendly and plain.
- Use ONLY the facts given. Do not invent prices, discounts or dates.
- Mention one specific fact to show this is personal.
- No greeting and no sign-off (the template adds them).
- If the facts are insufficient, return exactly: NEEDS REVIEW

Customer: Sarah, Northside Dental
Facts: Bought 2 chairs Mar 2026; asked about service plan; renewal 1 Dec

Step 3: Review in the Sheet

• Filter Draft for NEEDS REVIEW and fix the facts or write those by hand.

• Read every draft for a list under 50 rows; read a random 20% for larger lists, plus all high-value customers.

• Check for anything that sounds like a promise: prices, deadlines, guarantees.

• Set Status to Approved only for rows you are happy to send.

Step 4: Create the Outlook Emails With VBA

Option Explicit

Sub CreateFollowUpEmails()
    Const SEND_NOW As Boolean = False     ' False = save as drafts
    Const BATCH_PAUSE_SECONDS As Long = 20
    Const BATCH_SIZE As Long = 25

    Dim ol As Object, mail As Object
    Dim lo As ListObject, r As ListRow
    Dim body As String, n As Long

    Set lo = ThisWorkbook.Worksheets("Contacts").ListObjects("tblContacts")
    Set ol = CreateObject("Outlook.Application")

    For Each r In lo.ListRows
        If r.Range(1, lo.ListColumns("Status").Index).Value = "Approved" Then
            body = "Hi " & r.Range(1, lo.ListColumns("FirstName").Index).Value & "," & _
                   vbCrLf & vbCrLf & _
                   r.Range(1, lo.ListColumns("Draft").Index).Value & _
                   vbCrLf & vbCrLf & _
                   "Best regards," & vbCrLf & "Alex" & vbCrLf & "BrightCare Equipment"

            Set mail = ol.CreateItem(0)
            With mail
                .To = r.Range(1, lo.ListColumns("Email").Index).Value
                .Subject = "Your service plan renewal"
                .Body = body
                If SEND_NOW Then .Send Else .Save
            End With

            r.Range(1, lo.ListColumns("Status").Index).Value = IIf(SEND_NOW, "Sent", "Drafted")
            r.Range(1, lo.ListColumns("SentAt").Index).Value = Now
            n = n + 1
            If SEND_NOW And n Mod BATCH_SIZE = 0 Then
                Application.Wait Now + TimeSerial(0, 0, BATCH_PAUSE_SECONDS)
            End If
        End If
    Next r

    MsgBox n & " emails " & IIf(SEND_NOW, "sent.", "saved to Drafts."), vbInformation
End Sub

Adding attachments

Choosing the Right Sending Method

Rules That Keep You Out of Trouble

• Only email people who have a business relationship with you or have opted in, and honour opt-outs.

• Keep a Skip status and never remove rows; it is your record of who asked not to be contacted.

• Keep AI facts factual. If a fact is old or uncertain, leave it out.

• Do not send personal or health information in email bodies.

• Respect your mailbox provider's sending limits; batch and pause.

Industry Uses We See Most

• Clinics and dental practices

  Recall reminders that mention the last visit type; see our dental patient recall tracker guide.

• Insurance agencies

  Renewal reminders that reference the policy type and renewal date.

• Accounting firms

  Document request emails listing exactly what is still missing per client.

• B2B suppliers

  Re-order prompts based on the last order date and usual order interval.