Hi everyone, my name is Kevin. Today I want to show you how you can do a mail merge with custom attachments and custom subject lines using Microsoft Office. Now I did a video a little while back on doing mail merge just out of the box using Microsoft Office. If you want to do things like attachments, if you want to modify the subject line, if you want to say CC or BCC someone, these are a little bit more advanced and you can't do these out of box. You need to use a macro enabled sheet to help you with this. So if you're just looking for basic mail merge, I have a link down below where you can get to that video. However, if you are looking to doing some of these more advanced scenarios, stick with this video and I'll show you how to do it. Now, first off before we jump into it, one of the main questions is, well, what is mail merge? What mail merge allows you to do is you could send a customized, whether it's emails, letters, address labels, you could basically send out all these things by customizing information in them. So the easiest way to explain it is imagine I'm an energy company. So maybe we have the Kevin Energy Company and what I want to do is I want to send an invoice to my customers and within my email invoice, you know, maybe I want to say, hi, the person's name. I want to include the account number. I might want to include other information about them, the due date and mail merge allows me to customize many messages that I send out very easily, so I don't have to go through message by message by message. Now, if you're only sending out five or 10 messages, you could probably just do it manually. However, imagine the case that I'm this energy company and I need to send out an invoice to 10,000 people. I'm not going to sit there doing them one-by-one. Instead, I'm going to let the computer automate that for me. Okay, so the version that I'm going to use, this is Office 365. This is the latest and greatest. However, you should be able to follow along even if you have an older version of Office. So if you have 2019, you'll be good. If you're on 2016, 2013, 2010, any older version, mail merge has been around for a little while, so you should be able to follow along. But it might be slightly different from what you see on my screen. And as full disclosure, before we jump into it, I work at Microsoft as a full time employee. All right, well enough talk. Let's jump onto my desktop. So here I am on my desktop and what I want to do first is I want to just show how we're going to do this merge scenario. So what I'm going to do is I'm going to open up Microsoft Excel first, and I have this sheet that I've already created called mail merge with attachments. And I want to walk through what's on this sheet. So a few of the things, I have my customer names of my energy company. So James, John, Robert, they're all customers of mine. And then I have their last name in the sheet as well. I list it out when their bill is due. So here's their due date. So here, throughout the month of September, these people need to pay, submit their payment. I've also included their account number, and what I've done then is I've called out the invoice location. So each one of these customers has an invoice that I want to attach to this mail merge. And so I've called out each individual document. So here on my desktop, I have all of the mail merge documents. So I'm going to go ahead and open one of them just to show what this looks like. So this is just kind of a email message or let's say an attachment that I want to put on that's customized for James Smith. So this would be kind of a custom attachment. And then John Johnson, he has his own customized invoice that I want to attach. So each one of these customers has a unique invoice. And then also I have the customer's email address. Just for simplicity, I put the same email address down, but you can imagine in the real world, I would have a different email address for each one of these people. Now, since we're doing attachments as part of this mail merge, it's very important that in your Excel sheet, you call out where that individual attachment is for that person who you're sending it to. So just keep in mind if you're creating an Excel sheet to include that information in there. So that's what's included in this Excel sheet. I'll also post it down below so you could access it if you just want to see a sample. Okay, so now we're done with Excel. I'm going to close this. And one thing I also want to show is I mentioned that we're going to do a mail merge, not only with customized attachments for each individual, but I also want to include an attachment that everyone is going to get. And I'll show you that. That's a PDF file that I'm going to attach. And this one is just called celebrate.pdf. And because I'm an energy company, we're celebrating how we've been helping our environment and celebrating, I misspelled that, I know, celebrating our community. So we're going to include this as part of all of the emails that we send out. And we probably fixed that typo before we send it out. So just to summarize, we have five customers. Each one of them is going to get their own invoice. And then we're also going to attach a PDF, which celebrates the company. So those are going to be the attachments that we put on the message. Now, what we need to do is we need to get a tool that's going to help us with this because mail merge out of the box doesn't do this. So what I'm going to do is I have a website open already. And I have a link down below in the description and then also as the first comment. What we're going to want to do is we want to download this tool called merge tools add-in. Now this guy, Doug Robbins, he's a Word MVP. He built this macro enabled sheet that'll allow us to do this. So thank you very much, Doug. It's really fantastic that you created this and let's go ahead and download it. So I'm going to click on this one and it's going to download now. So let's pull that up. So here I downloaded it and then I'm going to show it in the folder. Now I'm going to close out OneDrive since we got the file. Okay. So a few things that we're going to need to do. So I'm going to jump in. I've actually downloaded it twice. So I'm just going to open up merge tools and add in. We're going to open that zip and I'm going to open it up. Some of the things you'll see in here, there are a whole bunch of PDF files. There's a read me. There's instructions on how to use each of the tools, but that's what you're here for on YouTube. I'm going to walk you through how to do this. The thing that we care about is this merge tools file. And so we're not going to double click on it. We're just going to highlight it. And what we need to do is we need to put this into the startup folder of Word. How do you get to the startup folder of Word? Well, let me show you how to do that. I'm going to open up Word here, click into a blank document. Now we're going to go up to, so I clicked on the file menu. We're going to go down to options and within the options menu we're going to click on advanced. I know this options menu, there are so many buttons and check boxes like kind of gives me a headache when I look at this since there's so many different controls, but we're going to click on advanced and then we're just going to scroll down this list most of the way down to a section called general and under general you have something called file locations. Let's click on that. And at the very bottom of this list you have something called startup and you could click on modify and that'll show you the location where we want to put this file. So I'm just going to copy that. So we're going to copy that location and now I'm going to open up, so I'm going to open up File Explorer. I actually already have it open and it's in the startup location here. You could also take that address we just copied and you could paste that in here if you want to go to that exact location. And what we're going to do is I'm going to take, I'm going to take this file that Doug so graciously created and we're going to drag and drop that over into Word startup. Basically what this does is when Word starts up, it makes sure that it uses this file. So now what I'm going to do is I'm going to close out Word. So we're going to close Word and now I'm going to reopen Word. Okay, so now Word is reopened and I'm going to click on blank document. One of the things that you'll see is added now because I dropped that in my startup folder is this merge tools. This is what we're going to use for attachments, Okay. Now to set up the mail merge, what we're going to do first is we're going to click on the mailings pivot and then within the mailings pivot we want to click on start mail merge. Okay, this is a new mail merge and we're going to have to go to letters. Now even if you're sending out an email or any other type of mail merge, make sure to select mails because Doug's tool works with letters. So we want to select that one. Don't worry, you could send as other types if you'd like. So we're going to click on letters and now I'm going to start the, I'm going to start the mail merge. I'm going to say, oh actually before I do that I need to connect it to my spreadsheet. So as a next step we're going to click on select recipients and I'm going to say use an existing list. And now what I'm going to do is I'm going to navigate to my Excel spreadsheet that I created and then within this dialogue I'm just going to say we're going to use sheet one. My spreadsheet only has one sheet and my first row of data contains column headers. That's where it says first, last, email address. Those are my column headers. So yes, I do have column headers in there. And so now I've connected my Word sheet to my Excel spreadsheet. So what we want to do now is I'm going to say dear, we're just going to, we're going to type the email message that I'm going to send to all my customers. What we can do now is I can insert different fields from my Excel sheet. So I say dear and let's say insert and so now this shows all the columns of my spreadsheet. So I had a first name column, a last name column, a due date column and so on and so forth. So we're going to say dear first name, your bill, let's see, so this is an energy company. So your electric bill for account number, and now one of the cool things is we're going to insert the account number, is due on, and now we're going to insert the due date. So all I'm doing is I'm inserting different values from the columns in my Excel spreadsheet. So we've inserted all that and then I'll say thanks Kevin Energy. That's the name of my company here. And so now we could click on preview results. And so here it says a dear James, your electric bill for account number 54329 is due on 9/15. Thanks Kevin Energy. And then I can even say something like your invoice is attached and I could also say please also see the insert celebrating national utility month. Okay. So the email is ready to go. And now what you would typically do in just a normal mail merge is you would click on finish and merge. But we want to add an attachment so we have to use this out of box add in that Doug created. So what we're going to do first is we want to save this sheet. So I'm just going to go ahead and save as and I'm going to put it on my desktop and we're going to call this mail merge with attachment. Okay so I'm going to go ahead and save this file. And now what we could do is we're going to click on this pivot on the top called merge tools. And within merge tools, we're going to click on merge with attachments. Okay so let's click on this. And so Doug Robbins created this add in and if you find it very valuable and if it's helping you do work feel free to donate some money. By donating you can also make it so that you don't see this message anymore. So I'm going to click on the X and now the mail merge tool is going to pop up. So what you see here is select the merge fields that contain the path and file names of attachments. And I want to include a unique attachment for each one of my customers. And so I'm going to scroll down here and we see the invoice location. Remember when I showed the spreadsheet earlier it had a location for their invoice. This is that column right here. So invoice location and I'm going to go ahead and add that in. So what that means is every person who receives an email will get the document for the location specified in that spreadsheet. And what I also want to do is I want to include an attachment that everyone gets. So I'm going to browse for attachment to be included with all messages and that's this celebrate PDF. Now what you can do is if you just want to send the same attachment to everyone, you could ignore this first step. If you only want to send customized attachments and you don't want to have the same attachment for everyone, you could skip this part. So you could do kind of whatever your scenario is you could choose how you want to handle that. Now merge destination what this means is how are we actually going to distribute or send our message. Are we going to do an email message. Is it a Word attachment or is it a PDF attachment? What we're going to do, I'm going to do an email message. And so now what I could do is a few other things. So we're going to select the email addresses that it's going to go to and in my spreadsheet I also had the email address so we're going to select email address. And what you can also do this is kind of a power tool some people have asked me for this in my original video. But what if you need a CC or BCC someone. Well you could specify either one of those here if you want to do a carbon copy or a blind carbon copy. No problem to do that. And then also data source contains a subject field. So here I could type in a subject. I could just type in any any subject that I want or I could customize the subject. So I'll say data source contains a subject field. And what I'm going to do is let's insert the account number. So I'll say bill. Let's say electric bill for account, and then we'll insert the account number and then we'll say is due soon. So what this will do is as part of the subject it'll say electric bill for account and then there'll be a space. It'll insert the account number and then there'll be another space and it says is due soon. And so I've had I have it all set up now I have my individual attachments. I have a generic and just a generic attachment that everyone will get. I have my email address, the account number, and then my subject. So let's go ahead and continue now. And I want to do now I could specify do I want to do it to everyone in my list do I want to select specific people from my sheet or do I just want to do it to individuals. So remember I had five customers. I can say hey just do it to this one customer but I'm going to go ahead and do it to all of them. So we're going to click on OK. You'll see kind of in the background it's creating my emails. And what I'll do now is I'm going to click into Word and now you see that the bill is arriving. So here I see all my emails in my outbox. Here it shows the emails heading out and now I'm going to go to so I sent it all to Kevstrat2@outlook.com and so here you see my bill that I received by email. So it says Dear William your electric bill for this account is due on this date invoice is attached. And so here I could click on I'll click on the attachment that's going to open Word. Here's my invoice attachment for that customer. I could also click on this celebrate PDF and that's the same PDF to all the customers. So some of the cool things I did is I included attachments as part of the mail merge. I customized the text of the email message, and I also customized the subject line for each individual email. And so here you see the five emails that went out to my customers. Now another thing that I might want to do too is let's say in Outlook you have multiple accounts set up and you want to specify a specific account that sends out this mail merge. What I could do is I'll click into I clicked on file, then within the info view, click on account settings account settings they do account settings twice. And then here you see all your accounts that you have set up within Outlook. Now if I set you know kevstrat3 as a default or kevin.stratvert@live.com as the default, whatever the default email account is, that's the one that's going to send out the mail merge. So in this case I'll just leave that as a default and so there my mail merge went out. That's how you do a mail merge with attachments. That's how you could customize a subject line. That's how you could cc bcc people and how you could set your default email account. Now there are a lot of different steps involved. You have to download a macro enabled sheet but once you've done it once it's actually a pretty easy process to go through. And you know whether you're sending something out to 100 people or 10,000 people, this is a really powerful solution. I want to give thanks again to Doug Robbins who created this. If this is something you're using a lot please consider donating because I'm sure Doug would really appreciate that and he really did build a valuable tool here. I hope you found this tutorial video helpful. If you were able to send out a mail merge using this please give this a thumbs up. If you want to see more videos like this in the future please hit that subscribe button. And lastly if there are any other video topics that you need help with anything else that you're trying to figure out to do or things that you want to learn about please leave a comment down below and I'll add it to my list of videos to create in the future. And hey that's all I had for you today. I hope you enjoyed it and I'll see you next time. Bye.
