MO-110: Mail Merge That Has to Run Again in Six Weeks

Mail Merge That Has to Run Again in Six Weeks

Marketing holds a 1,150-row Excel workbook of event registrants (First, Last, Company, City, Session, PaymentMethod, Email). They must send a personalised confirmation letter by post to the 640 who paid by cheque, a confirmation email to everyone with an address, and one directory listing registrants grouped by Session. The letter must carry an extra paragraph of joining instructions only for Session = 'Workshop B'. The registration date in the workbook is stored as text for about 200 rows and merges as 45678 instead of a date. Marketing wants to rerun the whole thing in six weeks from a fresh workbook without editing the main document.

1. Diagnose the 45678 dates and choose between fixing the spreadsheet and fixing the merge field.

Read the worked answer

45678 is the Excel serial number for a date - a clue that Word is receiving the cell's underlying value, not its display. Roughly 200 rows are stored as text, so they behave differently from the other 950, which is why the fault is partial.

A merge-field switch alone will not fix it. { MERGEFIELD RegistrationDate \@ "d MMMM yyyy" } formats a value Word recognises as a date; applied to text it prints the text unchanged. It works for the 950 and fails for the 200, which is the confusing half-fixed result people report.
Fix at the source. In Excel, convert the column to real dates (Power Query, or Data > Text to Columns > Date, or DATEVALUE in a helper column that replaces the original). Then keep a \@ switch in the merge field for presentation.

Judgement: the rerun in six weeks is the deciding factor. A merge-field workaround has to be redone every time; a cleaned column in the workbook template is done once. If marketing cannot guarantee a clean column, connect with the OLE DB/Office Data Connection method rather than DDE, so Word reads typed values rather than formatted display strings, and restate the stored-as-text rule in the handover.

2. How do you produce the conditional 'Workshop B' paragraph without dropping anyone from the merge?

Read the worked answer

Use an If…Then…Else field, not a record filter:

Mailings > Rules > If…Then…Else, Compare: Session, Equal to: Workshop B, insert the joining-instructions text, leave the otherwise box empty. Word produces { IF { MERGEFIELD Session } = "Workshop B" "…text…" "" } wrapped in the right paragraph marks.
Verify the field braces are real field delimiters (Ctrl+F9), not typed characters from the keyboard. Typed braces render as literal text and print in 1,150 letters.

Why not 'Skip Record If': that rule excludes the entire record, so a Workshop B registrant would receive no letter at all - the opposite of the intent. Skip Record If is for data cleaning (test rows, blank emails), not for paragraph-level variation.

Two checks before merging: press Alt+F9 to inspect the resulting field code for stray quotes, and run Edit Recipient List to confirm 1,150 records are active. Then merge to a new document (not straight to the printer) for a 20-record sample, because a conditional field that loses its trailing paragraph mark silently pulls the following paragraph up onto the same line.

3. Marketing wants one source, three outputs: cheque letters, emails, and a grouped directory. Set out the merge strategy and flag the one output Word cannot do natively.

Read the worked answer

One data source, three separate merges - not one merge with complicated rules:

Cheque letters. Same main document, Edit Recipient List > Filter: PaymentMethod = Cheque. Result should be 640 records; if it is not, the filter or the source connection is wrong and you find out before printing 640 letters.
Emails. Filter: Email is not blank. Finish & Merge > Send Email Messages, mapping the Email column as the To field. Warning: merge-to-email flattens the letter into HTML, so headers, footers and text boxes vanish - design the email version as a short body with inline text. Word also cannot attach files through mail merge.
Directory. Directory (catalog) merge produces one continuous document instead of one page per record - the right choice for the listing.

The native limitation: Word cannot detect a change of value and start a group heading. A 'grouped by Session' listing needs the data pre-sorted by Session (sort in the merge or in the workbook) and either the Session printed on every row, or the aggregation done in Excel/Power Query before merging. Saying so up front beats three hours of nested IF fields that still misfire when two sessions interleave.