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.
Amber MacArthur, Bill Jelen, Leo Laporte, Andy Walker on the set of Call for Help on TechTV Canada.
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 & Macros for Microsoft Excel, 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.
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 out VBA & Macros for Microsoft Excel
For the BEST TV show on technology, check out Call for Help.
This tip was originally published on November 17, 2004 and the show originally aired on December 9, 2004. The permanent URL for this page is http://www.mrexcel.com/tip081.shtml.
If you are looking for show notes from another episode, visit my complete list of TechTV appearances.
MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.