when a new email arrives vlookup Subjectfilter

[Anonymous]

Hello,

how can I build a flow for following situation:

I work with "when a new email arrives(V3). I want to work with the subjectfilter. The subject Filter has: the latters a and b.

If the Mail has a "a", then persons A, B and C get a message/notification.

If the Mail has a "b", then person B, D and E get a massage/notifivation.

Thank you very much

[Rhiassuring]

Hi there, 

You're looking for a subject filter to work on ...letters? Not words, or a schema of some sort? I wouldn't recommend using letters, I'm hoping those are just a stand-in for a full word or at least a code like A03 or some such. 

Instead of "A" and "B", I will use the words "Apple" and "Batman".  🍎🦇

Since we are looking for two different words, I am not going to use "Subject Filter" - I am going to use the Ellipses > Settings > Trigger Conditions. In there, I will place this formula: @OR(contains(triggerBody()?['subject'], 'Apple'),contains(triggerBody()?['subject'], 'Batman'))  These words are case sensitive.

Next, I am just going to use a "Compose" action to determine whether it was Apple or Batman that triggered the flow. I am using this formula: 

if(contains(triggerOutputs()?['body/subject'],'Batman'),'Batman','Apple')

I will initialize a variable to hold our emails in. We will use this instead of hardcoding so it is more flexible and we only need one email action.

Then, I will add a Switch that evaluates the Outputs from our "Compose - Apple or Batman" action.

I will set the variable in each "Case" according to my needs. (⚠️ I would recommend not hardcoding where ever possible - in a real world scenario, I would have a SharePoint list holding "Apple" and "Batman", with another column to identify the recipients - I would query this list based on the outcome. Let me know if you need help with this part.)

In this example, just for the demo, I am hardcoding in the emails to set the variable.

Finally, I will just create an email outside of the switch and set the email address to the variable. I can even reference the chosen word using my Compose output.

Does that help?

Cheers,

Rhia

[Anonymous]

Thank you very much. 

The Problem is, that there are 132 "Apples;

, Badmans" and so on, not just two. And for the 132 "Apples,Badmans...." are around 300 Personen which gets a Mail/Notification. For Exemplar Apples- User 1,2, Badman - User 2,3,4..

[Rhiassuring]

Oh, I see - so you have a long list of 132 things to cycle through. In that case, I think you will need to have your email trigger regardless of each, turn your 132 items into an Array, and then loop through and do a "contains()" on each until you hit something - and then look it up from the list. 

Do you have scenarios where you might have more than one "Apple" or "Batman" in the subject? Or will it always be 1 of the 132?

[Anonymous]

I make an Other example: a House with 132 rooms. If the Windows get open, a Light Switched on or a door opens then some persons must get Infos.

Room 1: Person 1 and 2

Room 2: Person 2, 3 and 4

Room 3: Person 2 and 5

....

Room 132: Person 3, 299 and 300.

IT is always one of 132 rooms.

Thank yo

[Rhiassuring]

That is a good example 🙂 Okay, we got this, just a few changes.  And, there are a few clarifications needed to make this specific to your scenario .... and more elegant. I will comment on those at the bottom. 

First... We will need to store your columns somewhere, of course, so we can reference them. Excel or SharePoint. I will use SharePoint for my example.

Here is my demo data. In your scenario, your "Email" column would have people -- I am using fake email addresses in mine because I am the only person in my tenant. 

⭐Step 1 - The same as before, we add a When a New Email Arrives trigger, but this time, no trigger condition. 
⭐Step 2 - Create a "Get Items" SharePoint action. Point it at your Matrix in SharePoint.

⭐Step 3 - Initialize variables:  1) booTitleFound (boolean - default "false") 2) txtEmail (string) 3) intIncrement (integer - default "0")

⭐Step 4 - Add "Do Until" action and set it to run until "booTitleFound is equal to true". I've increased the Change Limits > Count to 132, given your requirements.

⭐Step 5 - Inside the Do Until action, create a "Set Variable" action. Select your booTitleFound variable, then in the Value, click on the Expression tab. Enter the following: 

if(contains(triggerOutputs()?['body/subject'],outputs('Get_items')?['body/value'][variables('intIncrement')]?['Title']),true,false)

⭐Step 6 - Add another Set Variable - this time pointed at your txtEmail variable. In the value field, click on the Expression tab, and enter the following: 

if(variables('booTitleFound'),outputs('Get_items')?['body/value'][variables('intIncrement')]?['Email'],'')

⭐Step 7 - Add an "Increment Variable" action and set it to increment intIncrement by 1.

⭐Step 8 - On the outside of your Do Until loop, add a "Send an Email" action. Use the "txtEmail" in your "To" field. Now you can use all the original elements from the incoming email to populate your email to the users.

Annnnnnnnd that's it. I used fake email addresses, so I won't send emails, but, here's the output from txtEmail when I send an email to myself with "Room 2" in the title. 

🏁Now, considerations for your requirements to make this a better flow:

1) If you can identify any sort of format of what you'd expect to come in - ie, the word "Room", or, a sequence of numbers... you'll want to user a Trigger Condition so this thing doesn't need to run every time.

2) If this is a personal mailbox that is usually monitored by a human, you could also use a Folder - ie, create a Folder called "Automated Maintenance" and drag incoming emails that need to be processed into it. This is more human action, though, so I get why you wouldn't want to do this.

3) You could also use Outlook rules to sort mail into the folder, if the requests come from specific email addresses, OR, you could create one rule per potential Subject word to automatically move them into folders. 

Hope this helps anyway, 

Cheers,

Rhia

[Anonymous]

Thanks. 

At the end of the last Point I get by Test following message:

What is wrong?

In this example are just three Do-Untils

[Rhiassuring]

Ah, right, for the email variable - if it's going to end up coming out empty (ie, it finds nothing at all), then it would fail the flow because it didn't email (even though, really, that's a success.) To avoid the fail, you could put a condition in (after the Do Until loop) that says "Is bool true?" - on the Yes side, the email - on the No side, nothing. That'll avoid the fail.

[Anonymous]

Thank you. Could you Show What you mean please?

[Rhiassuring]

[Anonymous]

Hello.

Thanks. But the problem/error-message is still the same:

Is in the Sharepoint ApprovalMatrix anything what is important, for example the file-type?

[Rhiassuring]

For my SharePoint list (which I called ApprovalMatrix) in my example I am using a text field called Email ONLY because I have no other users in my tenant. In your scenario, you would use a Person field - and you would look at the "User Email" output from SharePoint.  (It would also show you User Displayname, User Department - but you'd want User Email.

[Anonymous]

Thanks again. Now, I changed the Sharepointlist as you write to Person/Group. Now my next problem-message in the power automate in the last point send an email vs:

OpenApiOperationParameterTypeConversionFailed. The 'inputs.parameters' of workflow operation 'Send_an_email_(V2)_2' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'emailMessage/To' is required to be of type 'String/email'. The runtime value '"[{\"@odata.type\":\"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser\",\"Claims\":\"i:0#.f|membershipxx@xx.de\",\"DisplayName\":\"XXX\",\"Email\":\"XXX\",\"Picture\":\"https://X.sharepoint.com/sites/TXXXX/_layouts/15/UserPhoto.aspx?Size=L&AccountName=XXX\",\"Department\":\"XXX\",\"JobTitle\":\"XXX\"}]"' to be converted doesn't have the expected format 'string/email'.

[Rhiassuring]

Ah, I'm sorry, Iet's back up a bit.

Put "txtEmail" back into your "To" field on your email -- that's my fault, I forgot what scenario we were looking at. I need to test, I'll be right back.

[Rhiassuring]

 To summarize, I've added in an intSharePointID variable, and then using a Get Items and Append to String to get the Email claims from each. Your txtEmail will work after this.

The formula for the 'set intSharePointID' variable is:

if(variables('booTitleFound'),int(outputs('Get_items')?['body/value'][variables('intIncrement')]?['ID']),0)

Inside the "yes" side of our condition, we are now getting a single item from SharePoint using the int variable, and then we select the "MaintenanceContact Email" in an Append To String against our txtEmail variable. This creates a loop, and adds each one.

Now you will be able to use txtEmail!

[Anonymous]

Thank you again.

The "Test" works without an error-message. But I do not get an email.

And I do not know from where is "MaintenanceContact Email".

And I do not understand what is with the "initialize variable intInvcrement" and "Set txtEmail".

[Anonymous]

Can me somebody help please?

[Rhiassuring]

"MaintenanceContact" is my example of a person field. Yours might be just "Contact" or "Person" or whatever else you have named it. That field comes with several automatically created fields: Person DisplayName, Person Department, Person Email, etc. You want to use the one that is like, "Person Email" because it will automatically pull the email from the user account.

Initializing a variable - you always must Initialize a variable before you can set it. So because we can use Increment, we need to initialize it.

Set txtEmail - we aren't using that, we are using Append to String variable. When I give screenshots, the left part before the hyphen is always going to be the original name of the action used in Power Automate.

[Anonymous]

Thanks. Now, it works.

This example is a house with around 130 rooms with around 300 persons how get informed.

My question: How Can I set for example 5 cases for every of the 130 rooms.

"Room 1 ... light on"(or off); "Room 2 ... windows open"(or close); "Room 130 ... power off".

Important is, that the rooms and the cases doesen't stand always together, there are other words between this two parts of the Email-subject.

[Anonymous]

Can anybody help me?