This lesson is exclusive to members

Microsoft Word 2016 Bootcamp - Zero to Hero Training

Creating personalised letters in Word using an Excel spreadsheet - Mail merge

Daniel Walter Scott || VIDEO: 50 of 52

Download Exercise Files Download Completed Files


You need to be a member to view comments.

Join today. Cancel any time.

Sign Up

Hi there, in this video we're going to take this list from Excel, and merge it into Word using Mail Merge, and what it's going to do is it's going to automatically create pages in terms of our letter head, and it's going to put, all personalize, there's Daniel, there's Craig, there's Malcolm, all pulled from that database, and automatically make this long document for us, so we can print it off, super easy, using Mail Merge. Let's go do that now.

So first of all, open the letter that you want to work with, in our case, we're going to use our 'Word Exercise Files', there's one in '07 Mail Merge', we're going to use this one called 'Prospectus'. So it's got some basic text in here, what I want to do now is connect it up to the list. Now, the list, you need to have existing, so I've got a list in Excel, it's just kind of pulled from my database copy with first name, company, phone numbers, email addresses, addresses, things I'm going to need for this letter. You don't need to clean it up, you can just decide which of these columns you want to use. If you don't have it already, you need to insert a row on the top, that just kind of categorizes the columns, makes it really handy in Word, so company, first name, last name.

I've got that guy, I'm going to close him down, I don't need him now. Inside of this document, what I'm going to do is, I'm going to go to 'Mailings', and up here I'm going to go 'Start Mail Merge', I'm going to use a 'Letter', and I'm going to 'Select Recipients', I'm going to 'Use an Existing List', and if you're following me, grab the-- under 'Desktop', 'Word Exercise Files', there's one called '07 Mail Merge', and there is an Excel document in there. Click 'Open'. There's only one sheet in this case, one Spreadsheet in that document, that's fine.

The things I need to do now is, let's look at putting in a couple of bits to personalize it, so at the top here I'm going to put in 'First name', 'Last name', so in here where it says 'Insert Merge Field', you can see here, this is the list actually pulled from my Excel document, remember, there was 'Company', 'First_Name', Last_Name', this will look different depending on your Excel sheet, so I'm going to put in 'First_Name', then I'm going to put in a 'space', and then I'm going to put in 'Last_Name'. What's going to happen is, when I hit 'Finish & Merge', it's going to go to that list and replace 'First_Name' with the first name from that list, and the last name for that same person, so I want a bit of a space in between the two otherwise the names will be jammed together. 

You can preview the results while you're working, this is not finishing it. Can you see here, 'Preview Results', it's just pulling the first record from my Excel document, it's not finished, it's just a little preview, I'm going to turn that off. Put a ‘return’ in, I'm going to put in the 'Address', and that's what we need on the top. Along here, I'm going to say "Dear", and I'm going to put a 'space' in and then I'm going to say "Dear First_Name", and I’ll put a comma in, and a 'space'. That's all I'm going to use from this list, you can decide what you need to use. If I click 'Preview Results', it's going to say "Dear Greg"... Enclosed-- I'm going to do some spelling changes, but you can cycle through these lists. You could see here, I'm still previewing, so I'm just cycling to see what the rest of the values look like in my Excel sheet, but we're still not finished - I keep saying that, I know - and when you are ready to go, there's this option here that says 'Finish & Merge', and 'Print' option is really common, all you do is hit 'Print', and that's it, it's going to not save them if you know what I mean, it's not going to create a document with it all in there, it's just going to send it all to the printer with the right names on them, and be finished.

If you want to save them and have a printed version of them, because at the moment, if I turn 'Preview Results'-- this is like a working copy, and you'll always keep this one off. Save this one and keep it somewhere because it's first and last name, I can go in, and go 'Select Recipients' and I can choose a different list. So I do this every month, I send out a prospectus, I can just go to a different list, and it will use the same formatting from this one.

Let's say I do need a copy of this to send to somebody, or asking for a physical copy, it's this one here, it says 'Edit Individual Documents', click on this one, click ‘OK’, what it's done is, can you see, it's actually a different file, there's my other one before, there's my template version, it's created a separate version, and what it's done is, can you see, it's made a page on all of these, with all the different records kind of ready to go. I can do what I want with this, I can print it off, and yes, that's a separate document from the original.

All right, that's how to use Mail Merge using Word, and a little bit of Excel.