Skip to content
Tutorials Center
My learning

Topics

Lesson 40 of 42 · Projects and Practice

Project: A Form Letter and Certificate Merge

Practice mail merge from start to finish: clean an Excel list, write a letter template with conditions, merge to new documents, make certificates and print labels.

This project puts mail merge to work on a real job: a swimming club sends every member a renewal letter with a personal fee, a different paragraph for juniors and adults, and a certificate for those who reached a milestone. You will also check the data before merging, because most merge failures are data failures.

Read Mail Merge first if the vocabulary (main document, data source, merge field) is new.

Step 1: The data

Create an Excel workbook, sheet Members, with these columns in row 1:

First_Name, Last_Name, Email, Street, City, Postcode, Birth_Year, Years_Member, Fee, Paid

Enter about 12 members, including some awkward cases: a missing first name, a postcode with a leading zero, a duplicate member, a fee with cents, a member who has already paid. Format Postcode as Text and Fee as Number with 2 decimals. Close the file before merging.

Check the data with a script

Any list used for a merge should be validated first. This stand-in reads the sample rows, then reports empty cells, duplicates, a postcode that lost its leading zero, and a fee that is not a number. Replace the sample with your own data.

Python Validate the merge list (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input · what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Fix each problem in Excel, then continue.

Step 2: Write the letter

In a new Word document, set up a normal business letter: sender block, date, recipient address, subject, body and signature. Use the Letter type from Mailings > Start Mail Merge.

  1. Select Recipients > Use an Existing List... and pick the workbook and Members sheet.

  2. Address Block for the recipient (check Match Fields).

  3. A Greeting Line, with the fallback "Dear member," for records with no name.

  4. Body text, for example:

    Your club membership for the coming year is due on September 1. After «Years_Member» years with us, thank you.

  5. The date: use { DATE \@ "MMMM d, yyyy" } or just a date typed once.

  6. The fee: insert Fee, then press Alt+F9 and add the switch to get { MERGEFIELD Fee \# "$#,##0.00" }.

Add rules

  • Juniors: a rule for members born after the year 2008. In Word, use Rules > If...Then...Else..., with Field name Birth_Year, comparison Greater than, value 2008, and for the text: "Your parent or guardian can renew online." Else: "You can renew at the front desk."
  • Milestone: a rule on Years_Member Greater than or equal to 10: "You have reached 10 years; your certificate is enclosed."
  • Already paid: use Skip Record If Paid Equal to yes.

Press Alt+F9 to see the codes; they look like { IF { MERGEFIELD Birth_Year } > 2008 "text" "other text" }. Nested braces must be made with Ctrl+F9; do not type them.

Step 3: Preview, test and merge

  1. Preview Results: click through every record, especially the awkward ones.
  2. Check for Errors... > Simulate the merge and report errors in a new document.
  3. Finish & Merge > Edit Individual Documents... > All. A new document appears with one letter per page.
  4. Read at least five letters, and look at page breaks. Fix problems in the main document and merge again; never fix them in the merged copy.
  5. Print or save as PDF; or, for email, choose the Email Messages type from the start and send a test to yourself.

Step 4: A certificate from the same data

Start a second main document: Landscape, a decorative page border (Design > Page Borders), centered large text:

Certificate of Membership Awarded to «First_Name» «Last_Name» for «Years_Member» years of membership

Connect it to the same data source and use Edit Recipient List > Filter... to include only members whose Years_Member is Greater than or equal to 10. A filter picks records; a rule only changes text. Merge to a new document and print on heavy paper.

Seeing both in one view helps you check wording and layout. Preview with sample data:

Preview the merged letter and certificate

Step 5: Labels for the envelopes

Start a third main document of type Labels, choose your label product, insert Address Block in the first cell, Update Labels, and merge (Envelopes and Labels). Apply the same filter to skip members who already paid, so you only mail those who need the letter.

Common problems and fixes

Symptom Cause Fix
Dear , Empty first name Greeting Line fallback, or fix the data
$120.5 No numeric picture Add the \# "$#,##0.00" switch
Postcode 2134 Excel dropped the zero Format the column as Text, re-enter
Same letter repeated Missing «Next Record» (labels) Update Labels
Wrong rule result Comparing text as numbers Store numbers as numbers
Merge asks for a data source each time Moved or renamed file Reselect the data source

Check your understanding

Quick check · 4 questions

of 4 answered You scored / 4 · perfect!

  1. 1. Where should you fix a mistake found in a merged letter?

    Fixing the source makes all the letters correct; edits in the merged copy are lost on the next merge.
  2. 2. What is the difference between a rule and a filter?

    Filters choose which recipients are included; rules like If...Then...Else change the content.
  3. 3. How do you make nested field braces in an IF field?

    Field braces are special characters inserted with Ctrl+F9.
  4. 4. Why format the postcode column as Text in Excel?

    Numbers lose leading zeros; text keeps them exactly.

Summary

  • Validate and clean the data source before merging.
  • Use Address Block, Greeting Line, field switches and If rules.
  • Preview, simulate and merge to a new document before printing or sending.
  • Filters choose recipients; one data source can feed letters, certificates and labels.
  • Fix problems at the source, never in the merged copy.

Next: Shortcuts and Productivity Tips