Reading an excel table, and sending a single email with multiple rows to people that meet a criteria

[FSamba]

Hello,

I hope you are all well.

I am trying to build a Flow that scans an excel document. The document contains a list of document names and owners (emails). The purpose of the document is to scan and identify who has a document review due soon (highlighted in the document with a column, “Y” for due soon and “N” for not due soon). It will then send out an email to anyone with a review soon with a list of all the docs they have due soon.

There are two main challenges here:
- To scan the excel document and identify who has a document review due soon (indicated by a column with ‘Y’ and ‘N’). Emails should be sent out to only these people and nobody else.

- To send out only one email to anyone with a review due soon. The email will contain a table with all of the documents that are due for review. I want to avoid sending out a single email for every single document due for review, so the table will provide all of the info.

I am trying to follow the steps outlined in this link below:

https://powerusers.microsoft.com/t5/Building-Flows/Send-single-email-to-users-with-more-than-one-row...

This allows me to do a lot of what I want to do, but not all of it, primarily sending emails only to people who have a document due soon. I am very new to Automate. Does anyone have any suggestions?

Thanks so much in advance!

Kind regards,
Adam

[ArchitectMadhan]

Hi @FSamba ,

You have to list down things first, as shown below. That itself solves half of the issues.

1. Use schedule flow, it has to be scheduled to run daily.

2. Filter the Excel document for the filenames and email that has "Y" in the Due Soon field.

@not(equals(item()?['DueSoon'], 'Y')). To avoid fetching unwanted rows from excel. Also use order by on the email field.

3. Store the result in Two Arrays, 1st for storing Document names, 2nd for storing email addresses.

4. Loop thru the array of document names and check for the email address is equal to previous email address(have a variable to store the email address), If yes then add the document to another array named user docs (to group the docs belong to single email user) else send email with the list of document names to the email address in the variable and clear the user docs array. That's it.

Now you need to find the actions that matches every stage.

If you feel it helps you to find solution for your issue, then please accept it as solution.

[FSamba]

Thank you so much for the help!

Unfortunately I’m that new to Power Automate to the point where I don’t understand some of your message. I’m not quite sure how to filter the document (I understand I can use a custom expression that you highlighted, but not where or how I can put it in or what action is needed). I’m also unsure on how to store anything into arrays.

Would you be able able to advise me on this further? Any help would be very very appreciated!

Thanks again,

FSamba