Power Automate is a versatile process automation platform by Microsoft that allows businesses to streamline and optimise their operations.

Its application covers a wide range of scenarios, from managing approvals to automating document processes and integrating with Microsoft 365, Power Platform, or Azure.

In this article, I will take through building a process to automate the sending of e-mails to customers, prompting them from payment.

Before getting into Power Automate, I prepared a couple of views in the ERP’s system SQL Database, where the data on Accounts Receivable is stored.

These views will show customers who have any amount outstanding in their balance and how much of that, if any, is due. Also, they will hold a complete list of outstanding invoices.

The Power Automate flow starts by fetching the view of customers with outstanding amounts. Since this ERP system is on-premises, we will be leveraging the On-Premisses Gateway from Microsoft, which needs to be installed prior to building the flow.

^{Fig.1: SQL Server Get Rows (V2) Action}

Now that we have our list of customers and before getting into it, we need to initialise a variable to use further down the line:

• A variable to hold the e-mail or e-mails to use as the recipient.

Next, we will be iterating through the list of customers, looking for those with past due outstanding invoices and for each of those we will be looking at the view which holds all outstanding invoices and getting the ones corresponding to each customer.

^{Fig.2:  Apply to Each Action}

In this list of Outstanding Invoices, there are two date columns, Issue Date and Due Date, and since these are returned in a Date Time format (e.g., 2024-02-16 T00:00:00Z), we need to convert them to a more user-friendly format, we introduce a few steps to transform Date Time to Date only:

• Take the response from the SQL action and parse it.
• Convert it to text and remove the Time part of the dates.
• Reconvert it back to JSON.

^{Fig.3:  Data Manipulation actions}

With that in place, we can now build an HTML Table to show all the Outstanding Invoices.

As we draw close to the end of our process, it’s now time to get the e-mail or e-mails (depending on the requirements) to which we are sending our prompts for payment. In this scenario, we are using Microsoft Dynamics 365 as our CRM system, and the contacts need to be fetched from there. For that, we will be asking Dynamics to give us the contacts for each customer in this loop:

• List Accounts, filtering by the current ERP account.
• List Contacts, filtering by the above retrieved account.
• Get all gathered e-mails in the variable above initialised.

^{Fig.4:  Looping through accounts and contacts}

At this point, we already have a list of outstanding invoices and the e-mails’ recipients. Let’s build the e-mail:

• Input the variable holding the e-mails in the “To” field.
• Give it a good Subject.
• Write the Body and insert the HTML table and the e-mail signature.

^{Fig.5:  Send an e-mail action}

The result is a beautiful e-mail, fully automated that you can schedule to be sent on any given criteria.

^{Fig.6:  Sent e-mail}

Should you have any questions or require any assistance in streamlining and automating processes, feel free to reach out.