Based on Form response pick an email address from sharepoint excel

[mithushalk]

In the form, there is a question with department choices.

Based on the chosen department an email has to trigger only to that department. (An excel file with departments email address is created on the SharePoint)
The email address has to be picked from that Sharepoint excel.
IF an email address can't be picked from the SharePoint excel sheet, please suggest how it can pick from the Sharepoint list.

[DamoBird365]

Hi @mithushalk 

I have done a very similar video here. It is based on a list as a source, but the same applies for excel.  Get rows from a Table with a filter, column eq 'dynamic value from form'.

Please let me know how you get on.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[mithushalk]

Hi DamoBird365,
I just went through the video & subscribed to your channel too, it's amazing.
Can you please build a sample flow for me & share the screenshot here, it would be of great help.

[mithushalk]

I just followed as explained in the video but I didn't receive mail at the end. I created a list & tried 
The test was successfull.

[DamoBird365]

@mithushalk 

Is that you sorted or do you want it to work with Excel?  

What did your excel solution look like?

It should work in exactly the same way.

Damien

[mithushalk]

@DamoBird365 

I created a list on Sharepoint.

The test is running successful but at last, the mail isn't going to the chosen department in form.

Any thoughts on this? 

[DamoBird365]

@mithushalk 

What's your expression for the To?

item()?['Test_Email_Address']

It's easier to address list fields without spaces. If you go to list settings and click on the field, you will see the actual name in the URL.  Field=

Damien

[mithushalk]

@DamoBird365 

I used the same expression as you used in the video. Can you please help me with the right expression for my flow? 

[DamoBird365]

Hi @mithushalk 

Can you share with me the expression?  Have you check the actual name for your email column in sharepoint?

Damien

[mithushalk]

@DamoBird365 

Sure.

Expression - outputs('Get_items')?['body/value']?[0]?['Email']

[DamoBird365]

@mithushalk 

I note in your screenshot that your column is called test email ad and not email.  Your expression will need updated to reflect the actual name of the column.  

You can check this via list settings and clicking on your email column:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[mithushalk]

@DamoBird365 

I updated the expression to 

outputs('Get_items')?['body/value']?[0]?['Team_x0020_Email_x0020_Address']
Flow ran successful but the Email isn't going to the chosen email address.
Also can you please confirm What I need to add in Filter Query under Get Items? 

[mithushalk]

@DamoBird365 

Any suggestions why mail isn't going out even after the flow runs successfully?

[DamoBird365]

@mithushalk 

Your filter for get items needs to be

columnname eq 'dynamic value'

Regarding the email not sending, can you give me a screenshot?  Is there an error?  Are you sure that an email address is being retrieved by your expression?  Try checking back on the history, you need to spend some time with your eyes looking over the output.  

Damien

[mithushalk]

@DamoBird365 

While creating a list I used a default name as Title so even after changing it appears to be as Title in the settings

Expression I used in the output - outputs('Get_items')?['value']?[0]?['Team_x0020_Email_x0020_Address']

[DamoBird365]

@mithushalk That looks really good but you will need to run the flow and check the history output as I cannot see this from your screenshots.  You need to check the history.

Does your filter on get items show any output in the history?  If not, your filter isn't correct.

Does your apply to each run 1 or more times?  If not, your get items is returning no items.

Does your send email action show an email address in the history?  If not, your expression must be incorrect.

If you are seeing an error or unsure about the history, you need to share screenshots of the history and not simply the flow as we cannot see the history and outcome of your flow, you must show it to us.  We're just volunteers, helping in our free time and don't have access to MS systems.

Fingers crossed we can get your sorted.

Damien

[mithushalk]

@DamoBird365 

I am unable to check the email history. 

[DamoBird365]

Hi @mithushalk 

That is useful.  You cannot check the history of the apply to each because your filter is not returning any results.  

Title eq '["Finance"]' does not match anything in the get items action.  Most likely you need Title eq 'Finance'.

So as a test, I would suggest you replace the dynamic value with exactly this:

Title eq 'Finance'

If this works, then I suggest you need to use a replace expression to tidy up the dynamic value returned by your form.  You need to use

replace(replace(outputs('Get_items')?['body/value']?[0]?['Team_x0020_Email_x0020_Address'],'["',''),'"]','')

This will return Finance without the brackets and double quotes.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[mithushalk]

@DamoBird365 

Amazing.  It worked now & even mail was also sent out. 

I will follow the instructions from the last message & test the flow again. 

[mithushalk]

@DamoBird365 

Can you please mention the right expression I am expected to use now? OR Does the expression remain the same?

I cannot find the "Replace" in the Expressions list.