cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cindelwood
Helper I
Helper I

for ALL list items get user email from display name and send emails

Hello, I am trying to create a flow that will send an email notification when a user clicks on the "Send Request" button as displayed in the mockup image. The issue I am having is how to pull in the email address for each individual training facilitator based on the value in the 'FACILITATOR' column (person column). 

 

Unfortunately, no one in my org has been able to successfully create a robust approval flow due to the same underlying challenge - how to pull and/or sync profile and/or directory information to enable easy use within power automate. I'm at my wit's end and approaching a project deadline - any help would be very appreciated. 

cindelwood_1-1646077123619.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
cindelwood
Helper I
Helper I

Hi @Expiscornovus 

I got it! Thanks so much for your help!!

View solution in original post

10 REPLIES 10
Expiscornovus
Super User
Super User

Hi @cindelwood,

 

Do you already have a flow setup? If so, can you share a screenshot?

 

What you could do is use a For a selected item trigger action. That action will only return a couple of properties of the item. With a Get Item you could retrieve the rest of the columns like Facilitator. After that you should be able to see/use the Facilitator Email column from the Dynamic content list.

 

Below is an example of that approach.

 

1. The flow setup.

 

getitem_forselecteditem.png

 

2. The formatting of the Send Request column

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "button",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "{\"id\":\"yourflowid\", \"headerText\":\"Example E-mail Template\",\"runFlowButtonText\":\"Send E-mail\"}"
  },
  "attributes": {
    "class": "ms-fontColor-themePrimary ms-fontColor-themeDark--hover",
    "title": "Launch Flow"
  },
  "style": {
    "border": "none",
    "background-color": "transparent",
    "cursor": "pointer"
  },
  "children": [
    {
      "elmType": "span",
      "attributes": {
        "iconName": "Mail"
      }
    }
  ]
}

 

sendemailformatting.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Hi @Expiscornovus

I've created and tested a number of flows for this but don't have anything worthy of a screenshot at the moment. I was able to get one to partially work where a value for UserEmail is populated in List A Onboarding Dashboard from List B User Table>UserEmail column, however, the value does not match the associated Assigned Facilitator (person/display name) for that particular item. 

The User Table has 300+ items. I'm essentially trying to query that table with a filter on the value in Assigned Facilitator for the current item. I've included a couple of screenshots of the two lists for your reference. 

 

cindelwood_3-1646758028189.png 

cindelwood_4-1646758052340.png

 

 

Expiscornovus
Super User
Super User

Hi @cindelwood,

 

Thanks for the additional information.

 

Do you have a specific requirement to use that User Table lookup? (for example you want a custom e-mail which is not part of the users profile).

 

Normally such a setup would not be needed. Using a column of type person or group would be sufficient to retrieve a persons email.

 

Is your Assigned Facilitator column of type person or group (like in the screenshot below)?

 

personorgrouptype.png

 

When using this type of  field you will get all these different kind of user profile properties, including the persons email. 

 

assignedfacilitator.png

 

 

 

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


cindelwood
Helper I
Helper I

Hi @Expiscornovus 

I believe one issue is that we are currently using Lotus Notes for email, not Outlook. One of the challenges is determining what directory information is available for flow (if any) and how to incorporate it. For example, I've tried creating a flow using MS Office directory, however, ran into a multitude of challenges when attempting to use actions such as Get User Profiles. I tried using set/initialize variable with this as well. 

I replicated the flow you shared, but with Send Email notification V3 and of course it doesn't work. 

 

cindelwood_0-1646854127037.png

cindelwood_1-1646854137164.png

 

"errors": [

    {

      "message": "Does not contain a valid address.",

      "field": "personalizations.0.to.0.email",

      "help": "http://sendgrid.com/docs/API_Reference/Web_API_v3/Mail/errors.html#message.personalizations.to"

 

Expiscornovus
Super User
Super User

Hi @cindelwood,

 

Thanks for clarifying your setup. I understand why you use that supporting user table.

 

I have created an example.

In this example I use a Get Items with a filter query. This filter query tries to match what is in the Assigned Faciliator field of the on boarding list against the DisplayName of the User Table list items.

 

The To field of the Send an email (v2) action uses the UserEmail field value of the first list item it finds. I have used the following expression for that:

body('Get_items')['value'][0]['UserEmail']

 

displaynameeqassignedfacilitator.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


cindelwood
Helper I
Helper I

Hi @Expiscornovus 

I tried out the flow and got this error:

cindelwood_0-1646938421937.png

cindelwood_1-1646938517781.png

 

 

Expiscornovus
Super User
Super User

Hi @cindelwood,

 

Make sure you use the internal name of the Display Name field in the Filter Query. It is very likely that there is not a space character in there (normally SharePoint removes space characters in the internal name or replaces them by _x0020_)

 

You can check that in the column settings. Below is an example how you can check that (ignore the fact that I used the wrong column in this example 😅)

 

Internalname_SPO_column.gif

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


cindelwood
Helper I
Helper I

Hi @Expiscornovus 

Thanks for the tip. That seemed to resolve that issue, however now I got this error message: 

cindelwood_0-1646941287064.png

 

Expiscornovus
Super User
Super User

Hi @cindelwood,

 

The error suggests that it hasn't found any list items in the User Directory list which have a Display Name value which exactly match 'Candace Indelwood' (assuming your using the same test example as in your previous reply).

 

Just to double check. Your display name field is of type single line of text?



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


cindelwood
Helper I
Helper I

Hi @Expiscornovus 

I got it! Thanks so much for your help!!

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (5,866)