Today, I'm going to show you how you
can create personalized Word Documents from data in Excel in one go. Now, the
process I'm going to take you through is called Mail Merge. The purpose of Mail Merge is
for you to be able to create personalized letters to many people without actually writing letters
to many people. So, you have a standard template and you just want to change parts of that and
customize it to each person. So, you can use Mail Merge to do that. But of course, you can also use
this for other purposes as well, not just letters. Use it to dynamically link your Excel data
to Microsoft Word. Let's take a look. Assume I have this Word file and I want to use it
to send invoices to different customers. This is a template; it's standard. What's different is
the content that goes in here, and that content comes from Excel. So, in this case, I have my
Customer, my Company, the Address (Line 1, Line 2, Line 3), and the Services that
I've provided to the customer and the Date. Now, this is not a Date Field; it's just a Text Field
and I'm free to input the date in any way I want. Then I have the amount for Service Line 1.
Then I have a second line on the invoice where I can add additional services, and then I
have the amount for that service as well. Then, in Excel, I sum up these two values. So, any
calculations that we need to do, we're going to do it in Excel. I have the invoice number, invoice
date, and the email of the person that I want to send these to. All of this information that's
dynamic is sitting in Excel. What I want to do is to use Mail Merge to populate this information
in separate emails or letters in my Word Document. So, each person should receive their own
invoice. Let's get started with Mail Merge. First step is to go to the Mailings Tab and start
Mail Merge. You have the option to create letters, email messages, envelopes, labels, directory,
and normal Word Document. In this case, I'm going to go with a normal Word Document. This gets the
process started, now I can select my recipients. This is where I need to create a connection
to my Excel File. So, I'm going to go with "Use an Existing List..." and then browse for
my Excel File. Mine is sitting right here, and it's called "Outstanding Invoices". Now, click
on Open. Next step is to select the right sheet. So, I have two different sheets in there. One is
called Invoices. My information is sitting here, and also notice there is a check mark for
"First row of data contains column headers". This is important because those column headers are
going to show up as your merge field names. Now, let's go with OK. Notice the moment we created
a connection, the other features became activated. If you're wondering how I created this in
the first place, we'll take a look at this. It's actually a table, but I didn't
create this table in Word. I cheated and I created this in a Spreadsheet and then I
copied and pasted it in here, and it just inserted everything as a table. It's just a fast way for
me to create these type of templates in Word. Okay, so now that we have our connection, let's
go back to Mailings and let's make the parts that we need dynamic, dynamic. The logo, this part
here, stays the same. This is the first thing that we need to make dynamic because this is
going to come from our Excel file. And just to show you how that looked, I have a separate
column here for Invoice Number and Invoice Date. So I'm going to select this and replace this
static number by going to Insert Merge Field and replacing it with Invoice Number. Let's do the
same for Date. Select Insert Merge Field, Invoice Date. Payment terms, that's the same for everyone,
so I'm going to leave that. Next is the Company, and then Customer, and my Address. That's Address
Line 1. If you just click directly on the icon, you get the pop-up here. You can also insert
fields this way as well. That last one is Address Line 3. Now comes the
Services. Service Line 1, then Date Line 1, and Amount Line 1. Okay, I'm going to quickly do
Line 2, and then let's catch up. Moments later. Now finally, let's do the total. Insert Merge
Field, and Agreed Amount Total. So remember, this calculation isn't made in Word, it's already done
in Excel. We're just bringing the number over. That last part stays the same. Okay, so far so
good. Now we have the ability to preview the results. Just click on Preview Results. That's the
first line that we have: Robert Spear, Programming new tool. This is our Date Text Field where
I'm flexible to input the date any way I want, and then that's the Price and the Total. So I can
scroll and go to the next Field, that's Kim West, James Willard, and so on. So things look good,
let's just center this as well, and update the formatting of the numbers. Because on the Excel
side, I have the numbers formatted the way I want, but the formatting doesn't come with. We actually
have to specify that in Word. To do that, you can Right-Mouse Click, toggle Field Codes, and add the
formatting to it right here. So inside the Curly Braces, just at the end, put a Backslash, put
the Hash Sign to specify that this is a number, and the way I want this number formatted is
with a Thousand Separators, so Space, Comma, and 0 would make this a whole number, dot00 would
add two decimal places, right? So that's the type of formatting you need. I'm just going to
copy it because I also need to apply it to here. Before I apply it, let's just make sure it looks
correct. When I toggle this back on, I can see my number formatted correctly. So let's go ahead
and update the second line. Toggle Field Codes, and paste in the formatting. Now, if you want
to add the Currency Symbol, you can. Let's actually do that for the total. Right-Mouse Click,
toggle Field Code. I'm going to paste in my code, but this time I want the Euro Symbol here. So
just before the Zero, right after the comma, put in the symbol that you want. Now let's go
ahead and preview the results. That's how it looks. Okay, so now we're ready to merge all of
this. Before I finish this off, I just want to show you how many different options and how much
flexibility you have here. You have the ability to edit the Recipient List. So in case you don't want
to send an email to everyone, or print out every sheet, you can uncheck the ones that you don't
need. If you didn't have a template to begin with, you could start off by adding your own Address
Block, or adding your Greeting Line, and Matching the Fields. In this case, we didn't have to do
any of that because I already had a starting point. I already had my Excel File and all I had
to do was insert the Merge Fields. And of course, you can use these as often as you need in your
document, and anywhere you want in your document. Now, let's go ahead and merge this. I can either
edit individual documents. This is going to create a new Word Document with a separate page for
everyone. Just to quickly show you the end results, let's actually try it out. Select it and
now I can decide if I want to merge everything. So, all is going to take anything that has a check
mark beside it. In my case, everything has a check mark beside it, so it would run it for everything.
I can just run it for the Current Record, or I can decide to run it from 1 to, let's say,
the 4th record, and then click on OK. Now it's going to go ahead and create a separate file, and
this file has a page for the different records. I can save this and I can print this out as I
need. Another option is to print the documents. You again have the same options. This time,
I'm going to go with All and click on OK. You can print it to a PDF Document or just print
it directly to your printer, and then click on OK. I'll just click on Cancel. Let's go ahead and
take a look at the last option, which is to send Email Messages. Now, it's important here
that you have the email of the people, and in my Excel File, that last column is called
email. So, it automatically recognized that there is an email field. If it doesn't find it
for you, you can make the selection here, add a Subject Line, and decide if you
want to send it as HTML, Plain Text, or Attachment. Well, plain text is not going
to look good here, so I can send it as HTML or attachment. I'll just go with HTML,
and I'm going to show you how that looks. Let's just go and send it from 1 to 2. I don't
want to send everything right now. Click on OK. Now we quickly saw Mail Merge in the background.
Let's switch to email and I'll show you how that looks. This is how the HTML version of the email
looks. How does this look if it was an Attachment? Let's try that as well. Go back to Finish and
Merge, send Email Messages. This time, go with Attachment and click on OK. Because it's creating
a separate Word File and it's attaching it, you actually have to allow this process to
happen. It's attaching the first file and now the second file, and the process is done. So
remember, I didn't run it on all the records. I just ran it on two records. Now, just to show
you how that looks, that's the email. That's the attachment. This is for Kim West. So when
I open this, this is the invoice for Kim West. Okay, so as you can see, you have a lot of
flexibility when you use Mail Merge. This wraps up our Mail Merge tutorial. Many thanks for watching.
Thank you for being here, for your thumbs up, and for supporting my channel. I really appreciate
that, and I hope to see you in the next video.
