Html email reporting with tabular data
Email reporting is a great way to export or share data from a PowerApp. With the native Office 365 Outlook connection, it’s easy to format an email with basic HTML to create nice looking email reports.
Example: Let’s say we have a company asset checkout system. The application needs to generate a report of items checked out by an employee and then notify the asset manager. For this scenario, we will create an email preview screen in the PowerApp with HTML tags and tabular data collected from the user’s interaction. The preview is used as a direct input for the Outlook connector, and shows will be seen in the resulting email.
I’m using the Asset Checkout template for the backend data.
Once the user has selected the items they will be checking out, the tabular data we will use to populate the email are identified (in this example, the items are objects the user selects and are saved to a Collection called ‘Cart’). With the target data in its own Collection, we can easily wrap the data in HTML tags that can be used to format the email.
Generate the email body.
To send a simple email, we can declare the user, list the items being checked out by the user, the date checked out, and a return date.
Use the ‘HTML text’ control to preview what you can send as an input to the Office 365 Outlook connector (under Insert > Text > HTML text). In the HtmlText property of the HTML text control, we can combine the application’s data with HTML tags to format into a nice-looking report with the tabular data in the Cart Collection. In our example we combine the user’s information, the current date, a return date (1 month from today), and the actual tabular data items with HTML tags. Let’s look at the different syntax and functions used in the HtmlText property displayed below:
Joining data, variables and static text
Use the ampersand (’&’) character (or alternatively the Concatenate() function) to combine text and data. Here we combine the row label followed with the associated datum joined with an ampersand like so:
"<b> Employee: </b>" & User.FullName & “<br>”
You can use any HTML tags to markup text in the HTML text control. All HTML tags must be in double quotes to be interpreted correctly by PowerApps. Here are details on some tags being used in the example above:
- New line: Use the link break HTML tag (“<br>”) to identify where you want a new line character.
- Bold: Wrap the text you want bolded in bold tags, like so: <b> boldedText </b>
- Ordered list: Identify the start and end of an ordered or numbered list by using the tags <ol> </ol>. List items are identified in between the ordered list tags as <li> listItem </li>, and will automatically be numbered. The syntax should be something like this:
<li> item1 </li>
<li> item2 </li>
<li> item1 </li>
A good reference for HTML tags and basics can be found at ww3 schools here: https://www.w3schools.com/html/
Formatting the Collection data
Use the Concat() function to join data rows within a Collection. In this example, we used the Collection ‘Cart’, so the first argument in Concat() is the Collection’s name, ‘Cart’.
The second argument in Concat() are the values that will be displayed. This includes the field names and any additional text or characters we add to the argument. In our case we are showing the ProductName and ProductId columns, so the second argument concatenates the two table fields by their respective column names.
We also number the items in the table by concatenating the ‘list item’ HTML tag (’<li>’) into the second field of this Concat() call. Since the list items are inside two option list (<ol>) tags, the items will be numbered automatically.
Send the email with the Office 365 Outlook connector
Connect to the Office 365 Outlook connector
Do this in View > Data sources in the top ribbons. If it’s not in the list of connections when you click ‘+ Add data source’, then select ‘+ New connection’. Scroll until you see the ‘Office 365 Outlook’ connector, click it, then click ‘Create’ to add it to the app’s data sources.
Setup the send email button
Create a button control where the OnSelect property will send out the email with the HTML body text we’ve just configured.
In the OnSelect property of the button, type in ‘Office365.SendEmail(‘, and intellisense will provide the list of up to 4 parameters and details needed to complete the method:
- The first parameter is the email recipient. In this case we always want the recipient to be the same email address of the asset manager, so the argument is a static email ‘assetManager@contoso.com’.
- Second is email subject. Here the subject will result as “Asset checkout by John Smith”, since the logged in user’s full name is John Smith.
- Third is the email body text. We can reference the formatted HTML email body we just wrote in the HTML Text control by typing in ‘HtmlText_EmailPreview.HtmlText’. Whatever appears in that control will be passed into the email body.
- In the fourth parameter make sure to set the variable IsHtml to true, otherwise all the HTML formatting will not work and the tags will be visible.
The possibilities are endless with HTML reporting. You can get creative and make simple data look more sophisticated with additional formatting, colors, and other controls such as tables.
Here we use table formatting instead of ordered list tags to organize the data. Where we used tabular data, instead of <ol>, the list items are wrapped in <table> tags. In place of <li>, a row is defined between a set of <tr> tags. The columns are wrapped with <td> tags. The header on top of the table is wrapped in a set of <th> tags. Also, there is a 3rd parameter in the Concat function to end the line’s current <tr> (table row) and begin the next one (like this: "</tr><tr>").
"<h3>Asset Checkout Notification</h3>" &
"<table width='100%'> " &
<td> Employee: </td>" & "<td>John Smith (firstname.lastname@example.org)</td>" &
<td> Checkout Date: </td> <td>" & Today() & "</td>
<td> Return date: </td> <td>" & DateAdd(Today(), 1, Months) & "</td>
"</table> <br>" &
"<strong> Items: </strong>" &
"<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" &
<th>Id</th> <th> Name </th> <th> Description </th><th> Accessories </th>
"<td>" & ProductId & " </td>
<td>" & ProductName & " </td>
<td>" & ProductDescription2 & " </td>
<td>" & ProductDescription1 & " </td>","</tr><tr>") &