How to Mail Merge using Power Automate

hi everyone kevin here today i want to show you by far my favorite way of doing an email mail merge we're going to use tools that anyone can access they won't cost you anything you don't need a subscription we're going to use excel online together with power automate to do a mail merge now if you've never used excel online or if you've never used power automate before don't worry i'm going to make it super clear we're going to go through step by step how to do a mail merge so don't worry i got your back first off you might be wondering what is a mail merge well let's imagine that you have a large family and christmas is coming up and you need to send out an email to all your family members maybe talking about what happened during the year and you want to customize or personalize each individual message maybe you want to say dear uncle bob or dear aunt susan and then maybe within the message you want to call out their kids maybe you want to call out what when their birthday was and maybe you then want to sign it you could go through and maybe you have to send out 100 of these emails you could go through manually type in all the information and then send them out but that's going to take a long time instead let's rely on the computer to do the heavy lifting for us and customize all of these messages we're gonna set up an excel table with the information and then we're gonna use power automate to send out the emails we'll be able to send out emails with whether it's outlook or even gmail either one we have that customizability lastly with this mail merge first off we're going to start with the basics and then i'm going to show you how you can do more advanced mail merge let's say you want to customize a subject maybe you want to include an attachment or maybe you want a bcc or cc someone you can do all of that all right well enough talk why don't we jump on the pc and let's see how we could do this here i am on my pc how do we get to excel online and how do we get to power automate let's head to the website office.com if you already have a microsoft account you can click on sign in i mentioned at the beginning that you can do this entirely for free and you don't need a subscription if you don't yet have a microsoft account you can sign up for the free version right down here you can create a free microsoft account once you create your free account click on sign in and log into the authenticated version of office.com here we are on the authenticated page of office.com as a quick side note this page is near and dear to my heart this is the experience that i worked on when i worked at microsoft full-time once you log into office.com first off we need to create an excel sheet that's going to contain all the information that we want to insert into an email so as a first step let's click on excel over on the left hand side once you click into excel this opens up the excel start page i'm gonna simply start with a blank document however if you already have your data let's say in excel spreadsheet you're gonna have to make sure you save it on onedrive by going through excel online by default the new document will already be saved into onedrive i'm now in a new blank workbook in excel online and i need to add data in that i want to use for my mail merge luckily i already have some data that i want to use and if any of you have watched any of my tutorial videos before you probably realize that i'm building up the it capabilities of the kevin cookie company so today seemed like a good opportunity to build out my invoicing for some of my larger customers i've now pasted data into the excel sheet and i want to take a moment to describe what's here there are a few different columns i have a name column with customer names i have the customer's email address for simplicity i'm using the same email address but in a real mail merge i'd have different email addresses here i have how many boxes of cookies they ordered how much they owe for all of those boxes and then the due date with a mail merge i'm going to take this information and i'm going to insert it into a personalized email message for example i might say hi bob your order of 150 cookies is due on this date for the amount of 7500 and then each individual customer will get their own personalized email message one thing that's extremely important is you want to make sure you have an email address in here as well as any other information that you want to use in your customized message we're going to use this as a source for all of the personalization for your email message once we enter all the data into this excel spreadsheet we need to do two more things before we move on to power automate up on the top bar you can rename your excel spreadsheet just so it's a little easier to find right now my file is called book1 let me call it something more descriptive i gave the spreadsheet the name kcc billing that sounds good to me next i need to make sure that the data i entered here is in a table format to do that i simply highlight all of my data using my mouse and then up under the home tab i can go over within the home ribbon and click on format as table and i'm just going to go with this light design it doesn't really matter what it looks like i just need to make sure that it's a table this pops up a dialog that says format as table and there's one option here that says my table has headers as you can see here in my table name email boxes of cookies these are all of my headers so i'm going to make sure this box is checked and then click on ok now you can see all of my data is in a table format and i've now done everything that i need to do in excel online so we can now move on to power automate i'm back on office.com and next we want to go to power automate you might be wondering what is power automate power automate allows you to automate very many things and it allows you to connect different services today we're going to be connecting excel online with email whether it's gmail or outlook to send out a mail merge but you can do a lot more than that if you're interested in learning all about power automate i have a tutorial video that you can access in the description if you've ever used if this then that before power automate is very similar to that on the office.com home page to get to power automate you won't see it as one of these core apps here however if you click on this icon in the bottom left hand corner for all apps this will show you all the different apps that you have access to now once again even with a free account you get access to power automate now that we're in what's called the app gallery here i can see all of my different apps and right over on the right hand side i see power automate let's click on that this opens up power automate and once again i have a tutorial where you could learn more there's quite a bit here and you can do a lot of amazing things using power automate today we're just going to stick to a simple mail merge over on the left hand side let's click on the option that says create this opens up a screen where we can create different flows for instance you can automate a flow you can even set up scheduled flows let's say you want to send an email every week you can do that but for now we want to create an instant flow what this means is if you have your excel spreadsheet and you want to send out say bulk emails to a list of people within that spreadsheet you are going to trigger the start of this flow so let's click on instant flow this opens up the build in instant flow dialog and first off we need to give this flow a name i'm going to call it kcc billing mail merge once i type in the name i need to choose how i want to trigger this flow i want to manually trigger this flow so anytime i want to send out a mail merge i'm going to initiate the mail merge i'm going to click on this option and then click on create next we can start creating our flow or our mail merge as a first step let's click on the option that says new step first we want to connect to our excel spreadsheet because we want to use that as part of our mail merge if you have a work or school account you're going to want to connect to excel online or business if you have a personal account or if you're using just a free account or a consumer account we want to use excel online for consumer so i'm going to search for excel once i type in excel you'll see an option for excel online onedrive let's click on this next it shows me a whole bunch of different actions that i can take on my excel spreadsheet now once again power automate allows you to do all types of different automations so you can add rows to an excel spreadsheet you could delete rows depending on different things that happen today though we want to use it for mail merge so i'm going to select the option that says list rows present in a table what this means is it'll fetch all the data that we added to a table in our excel spreadsheet and it'll make it available for our mail merge let's click on this option next i need to select the file that we want to use for our mail merge i'm going to click on the picker this opens up a file picker that shows me all of the different files that are in my onedrive account now once again i mentioned that you need to make sure that your excel file is in a onedrive account if you created it in excel online by default it'll be in onedrive down here at the bottom i see my kcc billing file i'm going to click on this i want to use this for the mail merge next i need to select the table that i want to use and that's why it was so critical to add a table to the excel spreadsheet i didn't give it a fancy name it's simply called table2 so i'm going to select this in excel online you can also give it a more descriptive name so it's really clear what it is now that we've added the spreadsheet we want to add the email step so once again let's add a new step so so far i'm getting the excel spreadsheet and now i want to take another action and i want to mail this out and i want to use outlook to do this so i'm going to search for outlook here you see outlook.com shows up as one of the options if you have a free consumer account you're going to want to use outlook.com to send out the email earlier in the introduction i said that you're not just limited to outlook for instance if i type in gmail you could even send your email merge through gmail so you could use any email provider that's connected to power automate i'm going to use outlook.com for this example once again with a free microsoft account you also get access to outlook.com let's click on this once i click on outlook.com i have all these different actions that i can take the one that i care about though is the one that says send an email v2 with outlook.com let's click on this option this now opens up an email field where i can start customizing what my email looks like at the very top i specify who i want to send the email to if you remember in the excel spreadsheet i had all the email addresses listed out let's click on two and then over here you see an option that says add dynamic content the way to think of dynamic content in our excel spreadsheet that's all content that we can pull into here let's click on add dynamic content and here i see the email address from the excel spreadsheet i could also click on see more and that'll show me all the different fields that i can use for the to field i want to use the email so let's select that item back in the text field i have the email address entered now i could specify a subject for my email now one of the great things about doing a mail merge through power automate is that you can customize the subject if you've ever done a mail merge through microsoft word before this is one of the deficiencies where you can't customize the subject power automate allows you to do this now i want to type in the person's name and then i want to say your kevin cookie company invoice is due on and then i want to list out a date just like i did with the email address here too i can insert dynamic content from my excel spreadsheet so i'm going to click on add dynamic content and first off i want to list out the name then i'm going to have a comma and then i'm going to say your kevin cookie company invoice is due on and here i want to insert the due date now one thing to note is these are all of the column headers that i had in my excel spreadsheet let's click on due date and now i have a fully customized subject next i want to type in the body of my email message i'm going to click into this field and then i'm going to type in deer here i want to insert the name once again and then i want to say we hope you enjoyed your kevin cookie company cookies your bill for and then i'm going to list out the amount due so we're going to have the amount due then there's going to be a space and and now i'm going to list out how many boxes that they ordered so here too with dynamic content i'm going to select how many boxes of cookies so it'll have the number then it says boxes is due on and at the very end i'm going to insert the due date now what's really neat here is as i compose my email message as i compose my subject i can simply click on all of this data that i had in my excel spreadsheet and it'll use it to customize my message now at the very bottom which is very fitting i'm going to say thanks cookie monster now that i've finished composing who it's going to go to with the email address i have a customized subject and i also have a customized body let's click on show advanced options within advanced options i could specify who it's coming from who's on the cc line and also who's on the bcc line with mail merge in word unfortunately you can't do cc you can't do bcc so this is another advantage of sending a mail merge through power automate you also have the option to attach files and in a moment i'll show you how you can attach a file for now though i want to show how you can just send out this mail merge as is and we'll come back to attachments once we send out the initial mail merge once we're all done let's click on save i've now finished creating my mail merge we have all the data in excel i've customized my message next let's click back here i'm now on the main flow screen and this will show me details about the flow that i created it'll show when i've run it in the past it'll show me that i'm connected to excel online it shows me that i'm connected to outlook.com as a next step i need to run my mail merge and up above i can click on run and that'll kick off the mail merge let's try doing that when i click on run this opens up a pane on the right hand side and first off it checks to make sure that it's able to connect to the excel sheet and it also checks that it's able to connect to outlook.com if this is your first time doing it you'll likely need to log in type in your username password again to make sure you're connected to these services but once you do that you should see a green check mark for each of these once you do let's click on continue once i click on continue i now have the option to run the flow let's run it and start sending out some of these invoices it's now successfully started my flow and i can view the progress on the flow runs page when i click on the flows run page here i see that the mail merge completed in seven seconds and it succeeded it once again if you've ever done mail merge using word excel and also outlook you'll notice that the mail merge tends to take a bit of time this is nice because it all happens behind the scenes now that i'm in my email here we can see that the mail merge successfully completed you see that the subject line is customized and you also see that the message itself is also customized next i want to go back to power automate and show you how you can include an attachment as part of your mail merge to get back to the flow that we created let's click on my flows over on the left hand side here we see the kcc billing mail merge let's click on this once i click on it up in the top left hand corner i can go back and i can edit this flow now i'm back in the flow and if i hover over here i can add an additional step i want to attach a file so let's add another action and click on this item that says add an action within here i want to attach a file that i have in onedrive once again we need the file to be in onedrive i'm going to type in onedrive and depending on what type of account you have again if you have a work or school account click on onedrive for business if you have a personal or a free account click on onedrive this opens up all these different actions that i can take within onedrive and i want to get a file so i'm going to scroll down and click on get file content once i click on get file content i now want to pick the file within my onedrive i'm going to click on the picker click into root and now i'm going to scroll down and click on one dot docx i want to include this file in every single invoice that i'm sending out you can imagine that maybe this is a flyer or maybe you have a go green attachment any type of attachment you can even do it so you have customized attachments for each individual now that we've specified which file we want to attach we now need to include it as part of the email let's click on the last step of our flow if we go down to the bottom again we see our customized email message let's click on show advanced options within advanced options we want to give the attachment a name i'm going to call this flyer dot docx and then down here you can specify the file content from the previous step this is dynamic content and this will be the attachment once we're satisfied with all of this let's click on save once we save it once again i can go back and now we can run this new flow up here again let's click on run this opens up the pane on the side and let's run the flow now if i go back to my kev strat2 and outlook.com inbox here you'll see i have the customized email message and it also includes the attachment flyer.docx so not only am i able to include attachments and i can completely customize the message now one thing you might have noticed in my mail merge is if we look at the amount due and i also look at the date it took these and it didn't maintain the formatting when you insert fields like this using power automate it's a best practice to convert it to text before you start the mail merge if i go back into excel online you can right click on the item click on number format and change it to text first and then you could format it to exactly how you want it to appear within the message i sent another mail merge and here now for the dollar amount you see the dollar sign appear and now for the date because i formatted it as text you see that it appears as i would expect it to so this mail merge is now exactly how i want it to be now as this example shows it's always a good practice to test out your mail merge first send it to one of your email accounts make sure that it's working properly before you send it out to a massive number of people the last thing that you want to do is send out a mail merge only to discover that something wasn't formatted correctly or something wasn't exactly how you wanted it to be alright well that's how you can do a pretty advanced mail merge entirely for free using excel online and power automate if you found this video helpful and you now know how you can do a mail merge please give this video a thumbs up if you want to see future videos like this hit that subscribe button that way you'll get a notification anytime new content like this comes out and lastly if you want to see me cover any other topics in the future leave a comment down below and i'll add it to my list of videos to create all right that's all i had for you today i hope you enjoyed and as always i hope to see you next time bye you

Channel: Kevin Stratvert

Views: 90,558

Rating: 4.972558 out of 5

Keywords: kevin stratvert, mail merge, mail merge in excel, excel, power automate, gmail, outlook, mail merge in outlook, how to mail merge, microsoft, outlook mail merge, excel mail merge, tutorial, excel mail merge tutorial, how to do mail merge, mail merge excel, email, mail, merge, office 365, microsoft 365, 365, attachment, mail merge with attachment, custom subject, bulk, bulk email, bulk mail, how to, mailmerge, send email from excel, how to mail merge with attachments, attachments

Id: Nl5AoLusNR4

Length: 20min 22sec (1222 seconds)

Published: Fri Oct 02 2020