Help extracting unique emails from returned data and using them in the Flow

[P_Molitor]

I created a scheduled Flow that gets items from a list that have a "Complete" field of "NO" then places that in to an HTML table and emails the information. The email sent looks something like this.

The following Move Slips are awaiting completion and may be assigned to you.

Name	Submitted	Effective	MoveID	Responsible_Party
Anne Droyd	2022-02-01	2022-02-21	4	Responsible@email
Jacque Strappe	2022-02-01	2022-02-21	5	Responsible@email

Please follow this link and complete your move slips using the correct Move ID.
https://forms.office.com/r/BLAH-blah-BLAH  

The Flow looks like this:

I was hoping to have the table sent to the responsible parties and cc'd to me but I'm not sure how to make that happen. If I do an apply to each on the email action, if the same person is responsible for multiple entries then they will get multiple emails, as will I being cc'd. Is there a way I can take the info from the "Responsible Party" column, filter it for unique emails, then have that output as a string of emails seperated by ";" so that I can have that in the email TO: field as dynamic data so that only one email goes out? (at some point there might be 10-15 entries in this table with multiple duplicates for "Responsible Party")

Thanks in advance!

[DamoBird365]

HI @P_Molitor 

You can do this with a select action.

From Value from Excel

Put the select into Text mode and insert the Responsible party.

This gives you an array of all emails.  Next you want to get distinct emails.

Using a compose, use the union expression Union(outputfromselect, outputfromselect).

You now have your array that you can supply to the apply to each and then filter your original data set and send emails to the unique emails.

A similar q was asked here Re: How to send two different tables in one email ... - Power Platform Community (microsoft.com

albeit they already had the distinct emails.  My quick video will show you how to complete the rest of your solution if need be.

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

[P_Molitor]

I'll give this a try at some point today substituting sharepoint list functions where necessary. Watching the video, I was a little confused where the Send Email was in the Apply To Each using the [Email ID] from the List Rows action as the recipient, but I'm sure I just don't fully understand how the Apply To Each function operates.

[DamoBird365]

Hi @P_Molitor 

If you need more detail or get stuck, give me a shout.

Damien

[DamoBird365 · Accepted solution]

Hi @P_Molitor 

I've updated the video to include a demo of how to get distinct email addresses from an array (which can be an Excel Table or Microsoft List or any other array of data to be honest).  This should take you straight to the point in the video Send Unique Emails with HTML Table using Data from Excel #PowerAutomate - YouTube

This then negates the needs for table one in the post I referred to earlier, you can get distinct emails and via the apply to each send an email of only the data relevant to the responsible party.  Hope I have understood you correctly.

Damien

[P_Molitor]

Awesome! I tried it earlier and built it wrong, but I think the video has me almost there. I'm using a sharepoint list instead of excel, so it looks a little different. Here is what I have now (I've appended this to the bottom of my current flow so as to not change the earlier less featured version that was working).

This is where things are getting messed up. In your video, your Email To: field is [Email_ID] which I believe corresponds to my [New Ko-Cho] (this is the output of my (Get items) action that corresponds to the column with the emails in it); however, when I place [New Ko-Cho] here, it automatically puts my (Send an email) action in to its own (Apply to each) action where as yours remains as part of the larger (Apply to each) action. I'm sure I've done something wrong, just not sure what. Thanks!

[DamoBird365]

@P_Molitor

I know what it is, and it caught me out. I know you didn’t watch the playback of the flow because I too made that mistake 😂. Sorry. 

The email address is current item. This is because the input to the apply to each is a simple array of emails. For each loop we use that email in the filter and to then send the email. 

The rest looks great.

Damien

[P_Molitor]

HA! I literally had it paused while the Apply to each was in operation. My test after making that change seemed to work, but I only had one unique email after applying my earlier OData filter query, so I'll have to finagle my list so that it returns multiple emails for the (Apply to each) action to process in order to fully verify operation. Assuming that works, I'll be back here to accept your solution. Thanks for all your help! The video definitely made things easier.