Word: Mail merge

Updated 2017.11.26

You are to use a Word template, Excel, and Word's mail merge feature to create a series of award certificates for dean's list students. 17 points total. Use the following Excel spreadsheet: StudentsForDeansList.xlsx.

[1] Open the spreadsheet file and save it to your My Data Sources folder (it should be inside of your Documents folder). Do not change its name!

Create a Word mail merge document:

  • [1] Use the online templates to download a template for an award certificate (your choice).
  • Change the text of the document to include the following:
  • [1] The name of the school at the top (make up a name [e.g. Harvard, Briar Cliff] and create a text box if one is not already there).
  • [1] Text explaining that the recipient has been named to the Dean's List for the current semester.
  • [1] Most of the award certificates have room for a signature at the bottom. Type your name below a signature line. If no signature line is there, create one. You may have to add a text box.
  • [1] Save this document. Name it DeansList-Template-FirstName-LastName.docxusing your first and last name.
  • Use Word's mail merge wizard to merge the award certificates with the names and addresses from the StudentsForDeansListExcel spreadsheet.
  • [2] Use the sort feature to sort the names in alphabetical order, using the last name as a sort key. If two students have the same last name, sort them in alphabetical order by first name.
  • [1] Use the filter feature to select only those students with a GPA of 3.00 or higher.
  • [3] Select the First Name field, the Last Name field, and the GPA field for insertion into your document in appropriate locations.
  • [1] Format the First Name, Last Name, and GPA in relatively large, bold letters so they will easily be seen by anybody who is viewing the certificate.
  • [1] Complete the mail merge. NOTE: The GPAs will have way too many digits to the right of the decimal point. Don't worry about this (see extra credit part below).
  • [1] Save the document. It should still have the name DeansList-Template-FirstName-LastName.docx.
  • [1] When you get to the sixth and final step in the mail merge, click on Edit individual letters to create the merged document. It will be 19 pages long. Save the merged document. Name it DeansList-Merged-FirstName-LastName.docx.
  • [1] Put the following files in the drop box at the same time. Do not do two separate submissions. You must put both files in the drop box to receive full credit:
  • DeansList-Template-FirstName-LastName.docx
  • DeansList-Merged-FirstName-LastName.docx

I strongly encourage you to go back into the drop box after submitting your documents and make sure that you have submitted both documents correctly. I can only grade what is in the drop box.

Optional extra credit:

  • If your GPAs end up with way too many digits to the right of the decimal point (which I think they will), figure out how to use Excel to make sure that they have exactly two digits to the right of the decimal point. You must use an Excel formula in your solution (20%).
  • Add your actual signature (not merely a script font) to the bottom of the certificate (20%).

10/26/2018Mail merge to Print Deans List Certificates.docxPage 1 of 1