Automatically email new leads from past 24 hours in Dynamics 365

Learn how to email leads from the past 24 hours to the specific lead owner in an HTML table


The challenge

In this case, new leads are generated automatically in Dynamics 365, from a webpage contact form, and then assigned to different owners.

We want to collect the new leads from the past 24 hours and send each individual owner a reminder (in a table format) via email.

The challenges we have

  • The createdon time in Dynamics is in UTC (while I live in Denmark).
  • We need to identify owners who have new leads to avoid sending empty tables/emails.
  • We must create a table that includes only the leads for a specific owner.
  • We need to construct the table format for the email.

The solutions

We will create a Power Automate flow that runs daily at 10:00 AM (local time) that collects all new leads created in the past 24 hours and assign them to their respective owners.

This is the flow we are going to build

The flow

Create a scheduled cloud flow
First we will create a new scheduled cloud flow and set it to run at 10:00 AM. We will initialize a new array variable called OwnerID to store all the different owners.

Retrieve leads within the past 24 hours
To retrieve leads created within the past 24 hours, we will query the leads table, and apply an ODATA filter to the createdon field.

The data we get from Dataverse from the column createdon is in UTC, and we want to convert that to our current timezone.

Since the flow is running at 10:00 AM we will just get the current UTC() time, convert the timezone from UTC to (in my case) W. Europe Standard Time, and then we will subtract 1 day (addDays -1)

Expression 1

formatDateTime(addDays(convertTimeZone(utcNow(), 'UTC', 'W. Europe Standard Time'), -1), 'yyyy-MM-ddTHH:mm:ssK')

Next we will use the same code, but without removing 1 day.

Expression 2

formatDateTime(convertTimeZone(utcNow(), 'UTC', 'W. Europe Standard Time'), 'yyyy-MM-ddTHH:mm:ssK')

The final filter rows will look something like:

created on ge ’expression1’ and createdon le ’expression2’

  • ge = Greater than or equal to
  • le = Less than or equal to

Get unique owners
Now that we have all the leads within the past 24 hours, we will loop through each of them, to find out who owns the leads.

  • Create an apply to each action and insert value from List rows leads

  • Inside the apply to each add a condition that express: ownerID does not contain Owner (Value)

  • Then in the Yes condition, add an Append to array variable and add the value to the array variable

Now we know how many unique owners we have, and who we should send an e-mail.

Create a loop for each owner and filter array
Create another apply to each (outside the previous loop) and this time select ownerID as the output from the previous step

Create a Filter array action and add the following

  • From = Value (from List Rows)
  • Owner (Value) is equal to current item (be careful: You have two current item so make sure you select the one from your second/current loop)

Select columns
Add a Select action and in the From add Body from our Filter Array action

Now we will begin to build our table. In the Map write html in the left site column.

For my HTML table, I have taken the following fields

  • subject
  • firstname
  • emailaddress1
  • The ID so that I can create a link

You can get the internal names of the columns you are looking for by looking inside the filter array output (this is just one way to get the column names)

Very important You should replace each item within the { } in the below html code with the following expression

{item()?['subject']} should be replaced with following expression item()?['subject']

In the last column, Open in CRM, replace YYY with your tenant and ZZZ with your app ID

<tr>
<td style="font-family: Open Sans, sans-serif; border: 1px solid #dededf; background-color: #ffffff; color: #000000; padding: 5px;">{item()?['subject']}</td>
<td style="font-family: Open Sans, sans-serif; border: 1px solid #dededf; background-color: #ffffff; color: #000000; padding: 5px;">{item()?['firstname']} @{item()?['lastname']}</td>
<td style="font-family: Open Sans, sans-serif; border: 1px solid #dededf; background-color: #ffffff; color: #000000; padding: 5px;">{item()?['emailaddress1']}</td>
<td style="font-family: Open Sans, sans-serif; border: 1px solid #dededf; background-color: #ffffff; color: #000000; padding: 5px;"><a href="https://YYY.crm4.dynamics.com/main.aspx?appid=ZZZ&pagetype=entityrecord&etn=lead&id={item()?['leadid']}">Open in CRM</a></td>
</tr>

Select HTML
Still working inside your loop add another Select action - Output should be from the previous Select action we just created.

Click the little icon with a T and insert the following expression

item()?['html']

Building the table
Next up, create a new compose action

The below code is building the table header, and is implementing all the table rows we made earlier.

Insert the following code in the compose action

<div>
<table style="font-family: Open Sans, sans-serif; border: 1px solid #dededf; height: 100%; max-width: 75%; table-layout: fixed; border-collapse: collapse; border-spacing: 1px; text-align: left;" width="100%" height="100%" align="left">
    <tr>
    <th style="border: 1px solid #dededf; background-color: #eceff1; color: #000000; padding: 5px; text-align: left;" bgcolor="#eceff1">Subject</th>
    <th style="border: 1px solid #dededf; background-color: #eceff1; color: #000000; padding: 5px; text-align: left;" bgcolor="#eceff1">Name</th>
    <th style="border: 1px solid #dededf; background-color: #eceff1; color: #000000; padding: 5px; text-align: left;" bgcolor="#eceff1">Email</th>
    <th style="border: 1px solid #dededf; background-color: #eceff1; color: #000000; padding: 5px; text-align: left;" bgcolor="#eceff1">Open in CRM</th>
    </tr>
     HERE WE ARE GOING TO INSERT AN EXPRESSION LATER
</table>
</div>

Replace Subject, Name, Email and Open in CRM with your own headlines

Replace HERE WE ARE GOING TO INSERT AN EXPRESSION LATER with join(body('Select_HTML'),'')

Get lead owners e-mail
Since we already have the ID of the owner, we can use it to get the user details from the Users table in Dynamics

Still inside the loop insert an Get a row by ID action and select the Users table.

The row ID should be current item (and again, be carefull that you select the correct current item)

Send mail
Last we will insert an e-mail to the primary e-mail we got from the user table, and in our body we will insert the output from our compose action

Final loop
Here is how my final flow looks like

E-mail
This is how the e-mail could look like


See also