How does mail merge work? Rebuilt in about 30 lines of Python

Mail merge sounds like a Word feature, but it is a small data transformation: read rows, drop the ones a filter rejects, stamp the rest into a template, and put a page break between the copies. The Word editor in the osFoundry desktop app does it from the Mailings tab. This post rebuilds the same steps with python-docx and openpyxl, runs them on the same 40-row member list and one-page letter that were used to try the app, and times them up to 40,000 letters. The names, plans and dates are generated test data.

The inputs

The list is an .xlsx with a header row (First_Name, Last_Name, Plan, Renews, Paid) and 40 members, 12 of them with Paid = No. The letter is a .docx with six paragraphs; three of them contain the placeholder words NAME, PLAN and DATE.

import openpyxl ws = openpyxl.load_workbook("members.xlsx", read_only=True).active rows = [[str(c) for c in r] for r in ws.iter_rows(values_only=True)] records = [dict(zip(rows[0], r)) for r in rows[1:]] 

Each record is a dict keyed by the header. That is all a "field" is: a name that is looked up in the current record. In the app, the headings in row 1 are the list you pick from in Insert Merge Field.

Filter, then stamp

Word's own files store a field as an instruction such as MERGEFIELD First_Name inside the document XML. The script below skips that layer and uses placeholder words, as the recording of the app did, so it shows the mechanism and does not read documents made in Word. It copies the body of the template once per selected record, replaces the placeholders in the text, and inserts a page-break paragraph before every copy but the first.

import copy, docx W = "{http://schemas.openxmlformats.org/wordprocessingml/2006/main}" FIELDS = {"NAME": "First_Name", "PLAN": "Plan", "DATE": "Renews"} def merge(template, records, keep): out = docx.Document(template) # keeps styles and page setup  body = out.element.body sect = body[-1] # sectPr: must stay last  tmpl = [copy.deepcopy(e) for e in body if e is not sect] for e in [e for e in body if e is not sect]: body.remove(e) first = True for rec in filter(keep, records): if not first: sect.addprevious(docx.oxml.parser.parse_xml( '<w:p xmlns:w="http://schemas.openxmlformats.org/' 'wordprocessingml/2006/main"><w:r><w:br w:type="page"/></w:r></w:p>')) for el in tmpl: el = copy.deepcopy(el) for t in el.iter(W + "t"): for ph, col in FIELDS.items(): t.text = t.text.replace(ph, rec[col]) sect.addprevious(el) first = False return out merge("letter.docx", records, lambda r: r["Paid"] == "No").save("out.docx") 

Reading out.docx back gives the check that matters: 12 letters, 12 distinct greetings, 11 page breaks (so 12 pages), no placeholder word left in any paragraph. The second letter starts "Dear Liam," and says "Your Annual membership renews on December 21", the same text the app showed in Preview Results for row 2, and the merged document in the app said Page 1 of 12.

Two choices matter. The sectPr element holds the page size and margins, and the file format requires it to be the last child of the body, which is why every insertion goes addprevious. And the page break is its own paragraph, not a property of the first paragraph of each letter, so letters with a different first paragraph (a logo, an address block) still start on a new page.

The pitfall: a placeholder split across runs

A paragraph in a .docx is a list of runs, and Word starts a new run whenever the formatting, the spell-check state or the editing history changes. A word you typed as one piece can end up stored as three. The test letter has one run per paragraph, so it works, but this is what the same replace does when NAME is split as NA and ME:

naive per-run replace -> Dear NAME, joined-text replace -> Dear Maya, 

The fix is to replace on the joined text of the paragraph and write the result into the first run, at the price of the formatting inside that paragraph collapsing to the first run's. Real field codes avoid the problem, because the field is its own element, and that is the reason to use fields, not placeholder words, in a letter that other people will edit.

How long it takes

Time to read the template, build the document and save it, median of three runs, on an Apple-silicon Mac with Python 3.12 and python-docx. The 40 records are the real list; the larger runs repeat those rows with an index added to the first name so every letter is different, and keep all of them.

The cost is linear, about 46 microseconds per letter, and the file stays small because the XML of 40,000 near-identical paragraphs compresses well. Nothing here needs a server or a service, and even 40,000 letters take under two seconds to build. Opening or printing a document that long is a separate cost, and was not measured here.

Where the app fits

The Mailings tab does the same job with a screen over it: Select Recipients reads the .xlsx, Edit Recipient List shows the rows and counts what a filter keeps ("12 of 40 recipients will be merged."), and Finish & Merge writes the result as a new document. The editor ships in the free osFoundry desktop app, and the files stay on the computer (local-first).