In today's tutorial, I'll show you how you
can do a mail merge with attachments. So, we're going to be sending personalized emails
to multiple people with their own attachments, and we're going to do this from Excel. We're
not going to be using any VBA or macros or any external add-ins. Guess
what we're using? Did you guess it? Power Automate. Okay, so let
me take you through what we're going to be doing here. I have an Excel file called "FilesMaster".
In this file, I have a table with each person's first name, last name, email, and the file name
that I want to send them. So, in this case, it's just the name, not the address of the file.
Now notice each person needs to get their own customized PDF document. These PDFs happen to be
in the same folder where I have my Excel file, but they don't have to be. They can be on
SharePoint. They can be in another folder on your OneDrive. So notice all of these, in my case,
are saved in my OneDrive for Business for now. Let me show you how the PDFs look. The contract
for Kim West has her name and her address. The one for Lucas has his name and his address. If you're
curious how you can create multiple PDF files from Word, I also have a separate video on that,
check it out if you're interested. In this case, what we want to do is to go to Excel to select
the rows for the people to which we want to send our file to and then run our flow. What's going
to happen is that our flow is going to send each person a customized email message together
with their own attachments. All we have to do is select and click. Now let's set this up
together. First step is to log into office.com, sign in to your account, and then scroll down
and go to Power Automate. Now, in this case, I want to create a flow from scratch, so I'm
going to click on the create button on the side. Now I have to decide what type of flow do
I want to create? What's the trigger? Well, because my trigger is from an Excel file,
and it's based on the rows that I select. That falls under an instant cloud flow because if
I scroll down here, notice I have 'For a selected row" as an option. That's the one I want. Now
before I click on Create, let's give this flow a name and click on Create. Now comes the easy
part. I just have to select the location of my Excel file. Mine is in OneDrive for Business,
document library is OneDrive, the file itself, just click on the folder icon and click on these
arrows until you find it. Mine is right here. Now comes the table. Well, the moment I click on
the drop-down, I get the list of all tables in my Excel file. I just have one table, so I'm going to
select that. Now just to show this to you, let's bring up Excel. That's my data, it's formatted as
an Excel table, that's called "TableFiles". Okay, so, so far, so good. Now comes the part that we
need to think about the next step. So, how do we set this up? What I need to happen is somehow to
loop through all the files that exist in a folder, check the name of the files, and see if they're
equal to the file name that I have specified here. If they are the same, it should grab that
file and send it as an attachment. So this means, I need the ability to loop through files in
a folder that's an action that's OneDrive related. So let's click on "New step", search for
"OneDrive" (it's "OneDrive for Business"), and take a look at the different actions we
have. One action is "List files in a folder", that's the one we need. This is going to give us
an array with information about the files that we have in the folder. All I have to do is select the
folder. So, this is the folder where my PDF files, in this case, are sitting in; they're sitting
in the "Contracts" folder. OK, so, so far, so good. Now is the time where we can loop through each
of the files in the folder and check whether they match the name of the file that we have
in our Excel row. This time, I need a control. That control is applied to each because I want
a loop. I need an output from my previous step, that's basically the array that I get here.
So if I scroll down under "Dynamic Content", I can see "Value", that's going to do the job,
so let's select that. Next is another control, this time, it's Condition. Why Condition? Because
I need to cross-check the name with the name that I have in my Excel file. So this time, I need to
pick this column here, which is called "File Name". In Power Automate, this is "Dynamic Content". If I
scroll down here, I can see the "Dynamic Content" connected to my Excel file and I see "File Name"
right here. So let's go with that. Next, "Is Equal To" is fine, but you have different options in
case you need them. What is it equal to? Well, it's a "Dynamic Content" based on this array
here and of course, I have to make sure that I'm checking the same type of file name, so I can't pick
"ID" or "Name" or "Name without extension". I'm going to go with "Display Name" because that way, it's
identical to the way I've written it in Excel. OK, so, so far, so good. That's my condition. Next, what do
I want to happen if that condition is met? Well, I want to send an email, but before I send an
email with my attachment, I need to actually grab that file content that was matched. So,
as my action, I'm going to get the file content and this one is OneDrive related as well. So, let's
just restrict this to the actions we have here. If I scroll down, I see "Get file content". I need
the unique identifier of the file. So if I click inside here, under "Dynamic Content", I can see
the "ID" is the unique identifier of the file. So that's the one I need to go with. Next step, now
we can send an email. This one is Outlook related, Outlook 365, and we have "Send an email". Here,
I can specify to whom I want to send this email. This of course is "Dynamic Content" and it's
something I have in my Excel file, "Email". Click on "Add Dynamic Content", scroll down
until we get to "Email". Now for subject, this is something you can make "Dynamic Content"
as well. So if I had a separate column in my Excel file that was different for each person,
I can use "Dynamic Content" here. In this case, I don't, so I'm just going to put "Your Contract".
Now, you can type in whatever you need in the body. I'm going to go with "Hi," and add dynamic content
because I have the name of the person in my Excel file, so let's go with first name, and then let's
just add some text, "Please find attached your file," and we can add dynamic content. This time, the
dynamic content comes from OneDrive. Let's go with "Name Without Extension," and then "Best
Regards, Leila." Now we have the email, we have the content, but we don't have the attachment.
We're going to find that under 'Advanced Options' for 'Attachment Name.' Well, that's simple. If
you scroll down, you're going to see "Name" here to select that. "Attachment Content," that's dynamic
content as well, it's "File Content." You can add more attachments in case you need to. In this
case, I don't. You can also update the 'Reply To' email. You can add yourself on CC if you want or
other people. You can also update the 'From' email address, in case you need to. Okay, so all of this
part is set up. In case there isn't a match between the file names, so basically, between the file
that I have in the folder and the file name I have in my Excel file, nothing is going to happen because
I didn't put anything here. Okay, so, so far, so good. Let's save this. Okay, so now, we're ready to
test this. Let's go to OneDrive online and open our Excel file online. Let me just quickly close
the offline version, so we're not two people working in the same file. I closed that off, now I
just have it open online. Okay, so to run my flow, I'm going to go to the 'Data' tab and click on
'Flow.' In case you don't see 'Flow' here, you need to add it in. Just go to the 'Insert'
tab here, go to 'Office Add-ins,' search for 'Flow' and add it. It's a Microsoft add-in and
takes two seconds to get this. Once you add it, you're going to find it in the 'Data' tab. So, in
my case, I've already added it. Once I select it, I see the pane on the side here, and it's going
to pick up any flows that are connected to this Excel file. This is my flow. I'm going to click on
'Run.' If it's the first time you're running this, you have to be logged in, you have to have
permissions. I'm just going to click on 'Continue' and run the flow. But, what would it run? Well,
because I just have one cell selected in my table, it's only going to run for this row. If you want
it to run for multiple rows, you can select them like this or just select them like this. In
my case, I want to run it for everything, so I'm going to select these and click on 'Run
Flow.' It tells me 'Your flow run successfully started.' We can monitor it from the 'Flow Runs'
page. So, click on 'Done.' Now, before we check our flow run, let's just make sure everything
worked. So, one of these emails is this one and they should have received "Contract_KimWest.pdf." Okay, so I'm going to open up Outlook and I can see your contract is here. "Hi Kim,
please find attach your file: Contract_KimWest. Best regards, Leila." That's the contract.
When I open it, it's their own custom pdf. Now, you can check your flow run either from
here or we can go back to Power Automate and check our flow run here. So, I'm just going to go
back. I can see information about my flow up here and I can see all my flow runs succeeded. Okay,
so that's a super easy way to send customized attachments to different people directly from
your Excel file. You see, that's the power of Power Automate. It allows us to do tasks that
we needed to use external add-ins for. Now, we can create those add-ins ourselves as we need them.
That wraps up today's tutorial. I hope you found it useful. Let me know in the comments if you're
using Power Automate or not. If you are using it, give us some examples to encourage us to
give us ideas. And if you aren't using it, can you see yourself using it? Are there tasks
or processes that you think you can use it for? Please do share. Thank you for
watching. Do consider subscribing in case you aren't subscribed already and
I'm going to see you in the next video.
