Performing a Mail Merge with Microsoft Word

The Mail Merge feature in Microsoft Word enables you to create parental feedback letters in a group by combining a standard template letter with the exported child data file. This means that each parent receives the same basic letter but each letter is personalised with their child’s measurement results. 

The process of creating feedback letters to parents requires a mail merge between the downloaded NCMP child data file and a letter template. 

1. To generate and export a single CSV file from the NCMP IT system, containing data of all pupils in all BMI categories in the selected school(s). 

You can then perform a single mail merge with a Microsoft Word template we have provided. This mail merge template automatically adapts the text in the body of the feedback letter according to each child’s BMI Category. 

The four different letter templates are

• underweight
• healthy weight
• overweight
• and very overweight)

2. Alternatively your Local Authority (LA) may have developed its own feedback templates. Or you may choose to amend the NHS England template.  

The NHS England recommended feedback letter template is available on the main website (its called NEW NCMP results letter template.doc) 

A test pupil data CSV file (example pupil feedback CSV file) is also available to download. This file should be used to set up the feedback letter templates to reflect your local settings before performing a mail merge. 

For details of the individual BMI category templates, please see Public Health England’s Operational Guidance homepage. The individual templates can be opened directly in Word.

Before the template can be used in a live mail merge it must be set up for use within your LA.

1. Open the feedback template (for example NCMP results letter template.docx) in Microsoft Word. 

The letter consists of:  

• Static text This text does not change
• Mail merge fields  Microsoft Word replaces these merge fields with the data from the child data CSV file. These fields (First Name, Last Name etc.) are shown encapsulated within chevrons (<< >>) usually with a grey highlight
• Conditional text Specific paragraphs are designed to be included in the feedback letter depending on the child’s BMI category. For example, the text included in a letter for a child in the Very overweight category to provide sources of helpful information, is not included in a feedback letter for a child in the Healthy weight category. Also, usually shown with a grey highlight
• LA specific text  this text has been formatted with a yellow highlight and is specific to each LA so requires modifying. It includes your LA’s contact details (address, telephone No.,) and other LA specific information

The test pupil data CSV file (PupilFeedback.csv) contains 4 records, one for each BMI category.  You will need to temporarily link this file to the template in order to complete the template’s setup for your LA. This is a one-off process and does not require repeating for each mail merge

To link the data file to the Microsoft Word template:

1. Open the letter template.

2. In the letter template, click the Mailings tab on the Microsoft Word ribbon.

3. Most of  the commands on the Mailings tab will appeared greyed-out (unavailable) until a data file has been linked to the template.  Click the Select Recipients command on the Mailings ribbon then select Use Existing List… from the dropdown menu.

4. In the Select Data Source dialog box. Navigate to the downloaded test pupil data file. (PupilFeedback.csv). and click the file to select it. Now click open.

When the data file is attached, the previously greyed-out commands on the Mailings tab are now available.

5. To View the data in the letter template, on the Mailings tab

6. Click the Preview Results command. This will substitute the field names in the template, with the actual data.

7. Use the navigation buttons on the Ribbon to scroll through the records. When scrolling, notice the conditional text changing for each BMI category. Example of each letters are available from the gov.uk website.

8. To show merge fields and conditional text with or without the grey background, click highlight merge fields. 

9. This link is complete.

You will need to customise 3 sections of static text in the template for your local authority. They are

1. Sender name, organisation address, postcode, sender phone number and email address. Modify these items, highlighted in yellow, so that they provide the relevant information for your LA. You will need to remove the yellow highlight text by 

2. Selecting the text to remove the highlight from

3. Select the home tab on the ribbon, and click text highlight colour.

4. Click no colour. 

You should take care not to change the layout or the position of the parent/ carer address on the left.

5. In the paragraph beginning ‘This information has not been shared with’, modify the text to reflect which organisation holds the measurement information (local NHS or local authority public health team), removing the yellow highlight.

6. Modify the sender name. 

To add your local authority's emblem or logo in the header area of the feedback letter template.

1. Open the template's header. From the insert tab on the ribbon click the header. 

2. From the bottom of the drop down menu, select edit header. The header opens and the rest of the template will be temporarily greyed out.

3. From the design tab (header and footer tools) on the ribbon, click the picture command. 

4. In the Insert Picture dialog box, navigate to the emblem or logo image to be inserted. Select the required image and click insert. 

5. To make the image more movable, click the image to select it. 

6. From the picture tools, format tab, click wrap text and select square.

7. Drag the image to its required position ensuring that the layout or the position of the Parent/ Carer address information is not affected. The image can overlap the Header or Document body border.

8. Click the Close Header and Footer command.

1. Link your downloaded child data CSV file to the template.  To link your child data CSV data file (generated by the NCMP IT system) to the set up letter template instead of the test child data file

2. In the letter template, click the Mailings tab on the Microsoft Word ribbon.

3. Click the Select Recipients command on the Mailings ribbon then select Use Existing List… from the dropdown menu.

4. In the select data source dialog box

5. Navigate to the download pupil data

6. Click the file to select it.

7. Click open. 

8. To View the data in the template, on the Mailings tab

9. Click the Preview Results command. This will substitute the field names in the template, with the actual data.

10. Use the navigation buttons on the ribbon to scroll through the records.  

Chose from one of the following options to print a mail merged document 

1. Send the mail merge output directly to the printer with no further editing or

2. Send the mail merge output to a new Word document, each letter is on a separate page. This approach will offer you the flexibility to edit feedback letters for specific parents, for example, adding a note.

From the Mailings tab of the Ribbon, click the Finish and Merge command.

Select Print Documents from the drop down menu.

3. Select which letters you want to print choosing from 

• all feedback letters
• just the current record displayed on the screen
• a range of records by entering start and end record ID

4. Click ok.

5. Select the printer you want to use to print the letters. 

6. Click ok.

7. The letters will begin printing. 

1. From the Mailings tab of the Ribbon, click the Finish and Merge command.

2. Select Edit Individual documents from the drop down menu.

3. Select to print

• all feedback letters
• just the current record displayed
• a range of records by entering start and end record ID

4. Click ok 

5. Select the printer you want to use to print the letters. 

6. Click ok.

7. A new multi-page document opens containing all the parent feedback letters.

8. If you need to add in any specific comments to any of the letters. You can locate the letter and add in the extra wording.