Flow to create multiple mails to multiple recipients

[Emil1]

Hi

The flow I'm creating should create emails based on a table in an excel sheet. The excel table contains 5 columns: (product name, period, green label, blue label, email). The flow should create two mails for every unique product name - one email to the green label recipients and one mail to the blue label recipients. In the two label columns there are a 'X' marked for those emails that need either the blue or green label email. The flow should send only two mails per product and it therefore needs to create a string of recipients that have the same product name in common and a 'X' in the same label column. 

Challenges: I can't get the flow to work with a table with different product names. When I add multiple product names it sends two mails per product, but it sends all blue label emails to all recipients with a 'X' in the blue label column. I believe I should somehow link the email variables to the product names so that I create a different string variable with emails for each product name. But I don't know how to do that.

Any ideas are much appreciated. Please tell me if anything needs to be elaborated. Disclaimer: I'm fairly new to power automate, so it's quite possible that there is an obvious solution to this that I'm missing.

[zbegumdost]

hi,

It wasnt clear for me you have products if product has X in blue label you want to send email only for blue with email cell in the to mail part right?

[Joseph_Fadero]

Hi @Emil1 ,

Can you please share a screenshot of the flow you have already created so we can see how best to assist with your request

[Emil1]

Yes here is two screenshots of the flow. I've tried to create three variables, one for the products and one for each mailing list (blue and green). But as I said earlier I think I might need to link the variables somehow. Also the flows creates drafts of the mails, thats on purpose and that part works right now. 

[Emil1]

Hi @Joseph_Fadero 
I've added screenshots in the reply below. Any help would be much appreciated.

[Joseph_Fadero]

Hi @Emil1,

Sorry for the late response, 

I am still trying to properly process your request, 

Can you please clarify. 

What is the outcome of the current flow, and what is the expected outcome. You can ignore the variable, Also sharing an anonymized version of the data can also help. Also maybe if you can show the expression used in your core variables that may help. I think with further clarity I or someone else in the community would be able to help out with your flow. 

[Emil1]

Thank you for the reply @Joseph_Fadero 

Right now the outcome of my flow is two mail drafts for each product that have the either all the recipient emails for blue label or all the recipient emails for the green label. 
What I want the outcome to be, is two mail drafts for each product but only the recipients that are in the same row as the given products and have an 'X' in either the green or blue label (I have attached a picture of the excel table that I'm extracting data from). 

Here is the excel table I'm using as data source:

In this example I have two products meaning I want to create a total of 4 mail drafts. 1 for each label for each product. The mails should have the following recipients:
Mail 1 (product 1 blue label): Mail 1, Mail 2, Mail 3, Mail 6, Mail 9 and Mail 10. 

Mail 2 (product 1 green label): Mail 2, Mail 4, Mail 6, Mail 7, Mail 8 and Mail 10. 

Mail 3 (product 2 blue label): Mail 11, Mail 12, Mail 13 and Mail 14.

Mail 4 (product 2 green label): Mail 13 and Mail 14. 


The expression in the variables I used:
For the products:

Setup for the blue label mail string (the green label one is identical):

The last condition adds a ';' between the different mails allowing more recipient mails.


If you need anything else, or if I haven't properly explained the wanted outcome of the flow, please say so 🙂 
Thank you in advance!