We can automatically email reminders from Excel with Power Automate and there’s no programming required. You don’t even need to open the Excel file to trigger the emails to send! In the video and tutorial below we’ll look at a scenario where we have a list of tasks, and we want to automatically remind those responsible prior to the task due date.
Note: Requires Microsoft 365 and either OneDrive for Business or SharePoint.
Watch the Video
Automatically Email Reminders from Excel with Power Automate Step by Step
Below is a screenshot of my task list which is in an Excel table called Tasks:
The reminder date is a set number of days before the task due date. I want to send an email to the manager containing a list of their tasks when the reminder date falls due.
Launch Power Automate and Schedule Flow
Step 1: Your data must be in an Excel Table. Use the keyboard shortcut CTRL+T to format your data in a table or go to the Insert tab > Table.
Step 2: Save your Excel file on OneDrive for Business or SharePoint. Note: it doesn’t work with OneDrive Personal accounts, and you need a Microsoft 365 account.
Step 3: Login to OneDrive or SharePoint in your browser and navigate to Power Automate. Tip: if you don’t see Power Automate in the list of apps, click on ‘All apps’:
Step 4: click on ‘Create’ and then choose ‘Scheduled cloud flow’:
Step 5: Name your flow and set the schedule. I want the reminders to be sent out daily:
Step 6: You’ll now have one step in your flow for ‘Recurrence’:
Connect to Excel File with Power Automate
Click on + New step and choose ‘List rows present in a table’. Tip: if you don’t see this option in the list, you can filter it by clicking on the ‘Excel Online (Business)’ icon first.
Step 7: Choose the location OneDrive for Business or SharePoint:
If you choose OneDrive, then the Document Library is also OneDrive. If you choose SharePoint, then the Document Library field is automatically populated for you.
Next, navigate to the file location and select the file. Note: there is a limit to the number of folders that will display in this list, so you may need to create a folder that appears near the top.
Lastly, select the table name containing your data. Remember, mine is called Tasks:
Step 8: Open the Advanced Options and add an ODATA filter for the ReminderDate field. Start by typing in the Filter Query field: ReminderDate eq ‘
Then click on ‘Add dynamic content’, go to the Expression tab and in the formula bar enter this formula:
formatDateTime(convertFromUTC(utcNow(), 'E. Australia Standard Time'), 'yyyy-MM-dd')
Note: You can edit the time zone to your own using the time zones names listed here.
Lastly, set the DateTime format to ISO 8601:
IMPORTANT: Note the expression in the filter query is surrounded by single quotes.
Extract Distinct List of Emails
Step 9: Next we need to get a distinct (unique) list of emails so that each person only gets one email. We do this by adding a select step. Click ‘Add Step’ and in the search box type ‘select’ and choose it from the list of actions:
Click on Add dynamic content and choose Value from the list:
Click on the Text icon:
And choose Email from the list. Tip: enter the column name in the search field to filter the list:
Step 10: Add a new step to compose a list of distinct emails. Tip: type ‘compose’ in the search filed to reduce the list of actions:
Click on ‘Add dynamic content’ and go to the Expression tab and enter union()
Then click on the Dynamic Content tab to the left of the Expression tab and select ‘output’ from the list, then enter a comma and select ‘output’ again. You should have a formula like this:
Click OK to complete the formula for Compose.
Step 11: add a control
And select ‘apply to each’:
Click on ‘Add dynamic content’ and select ‘Outputs’ from the Compose step:
Extract Data for Email from Each Row
Step 12: Add an action:
To Filter array:
Click on ‘Add dynamic content’ and choose ‘Value’:
In the next fields choose ‘Add dynamic content’ and select ‘Email’ and ‘Current item’ as per the screenshot below:
Generate HTML for Email
Step 13: Add an action to create a HTML table:
In the From field enter dynamic content from the Filter array body:
Then ‘Show advanced options’ and change the Columns to Custom:
Enter names for the columns that you want in your table names in the Header column. And in the Value column enter expressions for the column names as they appear in the table using the item function like so:
Note that date fields need to be wrapped in the formatDateTime function otherwise they will appear as a date serial number in the email.
Step 14: Add an action for sending the email. I’m using Office 365 Outlook:
Choose Current Item from Dynamic content.
Add a subject and then add dynamic content for the body by inserting the Output of the Create HTML table action:
Your completed flow should look like this: