Understand mail merge data sources and master documents

19 Mail Merge

Learning Objectives

• Explain the purpose of a mail merge and list typical real‑world applications.
• Identify all data‑source requirements, including field naming, data types, primary‑key concepts and size limits.
• Link mail‑merge data sources to relational‑database principles (relationships, referential integrity, queries).
• Prepare a data source correctly for merging (Excel, CSV, Access, Outlook, Google Sheets).
• Design a master document, insert and format merge fields, and apply field switches.
• Run a merge, handle large data sets for Paper 4, and troubleshoot common problems.

Objectives‑to‑Content Mapping

What Is Mail Merge?

Mail merge is a feature of word‑processing software that combines a master document (the template) with a data source (a table of records) to automatically produce a set of personalised output documents – letters, labels, emails, certificates, invoices, etc. It removes the need to type repetitive information for each recipient.

Real‑World Applications

Key Terminology

• Master document – Template containing fixed text and placeholders (merge fields).
• Data source – Table‑like collection of records (rows) and fields (columns) that supplies variable data.
• Record – One row of data, representing a single recipient or item.
• Field – One column of data, e.g. First_Name, Postcode.
• Primary key / unique identifier – Field (or combination) that uniquely distinguishes each record; essential for avoiding duplicates and for relational‑database links.
• Merge field – Placeholder in the master document that is replaced by the corresponding field value during the merge.
• Field switch – Formatting instruction added to a merge field (date, numeric, case conversion, etc.).

Data‑Source Requirements

1. Header row: First row must contain unique field names. Use underscores instead of spaces (e.g. First_Name).
2. Consistent data types: All entries in a column should be of the same type (text, number, date). Store postcodes, phone numbers and IDs as **text** to preserve leading zeros.
3. Primary key: Include a field that uniquely identifies each record (e.g. Student_ID). This supports referential integrity when the source is a relational database.
4. No blank rows or columns: Delete any completely empty rows/columns; they are interpreted as additional (empty) records.
5. Size limits:
   • Excel (.xlsx): 1 048 576 rows × 16 384 columns.
   • CSV (plain text): limited by file‑size (≈2 GB on Windows); practical exam limit ≈ 10 000 records.
   • Access (.accdb): 2 GB per database; up to 2 147 483 647 records per table.
6. Encoding: Save as UTF‑8 (or UTF‑16) to ensure special characters (é, ñ, £) display correctly. Avoid ANSI when non‑ASCII symbols are present.
7. Dynamic range (Excel): Convert the range to a **Table** (Insert → Table) and give it a meaningful name (e.g. GuestList) so that added rows are automatically included in the merge.

Supported File Formats & Limitations

Link to Relational‑Database Concepts (Topic 10)

• Primary key & foreign key: In an Access data source, the Customer_ID field in the Orders table is a foreign key that references the Customer_ID primary key in the Customers table. This enforces referential integrity – every order must belong to an existing customer.
• Query as a merge source: Instead of linking directly to a table, you can create a query that joins two tables and filters the records you need. Example:

  SELECT Orders.Order_ID,
         Orders.Order_Date,
         Customers.First_Name,
         Customers.Last_Name,
         Customers.Email
  FROM   Orders
  INNER JOIN Customers
          ON Orders.Customer_ID = Customers.Customer_ID
  WHERE  Orders.Order_Date >= #2025-01-01#;
          

  The result of this query can be saved as RecentOrders and used as the mail‑merge data source. This demonstrates the practical use of relationships and queries covered in Topic 10.
• Why it matters for the exam: Paper 4 often asks candidates to design a data source that respects primary‑key uniqueness and to explain how a query could be used to limit the merge to a specific subset of records.

Preparing the Data Source

1. Open the source file (Excel, Access, CSV, etc.).
2. Enter a clear header row with unique names (no spaces; use underscores).
3. Format each column:
   • Text: set column format to Text (e.g., postcodes, IDs).
   • Date: use a consistent ISO format yyyy‑mm‑dd or a format recognised by the chosen word processor.
   • Numeric: remove commas or currency symbols unless you intend to apply a numeric switch.
4. Delete any completely blank rows or columns.
5. If using Excel, select the data range and choose Insert → Table. Give the table a meaningful name (e.g., GuestList).
6. Save the file in the appropriate format:
   • Excel – .xlsx
   • CSV – .csv (ensure UTF‑8 via “Save As → Tools → Web Options → Encoding”).
   • Access – .accdb
7. For large data sets (>10 000 records) intended for Paper 4, split the file into separate tables or create a query that returns only the required subset.

Designing the Master Document

1. Open a new document in your chosen word processor (Word, LibreOffice Writer, or Google Docs).
2. Compose the fixed text (greeting, body, closing, footer).
3. Insert placeholders where variable data should appear:
   • Microsoft Word: Mailings → Insert Merge Field
   • LibreOffice Writer: Insert → Fields → Other → Database
   • Google Docs: Use an add‑on such as “Autocrat” – the merge field syntax is the same {{Field_Name}}.
4. Typical merge‑field syntax (Word & LibreOffice):

   <<First_Name>> <<Last_Name>>
   <<Address_Line1>>
   <<Postcode>>

5. Apply any required field switches (see next section).
6. Save the master document in its native format (.docx, .odt, or .gdoc) before linking the data source.

Other Platforms (Exam‑Board Note)

The Cambridge AS & A Level IT syllabus recognises that schools may use LibreOffice Writer or Google Docs. All the concepts – merge fields, switches, previewing – are identical; only the menu paths differ. Candidates should be prepared to describe the process for at least one alternative platform.

Field Switches – Controlling Output Format

Running the Mail Merge

1. Link the data source: Mailings → Select Recipients → Use an Existing List. Browse to the prepared file and confirm the table/query name.
2. Preview results: Click Preview Results and scroll through several records to verify correct substitution and formatting.
3. Filter or sort (optional): Edit Recipient List lets you apply filters, change the sort order, or select a subset of records – essential when only part of a large data set is required for the exam.
4. Choose output type:
   • Print directly.
   • Generate a new merged document (Finish & Merge → Edit Individual Documents) for further editing or PDF export.
   • Send personalised emails (Finish & Merge → Send Email Messages).
5. Complete the merge and save the merged output in the required format (e.g., .pdf for submission).

Handling Large Data Sets (Paper 4)

• When the source exceeds the exam‑board limit (usually >10 000 rows), split the file into separate tables or create a query that returns only the records needed for the task.
• Document the method you used (e.g., “A query with WHERE Year = 2025 was created in Access to limit the merge to 8 732 records”). Examiners look for a clear justification.
• If using Excel, keep the table size below the limit or use a filtered view before linking.

Common Issues & Troubleshooting

• Blank fields: Ensure the merge‑field name matches the header exactly (including case and underscores).
• Incorrect date format: Apply a date switch (\@ "dd MMMM yyyy") or re‑format the source column to a recognised date style.
• Extra spaces or line breaks: Use the \* Trim switch or clean the source data (remove leading/trailing spaces).
• Special characters appear as � or ?: Verify the data source is saved in UTF‑8 (or UTF‑16) and that the master document’s encoding matches.
• Too many records for the exam: Use Edit Recipient List to filter, or create a query/table that contains only the required subset.
• Merge stops with an error: Check for hidden columns, merged cells in Excel, or stray commas in CSV files.

Summary Checklist

1. Header row with unique, underscore‑separated field names (including a primary key).
2. Consistent data types; text fields stored as text.
3. File saved in a supported format with UTF‑8 encoding (if special characters are used).
4. Dynamic range (Excel Table) or query defined for large data sets.
5. Master document contains correctly inserted merge fields with any required switches.
6. Preview shows accurate substitution for several sample records.
7. Output type (print, merged document, email) selected before finishing.
8. For Paper 4, data‑source size complies with the specified limit and the method of compliance is documented.

Suggested Classroom Activity

Task: Create a personalised invitation letter for a school gala.

1. Data source: Build a CSV file called Gala_Guests.csv with 20 records and the following fields:
   • Guest_ID (primary key)
   • First_Name
   • Last_Name
   • Event_Date (YYYY‑MM‑DD)
   • Venue
2. Master document: Draft a letter that includes a greeting, a formatted address block, and the event details. Insert merge fields for all columns.
3. Formatting: Apply the date switch \@ "dddd, d MMMM yyyy" to Event_Date and the \* Upper switch to the venue name.
4. Run the merge: Produce a merged PDF for each guest and check that names, dates and venues appear correctly.
5. Extension (relational‑database link): In Access, create a second table Donations linked to Gala_Guests via Guest_ID. Build a query that adds a “Donor_Level” field and use this query as the merge source for a second set of thank‑you letters.

Further Resources

• Cambridge International AS & A Level IT – Paper 4 (Practical) specification (Section 19: Mail Merge).
• Microsoft Office Support – “Mail merge tutorial” (step‑by‑step videos).
• LibreOffice Documentation – “Mail merge guide”.
• Autocrat add‑on for Google Docs – official user guide.