[Word]Mail Merge at Scale with AI

Summary: Use Word mail merge + VBA to turn one template letter into hundreds of named PDFs.

1. The Pain Points

Sending acceptance letters each semester, pay stubs every month, and meeting invitations before a conference — these tasks share one thing in common: about 90% of each letter is identical, and only the "blanks" such as name, amount, and time differ.

If you do it by hand: open the template → change the name → save as PDF → change the next one… After a few hundred letters, your hand is worn out from clicking, and it's easy to paste into the wrong row or skip a recipient.

Actually, there's an old trick that automates this: Word's mail merge. Pair it with a VBA macro, and you can export the hundreds of generated letters into separate PDFs with a single click. This article walks you through getting that pipeline running.

2. What You'll Produce

After reading and following along, you'll have:

An Excel roster (the data source) with columns: Name, Major, Scholarship Level, Personalized Congratulatory Note;
A Word template letter (the main document) using "merge fields" as placeholders;
Run the macro once, and out come hundreds of independent PDFs: AcceptanceLetter_1.pdf, AcceptanceLetter_2.pdf…, each with the right name and details.

Prerequisites: You have Word and Excel on your computer (version 2016 or later is fine; this article is written using the Word 365 / 2019 interface); you know basic copy-and-paste; and you're willing to open the "Developer" tab once (we'll show you how later).

3. Hands-on Walkthrough

Our approach has three steps: first use AI to batch-write the "one line that differs in each letter"; then set up mail merge in Word to pair the template with the roster; finally use a VBA macro to save the results as PDFs in bulk.

Step 1: Use AI to write the personalized body, then organize it into an Excel roster

The "data source" for mail merge is simply an Excel table, where each column in the first row is a "blank" you can fill into the letter. Let's prepare this table first.

Step 1 (enter the fixed info): In Excel, create a new table, write the headers in the first row, then fill in the data below:

Name	Major	Scholarship Level
Zhang San	Computer Science	First Class
Li Si	Finance	Second Class

Step 2 (let AI fill the "Personalized Congratulatory Note" column): Send the table above to your usual AI assistant with this prompt:

Here is my admission roster in Excel with three columns: Name, Major, Scholarship Level. For each student, write one personalized congratulatory line of about 25 words — it must mention their name and major, in a sincere, warm tone. Follow the roster order strictly, one line per student, no numbering, so I can copy-paste straight into a new Excel column.

The AI will spit out a string of name-tagged sentences, for example:

Congratulations, Zhang San — welcome to the Computer Science program! A brilliant future awaits!
Congratulations, Li Si — the Finance program invites you! May every path ahead be bright!

Step 3 (paste back into Excel): Add a "Congratulatory Note" column to the table and paste the AI's text in row by row.

Key point: The number of rows the AI generates must correspond one-to-one with the roster rows, and the order must not be scrambled — otherwise letters will get the wrong person's details. This is the easiest step for manual errors; after pasting, eyeball-check it once.

Step 2: Set up mail merge in Word

Mail merge = pairing one template letter with a roster to automatically generate N letters with different content. Let's go step by step; follow along and click each step.

Step 1 (write the template): Open Word and write a "generic" acceptance letter, leaving the variable parts blank for now. For example:

Dear «Name», Congratulations on your admission to the «Major» program, with a «Scholarship Level» scholarship. «Congratulatory Note» — Admissions Office

Step 2 (connect the data source): Click the top menu Mailings → Start Mail Merge → Letters; then click Select Recipients → Use an Existing List, pick the Excel file you just made, and choose the correct worksheet (usually Sheet1).

Step 3 (insert fields): Place the cursor where you want the blank, click Mailings → Insert Merge Field, and choose the matching column name (Name / Major / Scholarship Level / Congratulatory Note). Once inserted, the placeholder appears as «Name» with guillemets (angle quotes) — this is a "merge field", the equivalent of a "fill-in box" in the letter.

Step 4 (preview): Click Preview Results and use the left/right arrows to flip through, confirming each name matches.

Step 5 (generate the big document): Click Finish & Merge → Edit Individual Documents, choose "All", and confirm. Word generates a new document with hundreds of letters strung end to end.

Key point: Don't rush to save PDFs manually at this step. "Edit Individual Documents" is just to give our macro later a "single block it can slice up."

Step 3: Use a VBA macro to batch-export into separate PDFs

A macro = a sequence of recorded or written operation steps that reruns automatically at the click of a button. What our macro does is simple: it slices the big document from the previous step "by each letter" and saves each one as a PDF.

Step 1 (open the macro editor): In Word, press Alt + F11 to open the VBA editor; in the "Project Explorer" on the left, right-click your document → Insert → Module. If you don't see the "Developer" tab, first enable it under File → Options → Customize Ribbon. Note: To make the macro persist, save the document as .docm (a macro-enabled Word document) first — a normal .docx doesn't store macros, so they vanish when you close and reopen.

Step 2 (paste the code and change the path): Paste the entire block of code below into the module window. VBA = Visual Basic for Applications, the built-in "programming" language in Word/Excel, designed for repetitive tasks.

vba
Sub ExportPDFsBySection()
    'Purpose: take the big document generated by "Mail Merge -> Edit Individual Documents",
    '         slice it into individual files by section break, and export each letter as a PDF.
    'Prerequisite: the mail merge main document type must be "Letters" — the Letters type inserts
    '         a section break between records, so the "one record = one Section" assumption is reliable.
    'Usage: finish the mail merge and "Edit Individual Documents" first, then run this macro.
    
    Dim doc As Document, newDoc As Document
    Dim sec As Section
    Dim i As Long
    Dim outPath As String
    Dim fso As Object
    
    '★ Point this at your own export folder; no need to create it manually first - the macro creates it automatically (fault-tolerant)
    outPath = "C:\PDFExport\"
    
    '★ outPath fallback: create the folder automatically if it does not exist (requires the parent directory to exist, e.g. C:\)
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(outPath) Then fso.CreateFolder outPath
    
    Set doc = ActiveDocument
    i = 0
    Application.ScreenUpdating = False
    
    '★ Section-count self-check: verify the "one record = one Section" assumption holds before iterating,
    '   to avoid silently exporting just 1 PDF. Only 1 section in the whole document usually means the
    '   "Letters" type was not picked or "Edit Individual Documents" was not clicked yet;
    '   with a single record there is naturally just 1 section, which is normal.
    If doc.Sections.Count < 2 Then
        MsgBox "This document contains only 1 section." & vbCr & _
               "Please confirm: 'Start Mail Merge' used the 'Letters' type, and you ran 'Finish & Merge -> Edit Individual Documents'." & vbCr & _
               "(If you really have just 1 record, 1 section is normal and you may continue.)", vbExclamation
    End If
    
    For Each sec In doc.Sections
        i = i + 1
        sec.Range.Copy                 'Copy this "section" (one letter)
        Set newDoc = Documents.Add     'Create a new blank document
        newDoc.Range.Paste            'Paste into it (pasting at a target does not depend on cursor position — safer than Selection.Paste)
        'Export as PDF, with a sequence number in the file name
        newDoc.ExportAsFixedFormat _
            OutputFileName:=outPath & "Notice_" & i & ".pdf", _
            ExportFormat:=wdExportFormatPDF
        newDoc.Close SaveChanges:=False
    Next sec
    
    Application.ScreenUpdating = True
    MsgBox "Done! Exported " & i & " PDFs to " & outPath, vbInformation
End Sub

Key point (confirm before running): This macro splits by section break (doc.Sections — one letter per section). Whether section breaks are generated depends on the document type chosen in "Start Mail Merge", not on the Word version — when you choose the "Letters" type, Word inserts a section break between each record, so the assumption "one record = one Section" is reliable. The macro starts with a "section-count self-check": if the whole document has only 1 section, a popup reminds you to check whether you picked the Letters type and actually clicked "Finish & Merge → Edit Individual Documents", so it won't silently export just 1 PDF. (The "split by page" fallback approach doesn't apply in this scenario, so we no longer use it.)

Step 3 (run it): Place the cursor inside the code and press F5 to run. When the "Done" dialog pops up, go to C:\PDFExport\ and you'll find a bunch of Notice_1.pdf, Notice_2.pdf….

Key point: Just fill outPath with a path you have write permission for (e.g. C:\PDFExport\); the macro **creates the folder automatically**, so you don't need to create it manually. Want to name files by person? Replace & i with logic that reads the name field (covered in the advanced section).

4. How It Works

In one sentence: the essence of mail merge is pairing two things — "template + data" — where the template provides the unchanging framework and the data (the Excel roster) provides the per-letter blanks, while Word cranks out every combination for you; and the essence of a VBA macro is turning the mechanical repetition of "copy this section, save as PDF, close it, next section" into a loop, so the computer clicks the mouse hundreds of times for you. AI does just one thing in this chain: batch-writes those one-of-a-kind lines tailored to each person, sparing you the most brain-taxing part.

5. Troubleshooting

Merge fields show as «Field» instead of the real name? That's the normal "code view". Click Mailings → Preview Results to see the real names; printing or exporting to PDF also uses the real content, so don't worry.
The macro errors with "path not found" right away? The macro already creates the folder automatically; if it still errors, it's most likely a wrong outPath, a non-existent disk, or no write permission (e.g. blocked when writing to the system drive). Just switch to a path you have permission for.
The Excel roster can't be read / fields are empty? Check three things: the header row must not contain merged cells; don't leave a blank row as a title; and column names shouldn't have weird symbols. The safest setup is a clean header row in the first row with data right below.
The line breaks in the congratulatory note disappeared? To break a line inside an Excel cell, use Alt + Enter for an "in-cell line break" — don't just press Enter (that jumps to the next row and becomes a new record). The paragraph breaks are preserved after merging.
The macro won't open / a warning appears the moment you open the document? Word disables macros by default. Save the document as .docm (a macro-enabled Word document), and add the document's folder to "File → Options → Trust Center → Trust Center Settings → Trusted Locations" — far safer than globally enabling all macros (only do this on your own computer with trusted files).

6. Advanced Extensions

Want the roster to "grow itself"? The data source often needs cleaning from messy system-exported tables — for that, see B5 (Power Query chapter): use Power Query to clean the raw table into a tidy mail-merge data source, no manual copy-paste.
Want to send different versions of the letter by condition? For example, a long version for first-class scholarships and a short version for second-class — this is conditional batching, advancing to L4's "VBA conditional merge": read the 奖学金等级 field in the macro to decide which body text to apply before exporting.
Want file names to use the person's name directly? Change & i in the macro to read the value from the first merge field of each section; the next L4 hands-on piece will give you the revised code.

🛠️ Mail Merge at Scale with AI