Consume Multiple excel files and send a report for each to a given email address define in each file

[RobA1]

Hi,

I have a Power Automate process that splits one Excel file into multiple based on a value in a cell. This had to be done cause the original file was too large to do anything with.  Now I have multiple Excel Files in OneDrive that I need to consume and send a report for each file to only one instance of an email address defined in each file. 

I am trying to read in all excel files in the directory (Florida.xlsx, Georgia. xlsx, New York.xlsx) and send one email to each Indvidual (steve, lisa, rob) in the report. 

 I have a flow that I use for another process (below) that pulls in one excel file and sends one email to an instance of an email address defined in a cell. I just don't know how to add/edit to the flow to consume all files and send to one instance of the email address in each file.  Each row in the file will have the same email address but different from each file. Hope this makes sense. Thank you!

[Rhiassuring]

So Florida ALWAYS goes to Steve, New York ALWAYS goes to Rob, Georgia ALWAYS goes to Lisa? 

Is there one of these for every state? Or is it just the three? (Just in case this is just an example.) 

Honestly there's a few ways that could work if it's always the same. You could make a list to use as a matrix - ie, State | Email, and just grab that for the file. Or, you could just access Row 1 only to grab the email and set it to a single variable.

Are you trying to perform any filtering or operations on the data in the Excel file BEFORE it goes to the user?

Are you building a table for them to show in the email? 

In your example flow it looks like the answer is "yes" but it's hard to tell what's happening, as it looks like you're creating and selecting with your HTML table before cleaning it.

[RobA1]

Thank you for your response.  So on a monthly basis there could be any amount from 1 to 50 files. In each file the email address will always be the same. Florida will always be Steve, New York goes to Rob, Georgia goes to Lisa.  I could potentially have a file called Texas that has multiple email addresses, but they will always be the same every time a Texas file is produced. 

I do filter and create an HTML table before sending via email. To clarify, I am not sending the Excel file as an attachment but creating and formatting an HTML Table .

This flow here works great to pull in 1 file, and sends only one email to the owner rather than sending an email for every row that same owner is defined. In this example, the Florida Report is sent once as an HTML report to steve .

I would like to edit this Flow if possible to consume all the files within a directory and send a report for each file to it's appropriate owner defined in the file. Keep in mind, each row in a file has the owner, so I want to avoid sending a report for each row instead of file. Again the below flow works great for one file, just need to consume multiple files which can't figure out how to do with this flow. 

Your help is much appreciated...

[RobA1]

Ugggg! Just made a really simple change and the whole thing works. I basically changed it to and automated flow. So as each file is created it kicks off the flow in my example. Not sure why I made this so complicated. This is what happens when you stare at something to long 😂.    

One more question, how do I skip a file if the email field is blank? Thank you!

[Rhiassuring]

You have files where the email will be blank? Do you mean certain rows will be blank?

When you "List Rows Present in Table", right after that, you can use the Filter Array to get "Email" not equal Blank. https://powerusers.microsoft.com/t5/Building-Flows/Filter-Query-ne-null-doesnt-work/td-p/253467