Receive a weekly summary email of new D365 Opportunities using Power Automate

Have you or any of your customers ever had the need to schedule the automatic sending of emails? In this article I am going to discuss how we can user Power Automate to send out emails on a scheduled basis with information about Dynamics 365 records. This could be very handy in many scenarios, think about sending employees a list of their timesheets for the past week, or sending this information to clients as my coworker Chris Driscoll mentioned. Or sending reminders to employees reminding them to submit their expense reports, i can go on forever, as there are lots of scenarios where this would work. In this particular example I will schedule an email to go out once a week with a list of opportunities that have been created in the past 7 days. Obviously you can change the timing as you please and you can configure this for other Dynamics 365 records if you don’t want to use opportunities, but I am using this example because there is already a template for this in Power Automate. The only issue with this template is that it’s using the deprecated connector for Dynamics 365, which we shouldn’t use anymore. We should use one of the Common Data Service connectors, and personally I prefer the ‘Common Data Service (current environment)’ connector. So let’s start building the flow from scratch.

Configuration

Log into flow.microsoft.com and make sure you are in the correct environment. Since we are going to use the ‘Common Data Service (current environment)’ connector, we will need to be in the same environment as where our D365 data is stored. We can start building the flow from within a solution that we’ve created or we can add it to a solution later. Click on ‘My Flows’ then click the new button on top. We want this to be a scheduled flow so select ‘Scheduled from blank’. In the next window we will need to enter the name for the flow, when it should start running (date and time) and what the recurrence is. I’m selecting it to run every week. When we pick it to repeat once a week, it will allow us to pick a day for the flow to run. I’ll configure this flow to run every Monday.

When the flow loads, you see the recurrence step automatically was added. If you need to make any changes you can still do that here. You can do so by clicking the edit button on the ‘Recurrence’ step.

Get Past Time

Click on ‘+New Step’ and search for the ‘Get Past Time’ action. This step will give us a date value back. If the interval is set to 7 and the time unit is set to day, that means that the value it will spit out will be 7 days prior to the day the flow runs. We will use this in our query for opportunity records where we only want opportunity records that have been created 7 days ago. If you want records that were created 14 days ago, you can change the interval to 14.

List Records (CDS – current environment)

The next step we need to add to the flow is the list records step but we want to use the Common Data Service (current environment) connector. I usually search for the connector and then pick the correct action (list records) from the list. In the list records step choose opportunities as the entity and then click ‘show advanced options’ so we can filter on the opportunities we want to include. You can use the Fetch XML builder in the XRM toolbox to filter your query. We want to get a list of all opportunities that were created on or during the past 7 days, so what you want to enter in the ‘Filter Query’ field is createdon ge (g=greater than/after the past time date, e=equal to (the past time date)after this expression we want to put the ‘past time’ value in there but make sure you put this in single quotes. You will be able to add ‘past time’ from the Dynamic content section. It will look something like the image below.

Create HTML Table

In the next step we want to create an HTML table that we can get drop into an email message. (and use some html formatting in the email to make it look pretty) so click on ‘+New Step’ and search for the ‘Create HTML table’ action. From the Dynamic content window enter ‘value’ in the ‘From’ field and select ‘Custom’ in the ‘Columns’ field. Now you can add additional columns to the table. The values I’ll enter in the table are: Date (created on), Opportunity (Topic), Owner (Owner Full Name), and Stage (Pipeline Phase). After you type these in the header, you can add the values from the Dynamic content window. For the Date value we will need to do a bit of formatting and I need to convert the ‘createdon’ field’s date to eastern time. (Other time zones can be found here) Click on expression and enter the following expression:

convertFromUtc(item()?['createdon'], 'US Eastern Standard Time', 'dd/MM/yyyy')

then click OK to add the expression in the Date value cell. If you don’t do this you’ll get some weird looking string of data.

We are almost done! We just need to complete the last step, which is the sending of the email. There are different ways to send the email, for example you could setup a shared mailbox in exchange and send it from that mailbox, of you can just send it from a users account. In my example I am going to send the email from a shared mailbox.
Keep in mind in order to do this you’ll need to setup the shared mailbox first: Which you can do by navigating to the admin portal of office 365. Then expand ‘Groups’ on the left hand side and select ‘Shared Mailboxes’. Click ‘+Add a Mailbox’ and set the mailbox name and email address. Once the mailbox has been created remember to add members to the mailbox by selecting the mailbox and clicking ‘edit’ next to Members.

Send email from a shared mailbox

Click + New Step and select ‘Send an email from a shared mailbox (V2). If you prefer sending it from a user account select ‘Send an email (V2). In the mailbox address (only present in the send an email from a mailbox action) select the mailbox you want to send the message from. In the ‘To’ field, you can enter individual but I would recommend setting up an office group or distribution list and sending the email there. The subject can be something like ‘New opportunities since last week’. When you get to the body of the email you need to click on the </> icon on the far right on the formatting toolbar (when you hover your mouse over the icon it will show a screen tip ‘code view’). You need to change to codeview because we want to copy and paste the below HTML to make it look pretty:

<p>Hello!<br>
<br>
Please see below for the opportunities that have been created in the past week.<br>
<br>
<span style="font-size: 14px"><strong>NEW OPPORTUNITIES</strong></span></p>
<br>
<html>
<head>
<style >
table, th,tr, td  { border : 1px solid black; }
th {background-color:silver;}

</style>

</head>
<body>

INSERT OUTPUT OF THE HTML TABLE HERE

</body>
</html>
<br>

You need to remove the section that reads ‘INSERT OUTPUT OF THE HTML TABLE HERE’ and add the output from the ‘Create HTML table’ using the Dynamic content window. The image below shows what it should look like.

That is it! All you need to do now is save your flow and click on the arrow on the top of the screen to navigate back to the flow details. Click the run button on the top of the screen and test your flow! I would suggest doing an advanced find query in Dynamics 365 so you can compare the data in the email with the records in Dynamics 365 to make sure all looks good.

I hope you enjoyed this post! Be sure to check in again next week for a new article or subscribe here to never miss another post!