Mail Merge skill for Codex

A merge is done when every record produced exactly one correct output, no record was silently skipped, no placeholder survived, and a human has proofed a sample before anything leaves the building. Sending is a separate, explicitly confirmed step.

Hard rules

• Never send email, upload to a mail service, or print-to-queue without an explicit yes from the user for that batch. Produce drafts (.eml, PDFs) and a manifest first.
• Validate the data before rendering. Most merge defects are data defects.
• Every output file name must be deterministic and unique (record ID, not row number alone), so reruns overwrite instead of duplicating.
• Personal data stays out of logs and chat; report counts and IDs.

Workflow

- [ ] 1. Read the template and the data; list fields on both sides
- [ ] 2. Validate and normalize the data
- [ ] 3. Choose the output shape
- [ ] 4. Render one record, proof it, then render all
- [ ] 5. Check outputs against the manifest
- [ ] 6. Hand over (and only on confirmation, send)

1. Inventory fields on both sides

Template fields must equal data columns, or the gap must be explained.

# docxtpl / Jinja-style template ({{ first_name }})
from docxtpl import DocxTemplate
tpl = DocxTemplate("letter.docx")
template_fields = tpl.get_undeclared_template_variables()

# Word-native MERGEFIELD template (built with Word's Mailings tab)
from mailmerge import MailMerge      # pip install docx-mailmerge2
template_fields = MailMerge("letter.docx").get_merge_fields()

import csv
with open("recipients.csv", newline="", encoding="utf-8-sig") as f:  # -sig strips Excel BOM
    rows = list(csv.DictReader(f))
data_fields = set(rows[0].keys())
print("missing in data:", set(template_fields) - data_fields)
print("unused columns:", data_fields - set(template_fields))

For .xlsx sources read with openpyxl.load_workbook(path, data_only=True) so formula cells give values, or pandas.read_excel(..., dtype=str) so ZIP codes and IDs keep leading zeros.

2. Validate and normalize

Check every record and write rejects to rejects.csv with a reason, never drop silently:

Check	Rule
Required fields	Non-empty after .strip() for every field the template uses unconditionally
Duplicates	Same email or same ID twice: stop and ask
Encoding	No é-style mojibake (re-read as UTF-8 or cp1252); no stray \xa0
Names	Keep the source casing; do not title-case (McDonald, van der Berg, O'Neil)
Dates and money	Parse, then format per locale in code, not in the sheet
IDs and postcodes	Read as strings; 02134 must stay 02134
Salutation	Derive a fallback (Dear customer) when the name is missing, instead of Dear ,

Format values in Python, not in template logic, so the template stays simple:

from datetime import date
from decimal import Decimal

def prepare(row: dict) -> dict:
    ctx = {k: (v or "").strip() for k, v in row.items()}
    ctx["salutation"] = f"Dear {ctx['first_name']}" if ctx.get("first_name") else "Dear customer"
    ctx["due_date"] = date.fromisoformat(ctx["due_date"]).strftime("%-d %B %Y")  # POSIX only
    ctx["amount"] = f"{Decimal(ctx['amount']):,.2f}"
    return ctx

3. Choose the output shape

Need	Output	Tool
Letters to print and post	One combined PDF, page break per record, sorted by postcode	docxtpl per record, then convert and concatenate
Letters to email or archive individually	One PDF per record named <id>-<slug>.pdf	docxtpl + LibreOffice headless
Template already uses Word MERGEFIELDs	Keep it; do not rebuild	docx-mailmerge2 merge_templates
Address labels	Label grid document (Avery layout)	docxtpl with a table loop
Personalized email	.eml drafts plus manifest, reviewed before send	Python email.message

Combined Word file from a MERGEFIELD template:

from mailmerge import MailMerge
with MailMerge("letter.docx") as doc:
    doc.merge_templates([prepare(r) for r in rows], separator="page_break")
    doc.write("letters-combined.docx")

Per-record files with docxtpl (loading the template per record costs little and keeps images, subdocs and rich text from one record out of the next):

from pathlib import Path
from docxtpl import DocxTemplate

out = Path("out/docx"); out.mkdir(parents=True, exist_ok=True)
for row in rows:
    ctx = prepare(row)
    tpl = DocxTemplate("letter.docx")
    tpl.render(ctx, autoescape=True)  # escapes & < > in names like "Smith & Sons"
    tpl.save(out / f"{ctx['customer_id']}.docx")

Convert to PDF in one LibreOffice call (much faster than one call per file):

soffice --headless --convert-to pdf --outdir out/pdf out/docx/*.docx
qpdf --empty --pages out/pdf/*.pdf -- letters-combined.pdf   # optional single print file

LibreOffice must be installed; if soffice is missing, ask before installing it. Run only one soffice instance at a time or pass a separate profile with -env:UserInstallation=file:///tmp/lo-profile-1.

4. Render one, proof, then render all

Render the first record plus the edge cases (longest name, missing optional field, non-ASCII characters, largest amount). Convert to PDF and inspect the pages. Only after that looks right, render the whole set.

5. Check outputs against the manifest

Write manifest.csv with id, recipient, output_path, status. Then:

ls out/pdf/*.pdf | wc -l                 # equals accepted records
unzip -p out/docx/A1001.docx word/document.xml | grep -c "{{\|MERGEFIELD\|«"   # expect 0

In Python, scan every rendered .docx for leftover {{, {%, « or MERGEFIELD and for the literal strings None and nan, which mean a missing value slipped through.

6. Email drafts, and sending only on confirmation

Build drafts first; they can be opened in any mail client for review:

from email.message import EmailMessage
from pathlib import Path

def draft(ctx, pdf_path: Path, sender: str) -> EmailMessage:
    msg = EmailMessage()
    msg["From"] = sender
    msg["To"] = ctx["email"]
    msg["Subject"] = f"Your statement for {ctx['period']}"
    msg.set_content(f"{ctx['salutation']},\n\nPlease find your statement attached.\n")
    msg.add_attachment(pdf_path.read_bytes(), maintype="application",
                       subtype="pdf", filename=pdf_path.name)
    return msg

Path("out/eml").mkdir(parents=True, exist_ok=True)
for ctx in contexts:
    m = draft(ctx, Path(f"out/pdf/{ctx['customer_id']}.pdf"), "[email protected]")
    Path(f"out/eml/{ctx['customer_id']}.eml").write_bytes(bytes(m))

Before any send, show the user: count of messages, sender address, subject line, one rendered sample, attachments per message, and the rejects list. Send only after an explicit yes, throttle to the provider’s rate limit, record each result in the manifest, and make the sender idempotent (skip IDs already marked sent). Sending needs network access and SMTP or API credentials from the environment, never hard-coded.

Gotchas

• Word splits {{ first_name }} across runs when it was typed with autocorrect or edited later; retype the whole tag in one go (see docx-templates for details).
• Excel exports dates as serial numbers and drops leading zeros; read with dtype=str.
• CSV from Excel on Windows is often cp1252, not UTF-8. Detect mojibake before merging.
• strftime("%-d") fails on Windows; use f"{d.day} {d:%B %Y}" for portable code.
• Duplicate recipients produce duplicate letters; dedupe on a stable key before rendering.
• Mixed-language batches need per-record locale for dates, decimals and salutation.

Output: merge report

## Merge report: renewal-letters-2026-11
- Source: recipients.xlsx (sheet "Active"), 1,204 rows
- Accepted: 1,198  Rejected: 6 (rejects.csv: 4 missing address, 2 duplicate IDs)
- Outputs: out/pdf/<customer_id>.pdf (1,198), letters-combined.pdf (2,396 pages)
- Proofed: A1001 (baseline), A1044 (longest name), A1102 (no first name), A1180 (accents)
- Leftover placeholder scan: 0 hits
- Emails: 1,198 .eml drafts in out/eml, NOT sent (awaiting confirmation)