Addressing Holiday Cards with Excel

November 17, 2004

It is that time of year to start planning your holiday card mailing. Store your addresses in Excel and you have two methods for producing labels; either using the Mail Merge feature in Word, or a custom macro in Excel.

Using either method, you will want to start with a list in Excel. You should have one address per line, arranged in columns as shown here.

Method 1:

Using Mail Merge from Excel

1. Save & close the Excel file
2. Open Microsoft Word
3. In Word, select Tools - Letters & Mailings - Mail Merge Wizard

   

4. Choose Labels & choose Next

   

5. Change Document Layout should be selected.

   ---

   ---

   

6. While in step 2, select Label Options.... For standard labels with 3 columns of 10 labels, choose Avery 5160.

   

7. Choose OK to select the label type and you will see a drawing of the labels on your screen.

   

8. Choose Next: Select Recipients

   

9. Choose Use an Existing List. Click the link to Browse...

   

10. Change Files of Type to "Excel Files".

    

11. Navigate to, and select the Excel file that you save in step 1.
12. Excel should identify that your table is on Sheet 1. Be sure to check the box in the lower left for column headers.

    

13. By default, Excel selects all of the names. You could turn off some names here by unchecking those rows. When you are finished reviewing the list, choose OK.

    

14. Choose Next to go on to Arrange your Labels

    

15. It is time to layout the first label. Select More Items.

    

16. You will be given a list of fields in your Excel file.

    

17. From the list, select Name and press Insert. Select the next field and press Insert, Repeat for each field. When you are done selecting fields, choose Close.

    

18. You will see all of your fields in the first label. They are all one one line right now.

    

19. Click between two fields and hit enter to insert a carriage return. Repeat for each field. Your label should look like this:

    

20. In the Task Pane, select Update All Labels.

    

21. Don't be alarmed that Word added the "Next Record" tag to each label.

    

22. Choose Next: Preview your Labels

    

23. The labels will be complete. You can select Next: Complete the Merge

    

Wow. 23 steps. That is crazy.

Method 2:

Use Excel VBA

Using the techniques described in VBA and Macros Microsoft Excel 2016, I have developed a macro that I use to print Avery 5160 labels from Excel.

1. Open Excel. From the menu, select Tools - Macro - Security and lower the level to Medium.
2. Download the zipped file.
3. Unzip and open LabelMacro.xls. If you are asked about macros, choose to Enable.
4. There are four columns for data on the Database worksheet.

   

5. Open the Addresses.xls file. Highlight your range of data and Ctrl + c to Copy.

   

6. Alt + Tab back to the LabelMacro.xls file. Put the cell pointer in A2 and Ctrl + V to paste.

   

7. Using the mouse, press the button in D1.

   

8. The macro will copy & format the data on the Labels worksheet. Print preview and Print.

   

Summary

Holiday cards will be a snap using either method. Now, if there were just some way that Excel could bake the holiday cookies... To get a thorough understanding of VBA, check outVBA and Macros Microsoft Excel 2016.