Excel to PDF mail merge

Letters from a spreadsheet, without Word's mail merge

The list already exists. It is a sheet with a row per person and columns for the things that change — the name, the address, the amount, the date, the reference. What you need is that letter, ninety times, with the right words in the right holes, and as PDFs rather than as something only Word can open.

Word's own mail merge will do the merging. Where it gets awkward is the last step: it wants to print, or to email, or to make one enormous document. Getting one PDF per person, named after that person out of it means a macro, or an add-in, or an afternoon of Save As. That last step is what this tool is.

Two shapes of the same merge

• One PDF with every letter in it. Each letter starts on a fresh page. This is the one to print, or to keep as the record of what went out.
• A separate PDF per letter, in a zip. This is the one for sending: each file is one person's letter, ready to attach.

They are the same letters laid out by the same code — the only difference is whether the pages go into one document or ninety.

Naming the files from the data

A zip of letter-1.pdf to letter-90.pdf is a zip you then have to open ninety times to find out whose is whose. So the file name is a template like any other part of the letter: {{Reference}} - {{Name}} gives you INV-2214 - A Nkemelu.pdf, and a folder you can actually search.

Anything a file system or a zip reader would object to — a slash, a colon, a newline that arrived from a spreadsheet cell — is replaced with a dash, and two people with the same name get (2) rather than one letter quietly overwriting the other. A format that comes back empty for a row falls back to letter-4.pdf instead of failing the merge.

The empty-line rule

Half your list has a company name and half doesn't. The naive substitution leaves a blank line in the middle of every individual's address, which is exactly the tell that a letter came out of a machine.

So a line whose fields all come back empty is dropped, and the address closes up. The rule is deliberately narrow: the line has to contain merge fields, every one of them has to be empty, and what's left over has to be punctuation and spaces rather than words. A line reading Account: {{Account}} with no account keeps its label and its gap, because that is a hole somebody should see rather than one that should quietly heal.

The preview is the letter

The page on the right is drawn by the browser while you work, so moving a block is instant. Check the real PDF asks the server for the actual letter for the row you're on — the same code path, the same layout, the same fonts as all ninety — and shows you that instead. It costs nothing: looking is free, and only generating the letters spends one of your files.

Flip through the rows before you generate. The one worth looking at is never the first one: it's the customer with no second address line, the name that's forty characters long, the note field somebody pasted three paragraphs into.

Logos and signatures — and what "digital signature" means here

A logo and a signature are pictures, and they go on the letter as pictures: upload a PNG or a JPEG, or sign in the box with a mouse or a finger and that drawing becomes the image. A PNG with a transparent background stays transparent, which is what you want for a signature over a line.

This is not a cryptographic signature. When a PDF is described as "digitally signed" in the strict sense, it carries a certificate that ties the document to an identity and breaks visibly if a single byte is altered afterwards. That needs a certificate authority, a private key you keep, and a signing step this tool does not do. What you get here is the picture of a signature — the same standing as a signature on a scanned letter, which for a covering letter, an invoice or a statement is exactly what is being asked for, and for a contract that has to hold up may not be. It is worth knowing which one you need.

What the letters can and can't say

The text is set in the standard PDF typefaces — Helvetica or Times, in the weights you'd expect. Nothing is embedded, so a letter is a few kilobytes and opens identically everywhere, and in exchange the alphabet is the Latin one: the accented letters of Western Europe are fine, and Greek, Cyrillic, Arabic, Hebrew and CJK are not. Characters outside it are swapped for a plain twin where there is an obvious one and marked with a ? where there isn't, and this page tells you the count after a merge rather than letting you find it in letter 60.

Values come out of the workbook as values, not as they were formatted in Excel. A cell showing £1,250.00 holds the number 1250, and the pound sign and the commas are the sheet's formatting rather than the cell's contents — so they don't travel. Dates are the exception, because a date has no natural spelling: pick the one you want at the top of step 02 and every date in the merge follows it. If you need currency symbols in the letters, put them in the column — or add a column that's already the string you want.

Where your files go

The workbook, the logo and the signature are uploaded so the letters can be laid out on the server, and every one of them is deleted within the hour. The letters themselves are built, handed to your browser and deleted on the same sweep. Nothing is kept, nothing is indexed, and no row of your spreadsheet is stored anywhere after the download — the same as every other tool here.

Related tools

• Excel to Word — the same rows as one Word report, when what you want is a document rather than a letter each.
• Auto-Clean — worth running over the list first if the names have stray spaces or the dates are text.
• PDF Page Editor — for putting a covering page in front of the merged PDF, or pulling one letter out of it.