“I have a Power Automate flow checking date in Excel file and sending reminders, can I improve it to send only a single email with multiple rows?”
When you send reminders using Power Automate flows, you’re doing it to help the users. They don’t have to think about dates, a flow will check what has to be done and gently remind them. Users will be happy, processes will finish in time… Unless you send them too many emails. If you stick with the basic reminders, you’ll probably make the users angry instead. They don’t want an email for each row, leading to tens of emails every day. You should always put as much as possible in a single email, all the items/rows at once.
I already wrote similar post based on a SharePoint list, this one will use data stored in an Excel file. As every other reminder flow, it has a Recurrence trigger starting the flow once a day.
Get the email recipients
The first step when sending a single email is to get all the recipients. At the beginning you don’t care about duplicates, just list all the rows from the Excel file with the desired filter, e.g. on a date.
From the rows, ‘Select’ only the column with the email addresses. In my Excel file it’s the ‘Assigned to’ column.
At this moment you don’t care about any other columns, just the column with the recipients.
It’ll return an array with all the email addresses in all the rows – lots of duplicates. Use the union(…) expression to keep only the unique values, each email only once.
In my example it’s just 3 email recipients in the end.
Send their rows in a single email
Now, when you’ve got all the recipients, it’s time to search for their rows. Loop through the unique recipients and filter only the rows where their email is in the email column.
Take the rows and turn them into an HTML table, that way it’ll be much easier to read for the recipients. You can add also some colours to the table as explained in previous posts.
Important note: you’re referencing output of the ‘Filter array’ action in the ‘Create HTML table’, don’t add dynamic content from the ‘List rows…’! If you do that it’ll add an extra loop and break the flow. You must reference the column directly using an expression:
item()?['ColumnName']
e.g.
item()?['Assigned to']
item()?['Name']
item()?['DueDate']
...or if it contains an apostrophe: item()?['Tom''s column']Once you prepare the HTML table send it to the recipient.
Note: it’s possible to apply some styling on the whole table too as explained by Paul.
Summary
There’re two parts to a Power Automate flow that’ll send a single email per person with multiple Excel rows. Firstly, you must identify the unique recipients. List all the rows that are important to you, select all the recipients, and reduce them to keep each user only once. Once you have the users you can check the recipients one by one, each time picking only the rows relevant to him. Send him a single email with a nice table and he’ll be much happier than if you kept sending him one email per row.
I have an excel sheet with several columns and several email recipients. I can’t figure out how to pull the columns I want with the custom HTML setup. The automatic HTML works but pull a bunch of stuff I don’t want.
Any help much appreciated.
Hi, I get the error: The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@outputs(‘Compose’)’ is of type ‘String’. The result must be a valid array.
Would anyone know how to solve this?