# 📬 B07｜Mail Merge at Scale with AI — Companion Assets

> **Series**: AI Office Practice: From Beginner to Expert
> **Article**: B7 (Track 1 Text / Level 3 Intermediate)
> **Title (CN)**: 用 AI + 邮件合并批量生成通知
> **Title (EN)**: Mail Merge at Scale with AI

---

## 1. Who is this for? What problem does it solve?

For anyone sending the **same letter** (admission notices, payslips, meeting invites) to dozens or hundreds of people: opening a template, finding/replacing a name, saving as PDF — over and over. After a hundred copies your hand is numb and the risk of mixing up names is real.

This folder delivers a **real, runnable** Word mail-merge pipeline:

- A **sample data source** (`数据源_sample.csv`) with 6 records covering Name / Major / Scholarship Level / Email / Issue Date / Congratulations message;
- A **Word template guide** (`通知模板_en.md`) walking you through building the merge-fields template;
- A **VBA macro** (`导出PDF宏_en.bas`) that splits the merged document into one PDF per record.

After running this, "hundreds of manual clicks" become "press F5 once and wait two seconds".

---

## 2. How to use (5-step pipeline)

### Step 1 — Prepare the data source (30 seconds)

Open `数据源_sample.csv` to see the format. For your own list, create an Excel table with header row `Name,Major,ScholarshipLevel,Email,IssueDate,Congratulations`. For the *Congratulations* column, ask an AI (ChatGPT, Tongyi, etc.) to write a 25-character personalized sentence per row — paste the AI output line by line and **double-check the row count matches**.

### Step 2 — Build the Word template (5 minutes)

Follow the steps in `通知模板_en.md` section 2:

1. Ribbon → **Mailings → Start Mail Merge → Letters** (not *Directory* or *Labels*);
2. **Mailings → Select Recipients → Use an Existing List** → pick `数据源_sample.csv`;
3. Insert six MERGEFIELDs at the right positions (**Mailings → Insert Merge Field**);
4. **Mailings → Finish & Merge → Edit Individual Documents → All → OK**.

> ⚠️ **Critical**: only the *Letters* type inserts section breaks; without them the macro will produce a single PDF. **Do not close** the new merged document — the macro reads it next.

### Step 3 — Enable macros in Word (first time only)

Word disables macros by default. Two quick changes:

1. **File → Save As → Word Macro-Enabled Document (`.docm`)** — regular `.docx` strips macros on save;
2. **File → Options → Trust Center → Trust Center Settings → Macro Settings**, choose *Enable all macros* (only for files **you wrote** on **your own** computer — **never** enable macros for attachments from strangers);
3. If the **Developer** tab is hidden, enable it via *File → Options → Customize Ribbon*.

### Step 4 — Import the VBA macro (30 seconds)

Press `Alt + F11` to open the VBA editor → **right-click any entry in the Project pane → Import File** → pick `导出PDF宏_en.bas`. A module named `B07_ExportPDFMacro_en` appears.

### Step 5 — Run the macro, get your PDFs (2 seconds)

Back in the merged Word document, press `Alt + F8` → choose **`ExportPDFs_OnePerSection`** → *Run*. Wait for the "Done" dialog, then check `C:\ExportedPDFs\` for `Notice_1.pdf, Notice_2.pdf, …`.

---

## 3. File list

| File | Type | Purpose | Lines |
|------|------|---------|-------|
| `B07_MailMerge_EnglishREADME.md` | Markdown | This file (English instructions) | — |
| `B07_邮件合并_中文README.md` | Markdown | Chinese instructions | — |
| `数据源_sample.csv` | CSV | Sample data source (6 rows) | 7 |
| `通知模板_en.md` | Markdown | Word template body + field reference (English) | 70 |
| `通知模板_zh.md` | Markdown | Same, Chinese | 80 |
| `导出PDF宏_en.bas` | VBA | Split + export PDFs (English comments) | 130 |
| `导出PDF宏_zh.bas` | VBA | Same, Chinese comments | 130 |

---

## 4. Practical code (VBA core, line-by-line comments)

```vba
' ============================================================
' 导出PDF宏_en.bas — Core snippet: split by section + export PDF
' ============================================================

Sub ExportPDFs_OnePerSection()       ' Main entry (also runnable via Alt+F8)
    Dim doc As Document, newDoc As Document
    Dim sec As Section
    Dim i As Long
    Dim outPath As String
    Dim fso As Object

    outPath = "C:\ExportedPDFs\"      ' ★ Change to your folder (auto-created if missing)

    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(outPath) Then fso.CreateFolder outPath

    Set doc = ActiveDocument
    i = 0
    Application.ScreenUpdating = False  ' Speed up: skip screen redraws

    ' Section-count sanity check: 1 section usually means Letters type wasn't picked
    If doc.Sections.Count < 2 Then
        MsgBox "Only 1 section. Please choose 'Letters' and run 'Edit Individual Documents'.", _
               vbExclamation
    End If

    For Each sec In doc.Sections         ' Loop over each section
        i = i + 1
        sec.Range.Copy                   ' Copy the section (one letter)
        Set newDoc = Documents.Add       ' New blank document as container
        newDoc.Range.Paste               ' Paste the section into it

        ' Export to PDF, indexed file name
        newDoc.ExportAsFixedFormat _
            OutputFileName:=outPath & "Notice_" & i & ".pdf", _
            ExportFormat:=wdExportFormatPDF

        newDoc.Close SaveChanges:=False  ' Close the temp document
    Next sec

    Application.ScreenUpdating = True
    MsgBox "Done. Exported " & i & " PDF(s) to " & outPath, vbInformation
End Sub
```

**What each line does**:
- `For Each sec In doc.Sections` — iterate sections; "one letter = one Section" only holds when the merge type was *Letters*.
- `sec.Range.Copy` — copy the entire section (text, paragraphs, merged field results).
- `Documents.Add` + `Range.Paste` — paste to an explicit target, more reliable than `Selection.Paste`.
- `ExportAsFixedFormat(..., wdExportFormatPDF)` — Word's built-in PDF export; the constant equals `17`.
- `Application.ScreenUpdating = False` — skip redraws; even hundreds of letters finish in seconds.
- The leading `Section-count check` prevents silently exporting one giant PDF when the user forgot to pick the *Letters* type.

---

## 5. FAQ / Troubleshooting

1. **Macro errors with "path not found" or "automation error"?**
   `outPath` must point to an **existing parent** directory (e.g. `C:\` always exists). If you use a UNC path like `\\nas\share\…`, confirm write access (try creating a folder in Explorer first). Safest: create an empty folder under your user profile.

2. **Only one PDF is exported?**
   99% you picked *Directory* or *Labels* instead of *Letters*. Return to Step 2 and re-pick *Letters*. (If you truly only have 1 record, 1 PDF is correct.)

3. **MERGEFIELDs show `«Name»` instead of real text?**
   That's the field-code view — totally fine. Click **Mailings → Preview Results** to see the real values; exporting to PDF also renders them as real text. No action needed.

4. **"Macros have been disabled" pops up when opening `.docm`?**
   Change **Trust Center → Macro Settings** to *Enable all macros* (only for files **you wrote**). **Never** enable macros on `.docm` attachments from unknown senders.

5. **CSV is garbled / names get misaligned in the merge?**
   Open the `.csv` in **Notepad** → *Save As* → Encoding **UTF-8**. Or save the data as `.xlsx` from Excel — Word handles `.xlsx` most reliably. Make sure the header row has no merged cells and no blank rows.

---

## 6. Next steps

You've now built a full pipeline: "AI writes the data → Word mail-merges → VBA exports PDFs". Recommended next directions:

- **Level up to L4**: send different letter variants per condition (e.g. First-class scholarship gets a long version, Third-class a short one). See the main tutorial's *Section 6 — Conditional merge*.
- **Clean dirty data sources**: if your list is exported from some system with merged cells and inconsistent formats, jump to **B5 — Power Query**, which cleans messy tables into merge-ready data.
- **Schedule batch jobs**: when you're ready to run this nightly, read **B20 — Automation Pipelines** to wire the macro + data into Task Scheduler.

---

> 📌 **Note**: All VBA macros in this folder have been structure-validated for Word 2016 / 2019 / 365. The CSV opens cleanly under UTF-8 on Windows. If PDF export complains about disk permission, change `outPath` to a sub-folder under your user profile (e.g. `C:\Users\<you>\ExportedPDFs\`).