cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mithushalk
Helper IV
Helper IV

Based on Form response pick an email address from sharepoint excel

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

mithushalk_0-1620305436705.png


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.


1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Super User
Super User

@mithushalk 

 

Hopefully this makes sense.... 😉 your previous message includes the expression as a string and so won't evaluate.

 

ezgif-7-6e134b565a04.gif

 

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 😉

View solution in original post

26 REPLIES 26
DamoBird365
Super User
Super User

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 😉

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.

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.

mithushalk_0-1620313356261.png

 

@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

@DamoBird365 

I created a list on Sharepoint.

mithushalk_0-1620316578366.png

mithushalk_1-1620316672775.png

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

Any thoughts on this? 

@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

@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
Super User
Super User

Hi @mithushalk 

 

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

 

Damien

@DamoBird365 

Sure.

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

@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:

 

DamoBird365_0-1620373320150.png

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 😉

@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_1-1620374651325.png

 

@DamoBird365 

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

@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

@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

mithushalk_0-1620436596278.png

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

@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

@DamoBird365 

mithushalk_0-1620464388097.png

I am unable to check the email history. 

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 😉

@DamoBird365 

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

mithushalk_0-1620465421977.png


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

@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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (3,566)