cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FSamba
Frequent Visitor

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

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

 

2 REPLIES 2
ArchitectMadhan
Super User
Super User

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.

- Architect Madhan

Please click the "Accept as Solution" button and give thumbs up, if it is helpful to you.

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

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (1,144)