Who's on the List?
Updated: Nov 7, 2019
Today's challenge was to get the list of email addresses from the "To:" part of an email, then augment that information with the user's name and job title, and then present that in an HTML table inside of Microsoft Teams. While this seems fairly simple, there were a couple of hangups that I felt were worth documenting.
I initially thought I could just take the To: field from the trigger and use that in the "Get User Profile" to pick up the Display Name and Job Title. What I found was that when I got to that step, Power Automate / Flow treats the entire To: field as a single string so it's trying to match "firstname.lastname@example.org; email@example.com; firstname.lastname@example.org" with a single record in Office365. In order to get the information for each email address, I'll need to split all of that up into an array.
To do this, I figured I'd have to use an expression (I looked for a built in 'action' of Split, but didn't see one. But if I went to the expression box and typed in "Split", a function comes up and walks me through what it needs (the field, and the delimiter). Here's the step and expression:
What I did was use the "Initialize Variable" step, gave it a name, and set it to an Array (because I want it to hold a list of things), and then set the default value to the split expression in the caption. I'm not great with expressions, so I started typing "split" (without the quotes) and then an open parenthesis. Power Automate tells me it wants the field I want to split, so I clicked on Dynamic Content and selected the "To" field. Then a comma, and then the delimiter in single quotes. Close parenthesis and we're off.
This next part is a little weird: Most of the time, PA / Flow will kick off an Apply to Each loop when I don't want one. This is the first time it didn't launch one when I did. So I added one manually and used the array variable as the input. Within the loop, I added the "Get User Profile" and used "Current Item" for the User (UPN). But now where do I put the information once I have it?
My usual caveat applies here especially...there are going to be more elegant ways to do this, but this is the hammer I have (the tool I know). What I did was put everything into another array variable inside of the loop so that I could convert it to an HTML table on the outside.
In order to do that, though, I need to first initialize the new array variable...which needs to happen at the top of the flow. Luckily, they make it really easy to add in a step, so I did that right after the trigger:
Then, back inside of my "Apply to Each" loop, I added the "Append to Array Variable" step to add a little bit of JSON for each of the email address entries. Before we get too impressed, I was essentially copying the information from this thread. Furthering my theory that you don't need to know how to cook, you just need to know how to follow a recipe. Here's the rest of the loop, note the highlighted commas, those are important:
From there, we can create our HTML table and post it to Teams pretty easily:
and here is the output in Teams (redacted):
Here is the complete flow:
This was a great exercise to help stretch my code-a-phobe skillset, so hopefully you'll find some value in it, as well.