From the course: The Best of Word Tips Weekly
Perform a mail merge using contacts in Excel
From the course: The Best of Word Tips Weekly
Perform a mail merge using contacts in Excel
- [Instructor] I often get asked by people who have contact information stored in an Excel spreadsheet if they can use that spreadsheet in a mail merge in Microsoft Word. The answer is a resounding yes, it's actually quite easy. A lot of automation is built right in to this particular scenario and that's what we're going to explore in this week's Word Tip. We'll start with the Excel spreadsheet. If you have the exercise files, open up Contacts067. You'll notice that there are a number of columns and each of these columns represents a single field. You wanna separate them out as best as possible. First name is separate from last name, for example. You can see I have two address fields. One for the main street address and if there are any units or apartments, there's a separate column for that. Notice that some of them have and some of them do not have contents in those fields. Then we have city, state, zip, country, even though most of them are USA, there is one that's not, we'll keep that in mind. We even have a phone field. You may have additional fields like company name and titles, et cetera. We don't have to use all of the fields when we perform a mail merge in Microsoft Word. This is the file we're going to use. Keep it in mind as we switch over to Microsoft Word now and start a new blank document. Click file, then new, and select blank document. Now, we're going to go up to mailings. When you're doing a mail merge, it could be with a form letter, it could be with labels, it could be envelops. So in our scenario, let's say we wanna create envelops for each of the people we saw in that Excel spreadsheet. On that case, we go to start mail merge and this is a drop down, you can see we could start a new letter, a form letter, an email message, envelops, there's labels there, and a few other options, but envelops is what we want, so let's give it a click. Right away, you're going to see envelop options where you get to choose the envelop size. Mine's already there, size 10 the standard business envelop, but you can see there's a long list to choose from. I'm gonna keep mine at size 10. We can change the delivery address and return address fonts. You can see, and their alignment, but there's a preview of what it's going to look like down below. Exactly what I want, so clicking OK is going to create the first envelop. My return address is already in there, so it shows up. You can go in and type in your own return address if you want to and use it going forward. Mine's there from a previous Word Tip where we created a single envelop. Next, I'm gonna zoom out just a little bit, down here with the zoom slider. I'm gonna click the minus sign to zoom out to 130% so you can see the entire envelop. All we need now is those addresses down below. Now if you start clicking in the envelop, you can see nothing happens until you get low enough. Once you're down there, you can click inside this little area that's reserved for the address block. At this point, we can start typing in addresses or perform that mail merge. If we're going to use a mail merge, we have to select where the recipients are coming from, and that's our Excel file, so that's step two. Go up here to start mail merge in this section and click the select recipients drop down. What we wanna do is use an existing list. We don't wanna type out a new one, we already have it in Excel. So let's use an existing list. When we click that, we just have to browse to that file. And again, it's in your exercise files if you have them, and it's Contacts067, an .xlsx file. With that selected, click open and those are the contact we'll be using from our sheet, sheet one. You can see dates modified and created, the type is table. Also, down below, checked off, the first row of data contains column headers. And if you recall, ours does, those labels at the top of each column. We don't wanna actually perform a merge with those, they'll be used as column headers. Everything below will be merged. Let's click OK. Nothing really happens except all of a sudden on the ribbon, a number of other items are now available to us because we've selected our recipients. I want you to see what happened automatically in the background. Let's go to match fields, click there, and you're gonna see what happened automatically. You can see first name was matched up with our first name column. Others that aren't being used are not matched up. Last name, last name. Next one is address one, street address one. Address two, street address two. If anything doesn't match here, we can change it. Notice we can click these little drop downs and choose from our Excel fields or columns. But it looks like Word did a good job matching city, state, postal code, and zip. I'm in Canada, so I see postal code. Country or region is matched with our country. Business phone is matched up with the phone column, even though we're probably not going to use that in our address block. Lots of other options to be matched up if we needed them. All we have to do now is click OK and know that everything's going to work out right as we start adding the fields here. Now, we can do it the long way, inserting a single field at a time. We could insert first name, leave a space after that, and then insert the next field, last name. But then when we get to the addresses, if they didn't have a second address, unit number, apartment number, for example, how do we make sure a blank line doesn't show up? Well, all of that's built in to something that's much easier than us entering these one by one. So use your backspace key to take them out until they're gone, cursor's still flashing here. Instead, we'll insert an address block, give that a click. And now you can see that there are a number of check boxes already checked off. You can see the formatting for recipients' names. There's a preview over here on the right hand side of what that's going to look like. In this case, our first record does have Unit B. If we move using the arrows here to the next one, it doesn't include an apartment or unit number and there's no blank line. It's removed for us automatically. Notice also it doesn't show USA. However, when we get to the last record, it does show Canada and that's because only country regions that are different than the USA will be included. That's because of this radio button. If you want the country to be included if it's USA included, choose always include. And now when we go through our records in the preview, you can see USA does show up. That's up to you. All we have to do now is click OK and the address block is there. Wanna see what that looks like? Click preview results up top and it actually shows you records that we can navigate from left to right. Looks perfect. Click preview results to turn that off. Now at any time if you wanna see the coding or edit the coding that goes into the address block, click anywhere inside, hold down your shift key and press F9, that's function key nine. You'll see the entire code that goes into the address block. The backslash f is a code that says if there's nothing in one of these fields, remove the blank line following that field. That's something you'd have to add doing the insert merge field one by one on your own. You have to understand that coding. You can see there's some more down at the bottom. When you're done reviewing the coding or even editing if you wanted to, press F9 all by itself to get back out. There's your address block, the preview looks good. All we have to do now is go up to finish and merge, and choose how we wanna finish things up. Do we wanna go right to the printer with our envelops, send them in an email, or edit individual documents? I like this one, it creates a Word document with one envelop for each of our recipients. Go ahead and click edit individual documents. We'll make sure all records are being used and click OK. And there we go, there's our first envelop, and as we scroll through the document, we can see multiple envelops with each of those addresses, thanks to the mail merge and our Excel file containing our contact information.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
- Landon_Revenues_042.zip
- Press_release060.zip
- Best_of_LH_GuideVer1_025.zip
- LeafAndMortar_Inventory_040.zip
- Best_of_Landon_Hotel_Guide_041.zip
- Best_of_Landon_Hotel_Guide_037.zip
- Tech_Connect080.zip
- LeafAndMortar_Brochure_050.zip
- Landon_Sales_Cycle_045
- Tech_Connect088.zip
- LM_Estimate_070.zip
- Press_release026.zip
- Best_of_LH_GuideVer2_025.zip
- KEYBOARD_SHORTCUTS027.zip
- Best_of_Landon_Hotel_Guide_031.zip
- LeafAndMortar_Brochure_043.zip
- Press_release_029.zip
- MATH_QUIZ_044.zip
- About_LM_bilingual_052.zip
- DBAssignment_054.zip
- LeafAndMortar_Receipt_049.zip
- LM_OrgChart_053.zip
- Best_of_Landon_Hotel_Guide_056.zip
- DBAssignment_055.zip
- Price_List076.zip
- Press_release_033.zip
- Press_release_032.zip
- Best_of_Landon_Hotel_Guide_035.zip
- LeafAndMortar_Brochure_047.zip
- Press_release_034.zip
- Envelopes064.zip
- Best_of_Landon_Hotel_Guide071.zip
- Tech_Connect073.zip
- Press_release_036.zip
- LeafAndMortar_Brochure_062.zip
- Tech_Connect074.zip
- LeafAndMortar_Letter_038.zip
- LeafAndMortar_Brochure_063.zip
- Calculator075.zip
- Best_of_Landon_Hotel_Guide_057.zip
- Contacts067.zip
- Best_of_Landon_Hotel_Guide_068.zip
- Press_release_058.zip
- Press_release066.zip
- Tech_Connect083.zip
- Tech_Forecast084.zip
- DRONING_AROUND087.zip
- Tech_Connect081.zip
- Landon082.zip
- Best_of_Landon_Hotel_Guide077.zip
- BRAINSTORMING078.zip
- LeafAndMortar_Letter_039.zip
- Tickets079.zip
- Flyer060.zip
- Press_release061.zip
- Best_of_Landon_Hotel_Guide_001.zip
- Best_of_Landon_Hotel_Guide_048.zip
- Best_of_Landon_Hotel_Guide_002.zip
- DiabetesAssignment.zip
- Report_004.zip
- Best_of_Landon_Hotel_Guide_005.zip
- Best_of_Landon_Hotel_Guide_006.zip
- Best_of_Landon_Hotel_Guide_007.zip
- Press_release008.zip
- Press_release009.zip
- Best_of_Landon_Hotel_Guide_010.zip
- Press_release011.zip
- LH_Guide_012.zip
- LH_Invoice_013.zip
- Best_of_Landon_Hotel_Guide_014.zip
- Best_of_Landon_Hotel_Guide_015.zip
- Press_release016.zip
- Press_release017.zip
- Press_release018.zip
- Press_release019.zip
- Best_of_Landon_Hotel_Guide_020.zip
- LH_Conference_Room_Wishlist.zip
- Best_of_Landon_Hotel_Guide_022.zip
- Landon_Sales_Cycle_023.zip
- Best_of_Landon_Hotel_Guide_024.zip
- CV085.zip
- Red30Heart.zip
- Press_release_Oct.zip
- Tech_Connect_089.zip
- BrightPeople.zip
- LH_ExecTeam097.zip
- Tech_Connect090.zip
- CV091.zip
- Tech_Connect092.zip
- Tech_Connect093.zip
- Tech_Connect095.zip
- Tech_Connect096.zip
- Tech_Connect098.zip
- Tech_Connect099.zip
- Tech_Connect100.zip
Contents
-
-
-
Work with sections10m 34s
-
Create and update a table of contents7m 5s
-
Create a form template from an existing document10m 53s
-
Access and update document properties7m 4s
-
Search and replace on document formatting9m 1s
-
Create, modify, and update styles6m 15s
-
Adjust paragraph formatting with styles6m 32s
-
Create your own document themes8m 42s
-
Use page breaks effectively8m 28s
-
Work with page numbers effectively7m 16s
-
Control content with margins9m 53s
-
Organize page contents with tables7m 40s
-
Convert text to a table8m 29s
-
Create a PDF version of your document7m 48s
-
Use the navigation pane to organize docs4m 26s
-
Add and edit an Excel chart in Word10m 2s
-
Save time with AutoCorrect6m 9s
-
Create and share documents online6m 56s
-
Copy and paste multiple items at once5m 28s
-
Add and customize graphics in a document9m 28s
-
Use tables to align content8m 24s
-
Copy formatting from text and images3m 41s
-
Make your point visually with SmartArt8m 22s
-
Add a cover page to a document5m
-
Compare documents side by side5m 48s
-
Get more info out of the status bar3m 32s
-
Create your own keyboard shortcuts4m 5s
-
Tailor your Recent Documents list4m 14s
-
Save time with AutoText5m 41s
-
Create a list of all Word commands3m 9s
-
Use portrait and landscape together3m 49s
-
Create and apply Quick Styles4m
-
Test your document's readability6m 18s
-
Recover text from a corrupted file2m 25s
-
Gather images from a Word file2m 56s
-
Crop an image in a Word document3m 48s
-
Reduce file size by compressing images3m 30s
-
Insert sample or placeholder text4m 17s
-
See more ribbon options as side panels4m 19s
-
Add sequential numbers to a table column4m 2s
-
Return to edit points quickly2m 45s
-
Create a chart from an existing table3m 43s
-
Paste text without formatting2m 48s
-
Calculate equations from within Word3m 59s
-
Start Word in safe mode2m 21s
-
Change the default font for new documents2m 11s
-
Create hyperlinks quickly2m 51s
-
Reveal paragraph styles in Draft view3m 5s
-
Work with hidden text5m 9s
-
Add live screenshots to a document4m 29s
-
Draw lines quickly with the keyboard3m 17s
-
Translate text without leaving Word4m 38s
-
Create an org chart with SmartArt9m 32s
-
Use highlighting for easy reviewing4m 8s
-
Add and edit citations5m 50s
-
Customize Track Changes5m 12s
-
Take control of white space2m 36s
-
Save time with Quick Parts7m 3s
-
Draw a table from scratch13m 10s
-
Open and edit a PDF file in Word6m 35s
-
Add a watermark to your document5m 56s
-
Create files for older versions of Word5m 2s
-
Add, edit, and delete comments6m 37s
-
Create and print a single envelope4m 53s
-
Create return address labels5m 40s
-
Save to .odt (OpenDocument Text)3m 39s
-
Perform a mail merge using contacts in Excel8m 14s
-
Correct images in a document6m 26s
-
Perform a mail merge with Outlook contacts7m 27s
-
Select arbitrary text2m 29s
-
Paste all content from multiple sources5m 14s
-
Copy UI customizations to other devices3m 36s
-
Choose how text gets pasted by default4m 2s
-
Make a working copy of your document2m 31s
-
Add a calculator button to toolbar5m 57s
-
Create a one-click button for special symbols5m 10s
-
Work on two document parts at once3m 41s
-
Brainstorm with Click and Type5m 24s
-
Label items with sequential numbering5m 24s
-
Move content quickly without cut and paste3m 17s
-
Ease eye strain with the black Office theme2m 28s
-
Place an image inside a shape3m 11s
-
Get fancy with drop caps3m 37s
-
Use a drawing canvas to create space for drawn objects4m 51s
-
Create a resume with Resume Assistant4m 16s
-
Dictate text instead of typing it3m 8s
-
Insert multiple views of a 3D object4m 41s
-
Make parts of an image transparent5m 5s
-
Convert a list to a SmartArt graphic3m 25s
-
Precisely size and position an image4m 53s
-
Add content to a printed page2m 13s
-
Select objects hidden by other objects2m 16s
-
Strip formatting from text2m 15s
-
Send a mass email with email merge5m 53s
-
Save and share directly to Google Drive2m 40s
-
Prevent accidental sharing of tracked changes and comments3m 35s
-
Create visual interest with icons4m 28s
-
Ensure accessibility with the Accessibility Checker4m 22s
-
Improve your reading experience with Learning Tools4m 32s
-
Insert and modify SVG graphics4m 40s
-
Create your own fractions2m 33s
-