Reminding about an Overdue Opportunity to Close Dates in Dynamics 365 CRM

In this article, the fourth in the series of articles devoted to Everyday Power Automate Solutions for Dynamics 365, we will build a simple Power Automate flow that automatically reminds opportunity owners whenever an open opportunity has passed its expected close date.

Andrei is a highly experienced Project Manager who leads a team specializing in Microsoft technologies, including Dynamics 365, Power Platform, Azure, Copilot, and more. Together, they deliver tailored solutions designed to address each client's unique business requirements. In addition to his leadership expertise, Andrei has extensive hands-on experience implementing Dynamics 365 Sales and Customer Service.

Opportunities play a central role in the sales process. They represent potential deals that have already progressed beyond the initial lead stage and are now being actively worked on by the sales team. Because opportunities are often used for sales forecasting, pipeline analysis, and performance reporting, keeping them up to date is just as important as creating them in the first place.

One of the most common automation requests is to notify managers when an opportunity has not been updated for a certain period of time. We have already looked at a very similar scenario for leads in one of the previous articles in this series, Managing Forgotten Leads in Dynamics 365 CRM.

This article focuses on another issue that many sales teams face. Opportunities often remain open even after their expected close date has passed. These outdated records can affect pipeline reports, make sales forecasts less reliable, and make it harder for managers to understand which deals still require attention.

Let us look at another common request that comes from sales teams after they have been using Dynamics 365 for a while.

During one of our meetings, a sales manager mentioned that many opportunities remained open long after their expected close date had passed. Some of them were still active because the sales process was taking longer than expected, while others had been forgotten. Since these records continued to appear in the sales pipeline, they made reports less accurate and made it more difficult to understand which opportunities still required attention.

The manager asked us to create a reminder that would help keep the pipeline up to date. The requirement was to run the process once a week and notify the opportunity owner whenever an open opportunity had a required "Estimated Close Date” field set for earlier than today. The reminder would encourage the owner to review the opportunity and either update the expected close date or close the record if the sales process had already finished.

After you have accessed Power Automate and selected the correct environment, it is time to create a flow:

1. In the environment where you want to create the automation, start by creating a new “Scheduled cloud flow” (1).

   

2. Give the flow a meaningful name (2), configure the start date, time, and recurrence interval (3), then click on the “Create” (4).

   

3. Before adding any actions, it is worth checking that the Recurrence trigger contains the schedule you configured in the previous step. Once that is confirmed, add a new "List rows" action. Since our goal is to monitor opportunities, select the "Opportunities" table in the "Table name" field (5).

   ! Note. We do not need to retrieve every opportunity in the system, so let us narrow down the results. The "Filter rows" field allows us to return only the records that match our requirements.

   Now, go through the following stages:

   1) First, we only want the open opportunities, so add the condition “statecode eq 0”.

   2) Next, we only want to notify the owners when the "Estimated Close Date" has already passed, so add another condition based on the "Estimated Close Date" field. Since this field is required in our CRM, there is no need to check whether it contains a value.

   3) Combine both conditions using the "and” operator so that only the records matching all the criteria are returned (6).

   

4. Next, let us create a variable that will be used in the email notification. This variable will contain a direct link to the opportunity record (7).

   To build the record link, open any Opportunity in Dynamics 365 and copy the URL up to and including the last '=' character before the record GUID. We will use this base URL to dynamically generate links to individual opportunity records.

   

5. The next step is to identify the owner of each opportunity so we can retrieve their email address.

   After that, follow this subsequence:

   1) Add a new "Get a row by ID" action.

   2) Since we are looking up system users, select the "Users" table in the "Table name" field.

   3) For the "Row ID" field, use the "Owner (Value)" from the "List rows" action. As soon as you add this value, Power Automate will automatically place the "Get a row by ID” action inside an “Apply to each” container. This is expected behavior because the flow needs to retrieve the owner information for every opportunity returned by the previous step.

   4) In the “Select columns” field, enter the schema name of the field that stores the user's email address.

   ! Note. Technically, there is no need to specify the “Select columns”, and the flow will work without it. However, we recommend using it whenever you need a few fields. Retrieving only the required data makes the flow slightly more efficient and easier to maintain. You can apply the same recommendation to the "List rows" action.

   

6. Now, it is time to create an email notification:

   1) Add a new "Send an email" action, use the email address retrieved in the "Get a row by ID" step in the "To" field.

   2) Create a subject.

   3) Then build the message body.

   ! Note. The email can contain any information that makes sense for your business. In our example, we only included a link to the opportunity record. One small detail worth mentioning is that there is no space between the “Opportunity variable” created earlier and the "opportunityid" value. This allows the email to generate a single clickable link that opens the corresponding opportunity directly.

   

   Now, save the flow, make sure it is active, and let it run according to the configured schedule. From this point on, opportunity owners will automatically receive a reminder whenever one of their open opportunities has passed its estimated close date.

UDS Blog articles:

• Detecting Duplicated Leads by Email in Dynamics 365 CRM
• Identifying and Automating Inactive Leads in Dynamics 365 CRM
• Managing Forgotten Leads in Dynamics 365 CRM
• Power Automate Solution: Instant Teams Notifications for New Contact Registrations
• Lead Generation with Dynamics 365 Sales