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

[cindelwood]

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. 

[Expiscornovus]

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.

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"
      }
    }
  ]
}

[cindelwood]

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. 

 

[Expiscornovus]

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)?

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

[cindelwood]

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. 

"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]

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']

[cindelwood]

Hi @Expiscornovus 

I tried out the flow and got this error:

[Expiscornovus]

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 😅)

[cindelwood]

Hi @Expiscornovus 

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

[Expiscornovus]

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?

[cindelwood · Accepted solution]

Hi @Expiscornovus 

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