Email list from excel table

[Anonymous]

Hi All,

Im having the following issue.

I need to send an email out to a set list of people.

I have a list of email addresses in an excel table.

I can get the email address successfully from the table and insert them into the"To" section

The problem is : the email is being sent to each recipient , 6 times (The number of rows in the column)

Can anybody suggest a fix of using an excel table as an email address source ?

Current flow 

[edgonzales]

@Anonymous 

Hi there.  Check out this article, Group a List for a Summary Using Power Automate.  It might be enough to get you started.

Keep us posted.
-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[Anonymous]

Hi @edgonzales ,

Thank you for providing the link.

It seems a bit more elaborate than what im trying to achieve however and i'm having problems applying it to my situation.

I will always have a fixed table of unique emails.

I need to put the email addresses from a column in that table into the "to" of an email in flow.

Following the link got me this far:

I need this output but in a format without the "Eaddress": column header or other stuff , just the email address in a way i can use to send an email to each.

Adding a select trimmed down to just the column i need but have the extra stuff which is failing the flow

[Anonymous · Accepted solution]

Ok i think i got it,

It was super long and probably not the correct way but it works!

- Get Rows

-Filter to only show the column which i think displays as JSON ? im unsure of the terminology. In the filter leave the map blank and put the value as the desired column of email addresses (Dynamic object)

-Compose the output from the filter

-HTML to text the compose

-Compose the HTML to Text (I know it sounds stupid) You get left with = [{"":"firstemail@outlook.com"},{"":"secondemail@gmail.com"}]

-Compose this and use an expression to cut the beggining 6 characters so you're left with = firstemail@outlook.com"},{"":"secondemail@gmail.com"}]

Instructions here which i adjusted to cut 6 instead of 1 https://powerusers.microsoft.com/t5/General-Power-Automate/How-to-remove-first-character-from-a-stri...

-Initialise this as a string variable

-Trim the last 3 characters from that so you're left with = firstemail@outlook.com"},{"":"secondemail@gmail.com

-initialise this as a variable (i called mine mailsnip)

-initialise another variable called temp2 as a type string and leave it blank

-Create an invalid character array for  "},{"":"  these all in one ' ' quotation

-Create an apply to each with input invalid character array

-Set variable and use an expression to replave mailsnip invalid characters with a ';' (doesnt work correctly if you quote each invalid character before individually - it gives you a lot of ;;;;;;)

-Set another variable with the value temp 2 (within apply to each)

-outside apply to each now create a compose with the value variable mailsnip and you get firstemail@outlook.com;secondemail@gmail.com

Instructions here https://www.alanps1.io/power-platform/flow/flow-stripping-unwanted-characters-or-special-characters-...

Which you can use as a dynamic content in your email.

This now sucessfully;

triggers in an earlier part of the flow

Does some things

reads my email table (which is updated from specific outlook contacts using a different flow)

And sends them all an email (the body can be anything you like)

Now if i remove in my outlook or add somebody to the department the table will reflect it, without me having to dive into the flow to amend. - the contact to excel syn is a different flow alltogether, which was just as annoying

Im sorry my explanation isnt great, i started using this a couple of days ago so getting used to it.

If anybody else can explain better please do!

Map Key left blank, Value is desired columncut begin expression is union(body('Select'),body('Select'))substring(outputs('cuthtml'),6,sub(length(outputs('cuthtml')),6))take(variables('String'),sub(length(variables('String')),3))take(variables('String'),sub(length(variables('String')),3))createArray('"},{"":"')replace(variables('mailsnip'),item(),';')

[Anonymous]

As a side note to this -

I could have achieved the same thing by creating a group email in Sharepoint and adding all intended team members, then putting the group email address in and controlling members in sharepoint.

The limitation i have is some of our team use nhs.net email addresses so they wouldn't be able to receive.

And i'm also planning on using a similar set up to inform clients which are outside of our org- the problem i will have there is I want to be able to fine tune it to send the same email to all clients in batch groups of company .

So i dont have an email with 100 people visible to each other. But i also don't want to have to repeat an action 10 times.

A workaround for that (i just thought of while typing ) is to put the final email address output into the bcc section under advanced in the email action i suppose.