Mail Merge Using a Spreadsheet

To perform a mail merge using a Microsoft Excel spreadsheet, first create the document using a set of values. The document could be a flat database, such as a list of employees, a list of customers, or a record of students. By default, the Mail Merge process will consider a whole spreadsheet and try to use its content. The column headers will be considered as holding categories of data. That's why you should design the worksheet so that the headers on top of each be explicit and organized enough; and these headers should be directly under the already existing column headers that are A, B, C, etc.

If you are not planning to use the whole worksheet or if the top section of your worksheet contains information that is not relevant to the list of items, then you should create a name range and include the cells you need for the mail merge process.

1. Start Microsoft Word

2. On the main menu, click Tools -> Mail Merge...

3. From the Mail Merge Helper click Create -> Form Letters...
   
   

4. From the Microsoft Word dialog, click New Main Document
   
   

5. From the Mail Merge Helper again, click Get Data and click Open Data Source...




6. From the Open Data Source dialog, click the arrow of the Files Of Types combo box and select MS Excel Worksheets. Using the Look In combo box, locate the folder where the exercise files were installed, it should be C:\My Documents\Mail Merge Files

7. Click Red Oak High School

8. And click Open.

9. From the Microsoft Excel dialog, click Stud_Records
   
   

10. And click OK.

11. Now Microsoft Word would like you to create the letter.
    
    
    
    Click Edit Main Document

12. Now we will create the main letter.
    Your cursor should be blinking on top of the letter.
    Make sure you are working in Print Layout View (on the main menu, click View -> Print Layout).
    Make sure the font is set on the Formatting toolbar as Times New Roman, size 10; otherwise, change it to that font and that size.
    Press Enter to move the cursor down.

13. Press the up arrow key to move the cursor back to the 1st line.

14. Move the Left Indent mark to the 4th mark on the horizontal ruler
    
    

15. Make sure the cursor is still on the 1st line. On the Formatting toolbar, make sure the font is Times New Roman. Change the Font Size to 8

16. Type 12044 Arnolds Drive and press Enter

17. Type Wheaton, MD 20904 and press Enter

18. Type Phone: (301) 417-9401 Fax: (301) 294-8811

19. Press the down arrow key to move the cursor down to the empty line.

20. Press Enter to gain some space.

21. Type RED OAK HIGH SCHOOL and press Enter

22. Select RED OAK HIGH SCHOOL

23. On the Formatting toolbar, change the font to Garamond, size 24, Bold, color Gray 50%

24. Click anywhere on RED OAK HIGH SCHOOL to make sure it is not selected but the cursor is anywhere on that line

25. If you don't have the Tables And Borders toolbar on your screen, then on the main menu, click View -> Toolbars -> Tables And Borders.

26. If you don't have the Drawing toolbar on your screen, on the main menu, click View -> Toolbars -> Drawing. Position the Tables and Borders and the Drawing toolbars at the bottom section of your screen, position them both side by side.
    
    

27. On the Tables and Borders toolbar (which is on the left, on my picture), click the arrow of the Fill Color button and click Gray 10%

28. With the cursor still on that line, position the First Line Indent marker at 0.5 and the Hanging Indent marker at -1 (in the gray section) of the horizontal ruler.
    
    

29. Now we will insert a picture on the letter.
    On the Drawing toolbar, click Text Box, then click somewhere on the left side of RED OAK HIGH SCHOOL

30. With the cursor inside of the text box, on the main menu, click Insert -> Picture -> ClipArt...

31. In the picture categories, click Academic

32. If you are using Microsoft Word 2000, from the list of academic pictures, click the first picture and click Insert

33. Close the Insert ClipArt window. (I think that if you didn't touch anything else on the image, it should have an acceptable size already).

34. Move the text box (with the image in it) to the left of RED. On the Drawing toolbar, click the arrow of the Fill Color button and click No Fill.

35. On the Drawing toolbar, click the arrow of the Line Color and click No Line.
    
    

36. Press Ctrl + End to get to the last empty line of the letter.

37. Position both the First Line Indent and the Hanging Indent marks at 0.5 on the horizontal ruler.
    
    

38. With the cursor under the big title, press Enter 3 times.

39. Press the up arrow key once to move the cursor up one line.

40. On the Standard toolbar, click the Insert Table button and elect to create a table with 4 columns and 1 row (1 X 4 Table)

41. With the cursor in the first cell, type Student:

42. Click inside the 3rd cell and type Mail-To:

43. Change Student: and Mail-To: fonts to Tahoma, size 10.

44. Click inside the second cell.

45. On the Mail Merge toolbar, click Insert Merge Field and click FirstName

46. Press Space

47. On the Mail Merge toolbar, click Insert Merge Field and click LastName

48. Press Enter to move the cursor to the second line inside the cell.

49. On the Mail Merge toolbar, click Insert Merge Field -> MajorCode

50. Press Space and click Insert Merge Field -> MajorName

51. Press Enter and click Insert Merge Field -> Grade

52. Click inside the last cell

53. On the Mail Merge toolbar, click Insert Merge Field -> ParentsNames

54. Press Enter and click Insert Merge Field -> Address

55. Press Enter and click Insert Merge Field -> City

56. Press Space and click Insert Merge Field -> State

57. Press Space and click Insert Merge Field -> ZIPCode

58. With the cursor still inside the last cell, on the Tables and Borders toolbar, click the arrow of the Outside Border button and click No Border

59. Click inside of one of the other cells and press F4 (to repeat the last action).

60. Remove the borders on the other two cells.

61. Click on the empty line that is just under RED OAK.

62. Press Ctrl + R to align the cursor to the right.

63. On the main menu, click Insert -> Insert Date And Time...

64. From the Date And Time dialog, click the 3rd date
    
    

65. And click OK

66. Press Ctrl + End to get to the last line of the letter.

67. Type Dear and click Insert Merge Field -> ParentsNames

68. Press Enter and type
    
    The Red Oak High School, in conjunction with the Montgomery County School System, would like you to honor us with your presence at this year-end's student award ceremony this Saturday October 24 at 7:30 PM.
    
    The students with good and higher grades will receive the evening's special mentions. At the event, the students with the three highest grades in each matter will receive an honor, silver, or a copper awards. This year's ceremony places a strong emphasis on discipline. We certainly hope that this is a good start and that you are raising a valuable young kid.
    
    The ceremony will start at 7:30 PM with a live session of international and ethnic dances. Although the awards ceremony will start at 8:00 PM, the high level of attractions prepared for the opening door is an entertainment session we encourage you to attend.
    
    We thank you in advance for your participation and attention.
    
    Bernard Glott
    ROSH Principal

69. Press Enter twice and create a table that illustrates the evening program. Here is an example:
    
    
    

70. All you have to do now is to read the letter