The Tech Ninja has many different weapons in his arsenal of educational goodness. A tool that the ninja frequently relies on is mail merge. Mail merge is the ability to take data (for example names and addresses) from a spreadsheet or database and insert them into some kind of printed document (a letter, catalog, etc.). This is a quick and easy way to make personalized letters to students or parents. The Tech Ninja has also used mail merge to make cards to remind students of passwords and to quickly send out midterm grades in a newsletter without having to sit there and write in each grade on a form. Yuck.
So for mail merge we will be using two programs: Microsoft Excel and Microsoft Word. We have three major steps to accomplish:
- Create data spreadsheet
- Create document to merge into
- Merge, save, or print.
We will start by starting up Excel.
Create Data Spreadsheet
Excel is a spreadsheet program that can perform all kinds of ninja magic on numbers and other data. A spreadsheet consists of rectangles called cells. Like other coordinate grids, each cell has a name that comes from the letter of the column and the number of the row. The highlighted cell below then would be named B2.
Basically we are going to use the spreadsheet as a data table and we will use row one for our headers. Double click cell A1 and type Fname. Then you can either press the TAB key or double click on cell B1 and type Lname. These headers can be anything you want. Address, City, State, Zip, Parents are some common headers that the Ninja uses quite frequently. A reminder, pressing TAB will take you to the next cell in the row, ENTER or RETURN will take you to the next cell in the column. If you hold down SHIFT with either TAB or ENTER it will take you to the previous cell rather than the next. As you type your text may leave the cell you are typing in. Don’t worry, it will do that.
Once you have the headers in, it is time to fill in the data for your students under the header. Each student gets one row. I have an example below of what your data might look like when you are finished.
You will notice that some of the text is cut off when it runs into another cell. That is fine, your text is still there. The Tech Ninja normally has separate columns for City, State, and Zip but combined them for space. As needs arise, you can always go back and add more rows if you get a new student or new columns to track additional data. The ninja usually makes a spreadsheet with names and addresses before the start of school each year and then just add as I go on through the year.
Now save your spreadsheet. Remember where you saved it!
Create Document to Merge Into
Now we head over to Word.
Type up your document as you normally, however where there is data that will come from your spread sheet, just leave a space there. See the sample below.
Once you have finished writing the document, it is time to start the mail merge process.
Click the Mailings tab and then from the Start Mail Merge drop down, choose “Letters”
Next from the Select Recipients, choose “Use Existing List.” An Open Data Source window will open. Browse to where you saved your spreadsheet file and click open.
A Select Table window will open. Select Sheet1$. Make sure that First row of data contains column headers is checked and then click OK.
Now to double check you will get the names you want, let’s click Edit Recipient List.
Now you should see the data that was in your spreadsheet. If you want to exclude a student from the mail merge you can remove the check from in front of their name. You might want to scroll down through the list to make sure there are no blank rows that have checks in front of them. Once you are satisfied with the list you can click OK.
Now we need to tell Word where want our data to appear in the final letter or document. To do that, place your cursor where you would like the first bit of data to appear. In the example below, I have place the cursor right after “To the parents of:” (1). Click the Write & Insert Field (2). Click the lower part of the Insert Merge Field (3) and then choose Fname (4).
«Fname» will appear where your cursor was. Continue to add fields to your document. You can change the formatting of the fields by applying underline, bold, italics, font, font size, etc. to the fields. You can also copy, paste and move the fields around your document. In the completed example below I have made the grades bold and two font sizes larger. Note: the complete field is the text between the double greater and lesser than signs. If you alter those signs or text in between them, the field will not merge properly.
Next, we need to preview the document to make sure that everything will merge correctly. Click the preview Results drop-down button and select Preview Results. You can use previous and next record buttons to see other data sets.
If you are happy with the result, we can go on to the final step.
Merge, Save, and Print
Now click Finish & Merge. Here we have three options:
1. If you edit individual documents Word will create a new document with each record on a different page. You can edit each individual letter, save the document and then print.
2. You can print the documents. When you merge to printer, a window will come up asking which records you would like to print. If you want all of the records printed, simply click OK and print away.
3. If you included e-mail addresses in your original spreadsheet you could also e-mail this document to parents by selecting Send E-Mail Messages. Word will ask you which column in your spreadsheet has the e-mail address and it will ask if you want to send the e-mail message as HTML so the document will appear in the e-mail message formatted as you have done in Word, Plain Text so the document will appear in the e-mail message but it will not be formatted, or to send the document as an attachment. Each recipient will get only the record that is associated with the e-mail address in the spreadsheet. Cool.
Now that you have created a data source, you can always go back and add info and use it for other mail merge documents, decreasing the amount of time that you are working on personalized letters and documents.
If you are using an older version of Word and Excel, the process is pretty similar however in Word you need to go to Tools and then Mail Merge for a mail merge wizard that will walk you through the steps for merging. One thing to remember, when you choosing your data source in older versions of Word, it doesn’t automatically look for spreadsheets so you will need to change the File Type to either All Files or Excel Spreadsheet.