In today’s tutorial, I will show you how you can use Power Automate to send emails. We are going to be sending personalized emails to multiple people with their own attachments and we’re going to do this from Excel. We are not going to be using any VBA, macros, or external add-ins. Guess what we are using.
- Did you guess Power Automate? In the excel file, I have a table with each person’s first name, last name, email, and the file name that I want to send them. In this case, it’s just a name, not the address of the file.
- 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.
- They don’t have to be PDF files; they can be on SharePoint. They can be in another folder on your OneDrive. All of these, in my case, are saved in my OneDrive. The contract for Kim West has her name and her address. Then Lucas has his name and his address.
- What is going to happen is that our flow is going to send each person a customized email message with their own attachments. All we have to do is select and click.
Create instant cloud flow in Power Automate
- Now, let’s set this up together. The first step is to log into the office portal, sign into your account, and then scroll down on the left side and select Power Automate.
- In this case, I want to create a flow from scratch. I’m going to click on the Create button on the left side. I now have to decide what type of flow I want to create.
- 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, I notice I have a selected row as an option. That’s the one I want.
- Now, before clicking on create, give this flow a name and click on create.
- That comes the easy part. I just have to select the location of my Excel file. Mine is on OneDrive.
- The document library is on one drive.
- Select the file and click on the folder icon to find it.
- Now comes the table. Well, the moment I click on the drop down, I get a list of all the tables in my Excel file. I just have one table so I’m going to select that.
- Now, bring up the file in Excel. It’s formatted as an Excel table. That’s called table files. So far, it looks good.
Setup a loop to get files in a folder
Now comes the part where we need to think about the next step. How do we set this up? What I need to do is somehow loop through all the files that exist in a folder, check the names of the files, and see if they are equal to the file names that I have specified. If they are the same, it should grab that file and send it as an attachment. This means I need the ability to loop through files in a folder. That’s an action. That’s OneDrive related.
- Click on New Step. Search for one drive. It’s OneDrive.
- Take a look at the different actions we take. One action is to list files in a folder. That’s the one we need. This is going to give us an array of information about the files that we have in the folder.
- All I have to do is select the folder where my PDF files are sitting in the contracts folder. So far, so good.
- Now is the time to move 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 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. If I scroll down under dynamic content, I can see a value that’s going to do the job. Let’s select that.
- Next, click “Add an action” to add another control.
- This time, it’s a condition.
- Why condition? Because I need to cross-check the name that I have in my Excel file.
- This is dynamic content. If I scroll down, I can see the dynamic content connected to my Excel file and see the file name that says File name (formatted). Let’s go with that.
- Next is equal to, which is fine, but you have different options in case you need them. What is it equal to? Well, it’s a dynamic content based array.
- Of course, I have to make sure that I’m checking the same type of file names. When choosing a value, I’m going to go with Display name, because that way, it’s identical to the way it’s written in Excel. 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, select “Add an action.”
- I need to actually grab the file content that was matched. I’m going to get the file content, and this one is OneDrive related as well.
- Let’s just restrict this to the actions we have. Select “Get file content.”
- I need the unique identifier of the file. If I click Insight under Dynamic Content, I can see the ID is the unique identifier of the file. That’s the one I need to go with.
- Next, click “Add an action.”
Prepare a custom email with dynamic content
- This is Outlook related so select “Office 365 Outlook.”
- Under actions, select “Send an email (V2).”
- I can specify to whom I want to send an email. Of course, this is dynamic content and it’s something I have in my Excel file. Click on “Add dynamic content.”
- Select “email (Formatted)“
- Now for the subject, this is something you can make dynamic content for as well. If I had a separate column in my Excel file that was different for each person, I could use dynamic content. In this case, I don’t. I’m just going to put your contract.
- You can type in whatever you need in the body. I’m going to go with high and add dynamic content because I have the name of the person in my Excel file. Select the first name, and add some text. We can add dynamic content. This time, the dynamic content comes from one drive. Let’s go with a name without an extension.
- We have the content, but we don’t have the attachment. Click show advanced options. Select attachment content. That’s dynamic content as well. It’s file content. You can add more attachments if you need to. In this case, I don’t.
- You can also update the reply to the email and add yourself to the CC list if you want, or other people. You can also update the email address in case you need to.
- All of this is set up. If there isn’t a match between the file that is present in the folder and the file name available in the Excel file, nothing is going to happen.
- Let’s save this at the top.
Run Flow to Send Emails With PDF Attachments
- We are ready to test it. Go to OneDrive online and open our Excel file. I will close the offline version so there are not two people working on the same file. I just have the online version open to run my flow. I’m going to go to the data tab and click on flow.
- In case you don’t see the flow, you need to add it. Go to the “Insert” tab and select “Office Add-ins.”
- Search for flow and add it. It’s a Microsoft add-in and it takes two seconds to get it added. You are going to find it in the data tab.
- In my case, I have already added it. Once I select it, I see the panel on the right side and it’s going to pick up any flows that are connected to the Excel file. Click on run.
- If it’s the first time you are running this, you have to be logged in and have permissions. I’m going to click on “Continue.”
- What would make it run well? If you have selected only one cell or table in the Excel file, it’s only going to run that row. If you want it to run for multiple rows, you can select them or just select them as you wish. In my case, I want to run it for everything, so I’m going to select everything.
- Click on run flow. It tells me your flow run has successfully started.
- We can monitor it from the flow runs page so click on done now. Before we check our flow run, make sure everything worked so check if one of the recipients received the email that says your contract in their mailbox.
- We can go back to Power Automate and check our flow. Click on my flows on the left side to view the status. I can see information about my flow and all my flow runs succeeded.
- That’s a super easy way to send customized attachments to different people directly from your Excel file. That’s the power of Power Automate. It allows us to do tasks for which we needed to use external add-ins. For now, we can create those add-ins ourselves as we need them.
Also read: How to Send Emails with Attachment from PowerShell?
That wraps up today’s tutorial. I hope you have learned how to use Power Automate to send emails. If you have found it useful, let me know in the comments. If you’re using Power Automate or not, give us some examples to encourage us to give us ideas. 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?
