When a new row is added send email to person

[Karolina17]

I am new to using Power Automate. I have a problem with creating a flow. I will briefly describe what I would like to do.

When a new row appear in an excel file, I would like it to automatically send an email with the file as an attachment to the person whose email address is given in this new row.

[rzaneti]

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/ 

[Karolina17]

Hi, thank you for your help. I have a problem with the fact that messages are sent several times, I added a condition but it doesn't work.

[rzaneti]

Hi @Karolina17 ,

What do you have as input for the condition?

[Karolina17]

I have this as input

[rzaneti]

Hi @Karolina17 ,

As you are using the condition to filter out the records where you already have a "TAK" in the "sent" column, make sure that the "TAK" in Excel is in upper case (Power Automate will be case sensitive for this comparison), and without any extra blanks at the beginning or end of the text.

Alternatively, you can use an expression to test if the "sent" column is empty and, if yes, send the email and run the update action. This works fine for any text added to "sent" column, ignoring the letters case and the extra blanks:

Expression: empty(items('For_each')?['sent']), and make sure to change the items('For_each')?['sent'] reference to your actual "sent" column dynamic content.  

Note: by applying this change, make sure to move your "Send email" and "Update row" actions to the "True" block of the Condition.

[Karolina17]

@rzaneti I added this condition, but after 1 minute I sent the same message again with this flow.

[rzaneti]

Hi @Karolina17 ,

The Excel "List rows present in a table" requires some minutes to capture file updates, so when you update a record in an run the flow after one minute, it is possible that it is using data from the previous version (in your case, without any information in the "sent" column. 

Some questions:

- Are you facing the same issue when you wait 5 minutes between two flow runs?

- If yes, is your Excel table "sent" column updated after running the flow?

- If not, please, share an image of your Excel table

[takolota]

If you wanted an automated trigger, then there is also this When an Excel row is created, modified, or deleted template: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/When-an-Excel-row-is-created-modified-or...

[Karolina17]

Hi @rzaneti,

-After 1 test, flow sent me 2 messages as if it had started 2 times.

-After 5 minutes I entered another adres email into the file, the flow started 2 times but sent 1 message.

- Yes, my "sent" column updated after running the flow

- I have also another question. In this file now I will have 3 tables. I would like this flow to start when the change occurs in first "1" sheet. Is it possible to add?

[rzaneti · Accepted solution]

Hi @Karolina17 ,

About the duplicated call, my guess is that you are getting one for the new row insertion (which is expected), and another for Power Automate "Update a row" task, which is modifying the file to include the "TAK" into the "sent" column. Ideally, this second flow run should not result in a new email sending, as we won't have any empty values in "sent" column, but it is possible to happen, as this second flow run can happen in less than 5 minutes from the file update, not capturing its changes. 

A possible solution may be to change the "interval" property from your trigger, setting it to 5 minutes:

This property controls how often Power Automate will monitor the changes in the SharePoint folder. By setting it to a larger amount of minutes, you must prevent Power Automate to capture the old Excel file version.

About limiting the flow run to changes in only one sheet, I don't think that it may be possible, as the trigger is related to a SharePoint control, and not to Excel itself.

Let me know if the recommended change works for you!

[KVIDP]

Hi @rzaneti 

I have made a similar flow as your instruction. The flow works. However I'm stuck with this.

The name Robert, it keeps sending mail repeatedly till I have to turn the flow off. In the column Status, it doesn't fill 'Sent' into my excel file as the action Update a row set.

In addition, why it doesn't display sheet name as in the excel file in the Table field, just 'Table3' [highlighted].

Could you please give me instruction to fix these problem?

[rzaneti]

Hi @KVIDP ,

It looks like Robert's candidate number is the same of Alex's, when it should be different. When running the "Update a row" action, Power Automate will search for the first match in the table, which is Alex, letting Robert's status as empty.

If your table is just an example, I recommend you to change the Alex candidate number and try to run it again. If the candidate number column is supposed to receive duplicated values, I recommend you to use a different column as key (maybe email address).

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/ 

[KVIDP]

Hi @rzaneti ,

I've change the candidate number. It worked but I still received email multiple times (one mail per a minute) before it stop. This time, 'Sent' was automatically fill in the column Status. It supposed to send only one email and mark 'Sent' to the table, no repeatedly sending mail. You have any advice for me?

[rzaneti]

Hi @KVIDP ,

Can you confirm the "Interval" used in the trigger? by default, it is set as 1 minute, but Excel files can take up to 5 minutes to be updated. It means that SharePoint captures a change in your Excel file (triggering the flow again), but Excel didn't register the change yet (set the column as "Sent"), resulting in these multiple email sending. 

I recommend you to set this value to 5 minutes, so SharePoint will wait a little while to look for changes in the file (and, once it does it, the "Sent" column will be already filled in Excel):

Let me know if it works!

[KVIDP]

Hi @rzaneti ,

Actually I've already set 5 minutes interval. Should I increase up to 10 minutes interval?

[rzaneti]

Hi @KVIDP ,

In this case, I recommend you to increase it to 7 or 10, if your requirements allow it. If the error persists, please share some images of your flow design. 

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/ 

[KVIDP]

Hi @rzaneti ,

I have tried setting the interval to 10 minutes and 15 minutes, but the error still persists, mail was sent least 3 times. I noticed that the 'Sent' status is filled in the Status column almost immediately. However, the email is still sent multiple times. In addition, I also noticed that after changing the interval to 10 minutes or 15 minutes and clicking Save, then running a test, after the test is finished, when I click Edit, the Interval is automatically adjusted back to 5 minutes. It's really confusing.