Auto Forward mail to different recipient based on email subject

[Anonymous]

Hello,

We want to create a Power automate flow to distribute mails to a vast user list. Different emails are to be forwarded to different user set.
Since the number of distinct mails is very high (~1000) Creating separate flows is not feasible. 

The decision about which mail is to be forwarded to which user set is to be made upon the Email subject.

We have already worked with email forward power automate flows which uses an excel file for fetching the recipient's email addresses.

We wanted to know, is there any way to filter recipient's email based on the Comparision between subject of the mail to be forwarded and the subjects mentioned in an excel sheet which contains the list of recipients.

Attached Image "power automate flow - eg xl" shows the sample excel file which has columns for email id, subject, and mail body. What we to achieve here is to forward the mail to akshay.thakur@xyz.com if the subject of the original incoming mail is "Test for Akshay" which is also in the subject column of the Excel and so on.

[RobElliott]

The way I would probably do this is to have a switch control which is a type of condition that looks at, in this case, the subject of the email. You then have up to 27 "cases". So if the subject equals Test for Akshay then send an  email. In case it's Test for Kajal then send a different email.

We do this for the hundreds of questions that come in from our staff about HR, IT and about 15 other subjects. All of which have emails sent to different department heads and there can be different content in each email. 

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[Anonymous]

Hello @RobElliott 

I tried the solution provided by you. But it solves the problem partially. 
Kindly let us know, if anyhow we can incorporate dynamic text (i.e. Excel column) in Switch condition (i.e. Equals)

[RobElliott]

Yes you can use an Excel column as the Switch condition. In this example it's looking at the Lastname column in the table. So the first case would equal Thakur, the second Badlani, the third Aware etc etc

 Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[Anonymous]

Hi @RobElliott ,

We want the particular recipient to be in accordance to the subject column value which matches the subject entered in "Equals" of the switch node.

For instance, if the received email has subject "TEST for Kajal", then it should be forwarded to the email address corresponding to the row in excel which has the same subject.

Thanks.

[grantjenkins]

Will each Subject in your Excel table be unique? Or could there be a Subject that appears more than once in which case it would be forwarded to multiple people?

[grantjenkins · Accepted solution]

I'm going to assume that there could be multiple emails/rows per Subject.

Here's a nice way to get a list of all the emails that match and joined by a semi-colon ready to send an email. What I'm not sure about is if you are just going to send a single email to everyone that has that Subject, or individual email to each match using the content from the Body column. I'll show both examples here.

For this example, I have the following Excel file.

Example 1 - Get a list of matching emails separated by a semi-colon.

Below is the full flow for example 1. I'll go into each of the actions.

When a new email arrives is fairly straight forward. Mine is set to wait for an email in a particular folder.

List rows present in a table get all the rows where the Subject is equal to the Subject of the email that just arrived.

The Select then takes each of the rows that were returned and outputs a simple array of emails.

If the Subject of the email was Test for Grant Jenkins (based on the example Excel Table) we would get back the following:

[
  "grant.jenkins@microsoft.com",
  "bob.jones@microsoft.com"
]

We can then use a Join to concatenate the emails separated by a semi-colon.

grant.jenkins@microsoft.com;bob.jones@microsoft.com

You can then add the output of your Join into your email and send to all recipients.

Example 2 - Send an individual email to each matching row taking the Body content from the Excel Table.

Below is the full flow. I'll go into each of the actions.

When a new email arrives, and List rows present in a table are the same as the previous example.

We then have an Apply to each that iterates over each of the rows that were returned.

Get a row will return each of the rows returned (one by one) using the ID field since this is the only field that can uniquely identify each row (your Table must have a column that contains unique values which it looks like it does).

Finally, we can use Send an email to email each of the users including the content from the Body column in the Excel Table. You need to make sure you only use the fields from Get a row in your email (not fields from List items present in a table).

[Anonymous]

Hey @grantjenkins ,

Thanks for such an elaborate response!
I am currently applying these suggestions and will let you know once they are done.

[Anonymous]

Hey @grantjenkins ,

Kudos to your solution. 
Solution 1 did the trick for us. Thanks a lot mate!

[FlowingUp]

Hi Grant,

I used this extremely helpful information to create a similar flow. However, I am running into an issue with multiple emails being sent to each recipient.  Do you have any recommendations about that?

https://powerusers.microsoft.com/t5/Building-Flows/Flow-Sending-Multiple-Emails/m-p/1997182#M220151

Thank you!