In today’s tutorial, I will show you on how you can do use power automate to send email. 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 or macros or any external add-ins. Guess what we are using.
- Did you guess it 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.
- The 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.
- They don’t be a PDF file, 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 for business. The contract for Kim West has her name and her address. Then for 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 together 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. First step is to log into 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. Now have to decide what type of flow do 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, notice I have for a selected row as an option. That’s the one I want.
- Now, before click 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 in OneDrive for business.
- Document library is 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 the list of all tables in my Excel file. I just have one table so I’m going to select that.
- Now, bring up the file on Excel. It’s formatted as an Excel table. That’s called table files. So far, looks good.
Setup a Loop to get files in a folder
Now comes the part that we need to think about the next step. 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 are equal to the file name 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 one drive related.
- Click on New Step. Search for one drive. It’s OneDrive for business.
- 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 where my PDF files are sitting in the contracts folder. So far, so good.
- Now is the time where we can 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 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. If I select scroll down under dynamic content, I can see 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 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. With choose a value, I’m going to go with Display name, because that way, it’s identical to the way written it 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 that file content that was matched. I’m going to get the file content, and this one is One drive 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.
- 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 subject, this is something you can make dynamic content as well. If I had a separate column in my Excel file that was different for each person, I can use dynamic content. In this case, I don’t. 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 high and add dynamic content because I have the name of the person in my Excel file. Select first name, and add some text. We can add dynamic content. This time the dynamic content comes from one drive. Let’s go with name without 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 in case you need to. In this case, I don’t.
- You can also update the reply to email and add yourself on CC if you want or other people. You can also update the from email address in case you need to.
- All of this part 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 on the top.
Run Flow to Send Emails With PDF Attachments
- Now, we are ready to test it. Go to OneDrive online and open our excel file. I will close the offline version so we are not two people working in the same file. I just have the online version open to run my flow. I’m going to go to the data tab on 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 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 you have to have permissions. I’m going to click on continue.
- What would 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 that 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 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 on their mailbox.
- We can go back to power automate and check our flow run. 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 that 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 learnt on how to use power automate to send email. If you have found it useful, let me know in the comments. If you’re using power automate or not and 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.
