Introduction
Many times we had the requirement to sent out an email from power automate where data had to be formatted as a table. In this article, we will see how to create an HTML table within the mail sent out from Power Automate and how to beautify the table with CSS.
Use Case
The business use case is such that we have a SharePoint list that hosts the invoices for various purchases made by a department and at the end of every week, we need to collate the purchases of that week into a table and sent out the weekly mailer to the Business Unit head for review.
The procurement details will be stored in a SharePoint list with the below List columns
Department |
Procurement Cost(USD) |
Item Name |
Procurement Description |
Procurement Date |
Procured ? |
Implementation
From an implementation point of view, we will be using a scheduled flow so that the flow would be triggered at a specified interval every week. We will also be filtering out the data retrieved from the list so that only the data from the last 1 week will be brought into Power Automate for processing. The flow will be set to run on every Friday using the below settings:
We will also create two variables to hold the Week Starting and Ending date so that it can be used to filter the data returned from the SharePoint List.
Week Start Date Variable
The week start date can be calculated by subtracting 5 days from the current date(Friday) on which the flow would be running giving us the effective Monday of that week using the below expression :
| Expression : addDays(utcNow(),-5,’yyyy-MM-dd’) |
Week End Date Variable
The Week End Date will be the current date(Friday) of the flow run which we can get using the expression :
| utcNow(‘yyyy-MM-dd’) |
Once we have the date variables in place, we can get the filtered items from the SharePoint list by comparing the Procurement date with the Start and End Date of the week so that the action returns only those items that were procured in the current week :
| OData Filter Query : ProcurementDate ge ‘@{variables(‘WeekStartingDate’)}’ and ProcurementDate le ‘@{variables(‘CurrentFridayDate’)}’ |
The next step is to map the column names to the table headers so that we can build an HTML table out of it. We will add the select action and do the mapping as below:
Now we will add the Create HTML Table action and pass on the output from the Select action to it
Now we are in a state to call the Send an Email action, but the HTML will be quite plain and unformatted which will render the table in the mail as below without any styles.
So as to beautify the table, we will add a Compose action and append CSS styles to the Output of the Create HTML Table action. There are multiple online sites that can help you create beautiful CSS like the Table Styler or DivTable
I have added the below simple CSS to the Compose action
| <style> table { border-collapse: collapse; width: 100%; } table th { font-size: 15px; font-weight: bold; color: #FFFFFF; border-left: 2px solid #D0E4F5; background-color : #008080 } table th, td { text-align: left; padding: 8px; } table tr{ background-color: #f2f2f2; } </style> |
Finally, we will add the Send an Email action to send out the weekly report for review to the unit head as below. This time we have used the formatted and styled output from the Compose action within the mail body
Testing the Flow
We can test the flow manually without having to wait for the schedule of the flow and the manual testing has fetched the records of items that were procured in the current week and have sent the formatted mail to the approver
Summary
Thus, we saw how we can schedule a flow to fetch details within a specific time interval and send them as beautifully formatted HTML tables via email
