Hi @Karolina17 ,
I don't know exactly how is your Excel table organized, but you must need (1) a column with unique values (like an ID column) and (2) a column to identify if the email was already sent by the flow. Here is a quick step-by-step about how to implement the flow.
References
Before we start, I'm sharing some articles about the topics discussed in this solution. It can be useful for you or any users that find this thread in the future:
- Dynamic contents and expressions in Power Automate: http://digitalmill.net/2023/07/31/using-dynamic-content-in-power-automate/
- Looping arrays: http://digitalmill.net/2023/07/12/using-loops-and-accessing-array-elements-in-power-automate/
- Integrating Power Automate with Excel tables: http://digitalmill.net/2023/07/17/integrating-power-automate-and-excel-tables/
- Inserting and updating data in Excel tables with Power Automate: http://digitalmill.net/2023/07/24/inserting-and-updating-data-into-excel-tables-with-power-automate/
Overview
This is how my table looks like: there is an ID column, that is used to identify the records; an email column, that must be used by the flow to populate the "Send email" action; and a "sent" column, that identifies if the email related to this record was already sent or not:
The "sent" column must not be fille by the user: the flow will automatically populate it once it sends the email.
Step 1 - Configuring the trigger
Let's start by creating a flow with the "When an item or file is modified", from SharePoint connector (you can also use it from OneDrive connector, if your file is stored in OneDrive):
Fill in with the details about your file's location (SharePoint Site, Library and folder). You may notice that there is no option to select the individual file, but only a folder to be monitored. This is expected, and technically this trigger is capturing the modification event of any file located into the folder selected by you.
To ensure that the trigger will monitor only a specific file, you will need to add a trigger condition. In your trigger, select the "Settings" tab (highlighted in yellow) and add a trigger condition (highlighted in green):
The condition must be the following: @equals(triggerBody()?['{FilenameWithExtension}'], 'emails to send.xlsx'), where you must to replace the text in red for the name with extension of the Excel file where your table is stored.
Although the trigger is monitoring changes happening in any file allocated in the folder selected by you, the flow will be triggered only if a change is made in your Excel file.
Step 2 - Capturing the new table records
Now it's time to add a "List rows present in a table" action. Populate it with the details of your flow and add a filter query to capture only the records where the column "sent" is empty:
The filter query text is sent eq '', and you must replace the text in red for the name of the column that you are using to register that an email was sent to the record.
This control is important to ensure that you won't be sending emails to old records. By default, Power Automate will retrieve all records from the table, and it won't be able to identify the new records included during the recent file changes. Once you insert a column to perform this control, Power Automate will be able to filter out all of the records that already had an email sent in the past (by using the filter query from above), and retrieving only the new records added to the table.
Step 3 - Sending the email
Power Automate returned only the new records added to the table, so you can loop them all with a "For each" or "Apply to each" and add your email action inside it. For this example, I'm using a "Compose" action just to display the email of the current loop iteration, but you can replace it for a "Send email" and populate the "To" input with the dynamic content of "email":
Also, you must allocate an "Update a row" action inside the loop, to register in the table whenever the email is sent to the user. Just populate the action with the table details, set the "id" as "Key column" (highlighted in yellow), set the "id" dynamic content as "Key value" (highlighted in blue) and set the "sent" column as "Yes" (highlighted in green):
Testing the flow
The flow is complete. I've included a new row into the table for testing, without filling the "sent" column:
As expected, the flow ran and identified retrieved only one record from Excel (highlighted in yellow), which email is test1@test.com (highlighted in green):
And after that, the flow automatically updated the "sent" column to "Yes":
Let me know if it works for you or if you need any additional help!
-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.
http://digitalmill.net/
https://www.linkedin.com/in/raphael-haus-zaneti/
