Consolidated email reporting based on Excel Online for multiple rows

[astrodileepa]

Hello Power Users ! 

I'm new to PowerAutomate and I have been building a small flow to send out some email notifications to my customers regarding their invoices that are currently overdue. I have managed to get done the trigger but the action of creating a consolidated email is where I have stuck almost a week now.

Following is a sample format of my Excell Online sheet with customer invoice data :

The consolidated emails should be sent out in the following format as an action :

Can anybody help me to create the action to send out the consolidated emails as the two examples? Much appreciated !

Thank you 🙂

[abm]

Hi @astrodileepa 

Is your emails are fixed list? If it is then add all emails in an array. Then iterate each array item (each email) filter against Excel so that you can get relevant rows based on email and send email to specified user.

Thanks

[efialttes]

Hi!

Consolidated email reporting is explained here

https://powerusers.microsoft.com/t5/Building-Flows/Consolidated-email-based-on-date-in-excel/m-p/471...

Hope this helps

[astrodileepa]

@abm  yes every customer has fixed email - and they have multiple rows of invoice details.

[abm]

Hi @astrodileepa 

Did you tried @efialttes  solution?

If you know all the customers email address before hand then declare an array and store all the emails. Then iterate the array using a foreach loop and filter the excel rows against the each email then send the email.

See this blog how to filter Excel Rows.

https://mydevexperience.wordpress.com/2019/09/25/microsoft-flow-excel-list-rows-present-in-a-table/

If you need any help in this please let me know.

Thanks

[astrodileepa]

Had done as above - it works to send the email directly to the customer but as you can see in my original post I need to CC (carbon copy)  the same email to the cashier email as well. How can I do that? 

[abm]

Hi @astrodileepa 

Do you want to do a screenshare? I could look into this. Send me your details by email.

Thanks

[abm · Accepted solution]

Hi @astrodileepa 

Thanks for the screen share.

You were almost there with @efialttes  suggestion but to get the cashier email from the Filter Array you need the following expression.

body('FilterArray')[0]Item('cashier_email')

All the cashier email are same for every customer so you can select this by array first index 0.

Hope it was a useful session.

Thanks

[astrodileepa · Accepted solution]

@abm Thanks so much for the screen share and supporting me to figure out the point where I was stuck and @efialttes  Thank you so much for the support it really helped a lot 🙂 

Posting the entire correct flow if someone need to understand it. 

[efialttes]

@astrodileepa 

I've also marked your last reply as Solution -more than one reply can by tagged as solution even from different Community members-, since I agree with you it will help others. Thanx for sharing!

[shabbirturra]

Hi @astrodileepa 

I have a similar requirement as yours and i am trying to replicate the steps shared by you. But getting stuck at creating a unique list. Can you or anyone else assist me with this.

I am pretty new with flows. Have been using VBA for much of the automation and this is my first time with MS Flow.

My data is having an "Email" column which i want to capture unique values.

I have doubt that my select comand and then unique email command (Union syntax) is not correct. Can you assist by providing step by step guide to use select and compose unique email id.

Thanks

Shabbir