Recently, a user asked me how they could send emails using a flow, with Excel as the data source. I’m going to provide an in-depth guide that covers every step needed to accomplish this.
What’s needed to follow along:
Access to a Power Platform environment
URL: https://make.powerautomate.com/
OneDrive (but you can use SharePoint)
Excel file
Open a new Excel file and populate it with the following columns:
RowID, Employee Name, Manager Email, Email Sent
Enter data in each of the cells, but ensure that RowID has a unique value for each row.
After the data is entered, you will want to create a table encompassing the cells that were just populated. With one of the cells selected, click the Insert tab at the top of the file, then click Table.
When the Create Table popup opens, ensure it includes the rows and columns you created, and the my tables has headers box should be checked. Click Ok to close the popup.
From the File tab, click Save As and save the file to a OneDrive location. You can save the file to your desktop or another location, then copy it to a folder in OneDrive. It makes no difference how the file gets there, but for this flow to work, it needs to be in OneDrive or a SharePoint location you have access to.
From a browser, navigate to https://make.powerautomate.com/. On the left side of the screen, click on My flows. After the page refreshes, click + New flow and select Instant cloud flow.
In the Flow name field, input a name for your flow. From the Choose how to trigger this flow, select Manually trigger a flow, then click the Create button.
In the flow design canvans, click the + below the Manually trigger a flow action, and select Add an action.
When the Add an action window opens on the left side of the screen, you will notice that you have a bunch of actions to choose from. The first box can be used to search for actions; here, enter Excel list rows. Note how the actions are grouped by the connector type, in our case, Excel Online. The other key to note here is to see more blue text to the right of the Excel Online group. If you don’t see the action you are looking for, always remember to click the see more link. Doing this will disable all available actions for the group. Go ahead and click on the List rows present in a table action.
From the Location dropdown, select OneDrive if that’s where you saved your file; else, select SharePoint or wherever you saved the file. For this example, the file must be in a storage location to which the flow can connect. Next, click the dropdown for the Document Library. In my example, you’ll notice that I have several options to choose from; if you get the same result, you’ll need to select each option and then click the File dropdown to see if you are in the correct location. Yes, it’s annoying.
Once you have the correct Document Library selected, click the dropdown for the File option, navigate to where your Excel file is stored, and select it.
From the Table dropdown, select the available option. In my example, the only table from the Excel file is Table2. There is a good chance yours is named something else.
You can verify the table name by returning to your Excel file, clicking the Table Design tab, and then noting the Table Name value.
Returning to the flow design canvas, click the + Add an action below the Excel action.
From the search box, enter send email. With the list of actions narrowed down, select the Send an email (v2) option. If you are reading this in the future, the option might (V_something else), but make sure you are in the Office 365 Outlook group of action. Do NOT use the action from the Mail group.
In the Send an email action, click in the To field. If the dynamic content is not visible, click the Enter custom value text and the little lightning bolt icon.
When the dynamic content window opens, you will see a few options for fields you can select to populate the To value of the Send an email action. From the list of options, select Manager Email. This will pull in the manager’s email from the spreadsheet.
Click in the Subject field and enter some text; here, you will notice I input Example. After you’ve entered some text, click the lightning bolt icon again, but this time select Employee Name.
The last thing we will populate in this action is the Body of the email. Again, feel free to input some text here, and like the other fields, you can use values from the dynamic content menu to use values from the Excel file.
The completed email action will look like this.
You will notice that a For each was automatically added to the flow design canvas. Why? If you think about what the flow is doing from a process standpoint, it added the For each to loop over each row in the Excel file. For each row in the spreadsheet, do ____. In our example, it will send an email, and each email will reference the current item in the loop.
Below the Send an email action, click + Add an action.
From the add an action search box, enter excel update row, and select the Update a row action. We will use this action to update the spreadsheet for each email sent.
In the Update a row action, navigate to where your Excel file is stored. The steps you followed when connecting to the file a few steps back in this process will apply here. Once you’ve connected to the file, click in the Key Column and select RowID from the available choices.
Click into the Key Value field, open the dynamic content window, and select RowID. We are telling the action that we want to update the Excel file row corresponding to the current item in For each loop.
Click the dropdown for the Advanced parameters field and select Email Sent. I entered Yes in the Email Sent field.
The completed flow should look like this: We trigger the flow, get the table from the Excel file, loop over each row in the table (for each), send an email, and update the spreadsheet for each item in the loop.
At the top of the screen, click Save, then Test
Select Manually when the next window opens, click Continue, and last but not least, click Run flow.
When the flow is finished running, you should see green check marks next to each flow action.
The emails were sent to each manager with their employee in the subject and body of each email. To test sending emails, I like to use https://temp-mail.org/en/
Navigating back to the Excel file, the Email sent value is Yes for each row in the file.
That’s it! There are lots of steps, but I hope it covers everything you need to create a workflow that does the exact same thing.