Recently, I have been working with Power Automate Desktop, where I got a requirement from one of our clients who wanted to share the Project Details Excel file with his manager via email in a tabular format.
However, unfortunately, Power Automate Desktop does not allow Excel tables in emails, so we need to add the data to an HTML table and parse it in the Email body.
This tutorial will guide you on how to add Excel data into an HTML table and send an email with an HTML table using Power Automate Desktop.
Insert Excel Table into HTML Email Body using Power Automate Desktop
Let’s consider I have an Excel data set with the “Product Details” as shown in the figure below. I would like to include the Excel data tables in the email as an HTML table.
Follow the steps below:
- On the Power Automate desktop app, create a flow.
- First, to open the Excel file on your local desktop, expand the Excel connector in the Actions pane. Then, drag and drop the “Launch Excel” action to the workspace. Provide the below information.
- Launch Excel: Choose and open the following document option.
- Document path: Click the insert file icon and select your Excel file.
- Make instance visible: You can enable this option to display Excel on the taskbar during flow execution.
This action will produce a variable named ExcelInstance. After that, click on the Save button.
- Next, to retrieve the first free column or row of an active worksheet, I will add the “Get first free column/row from Excel worksheet” action. This will be useful to extract the End columns of an Excel sheet dynamically.
- Refer to the image below that shows the end columns and rows.
- To extract the data from an Excel sheet, drag and drop the “Read from Excel worksheet” action that is present under Excel. Enter the details below:
- Excel instance: By default, it will take the Excel variable.
- Retrieve: I have selected the “Values from a range of cells” option.
- Start column: Enter the value as 1.
- Start row: Provide value as 1.
- End column: Select FirstFreeColumn variable -1, because this will give the end column dynamically.
- End row: Choose FirstFreeRow variable -1. This will get the end row dynamically without hardcoding it.
This action will produce the ‘ExcelData’ variable, where the Excel data will be stored. Click on the Save button.
Now, we have extracted the data from an Excel Worksheet.
- Then, add the “Close Excel” action and click on the Save button.
- Next, from the Action Pane, drag and drop the “Set Variable” action. Provide the HTML tags for the Excel data in the Value parameter. Click on the Save button. It will produce a variable named ‘NewVar‘.
<html><body><table border =1>
<tr>
- Add a For each loop action. This will iterate over the list of items in an Excel sheet. It will produce CurrentItem as a variable. Provide %ExcelData.Columns% in Value to iterate. I have renamed
I have renamed Store into property as ‘ExcelColumns‘.
- Inside the Apply to each loop, add an Append line to text action that adds a new line to the text value. Please ensure that you provide the parameters listed below.
- Original text: Click on {x} -> Add TableBody variable.
- Line to append: Insert the code below.
<th>%ExcelColumns%</th>Here, in the above code, replace the Excelcolumns variable with your variable produced.
- Now, outside of the loop, add one more ‘Append line to text’ action to add table rows. Provide the parameters.
- Original text: Take the TableBody variable.
- Line to append: Add </tr> to close the table row.
I have renamed the variables produced from this action to TableBody because it is appending values to the original table. Click on the Save button.
- Now, add another “For each” loop to iterate through the Excel data items. Set the parameters.
- Value to iterate: Click on {X} to add an Excel data variable.
- Store into: The values will be stored in the CurrentItem variable each time the flow runs.
- Inside this loop, drag and drop the “Append line to text” action that appends the table row tag to the text.
- Original text: Take the TableBody variable.
- Line to append: Add <tr> to close the table row.
- Inside the loop, add one more “For each” loop action that will iterate through Excel data columns. Save the action.
- Value to iterate: Select {x} -> Choose to add %ExcelData.Columns% variable.
- Store into: I have renamed it to Current Columns.
- Inside this “For each” loop, add a drag and drop the “Append line to text” action that will add the current columns to the table data.
- Original text: Take TableBody varaible.
- Line to append: Insert the below expression.
<td>%CurrentItem[CurrentColumns]%</td>I will merge the variables produced in this action with the TableBody variable. So, I have renamed it to ‘TableBody’.
- Outside the CurrentColumns “For each” loop, drag and drop an “Append line to text” action to close the table row produced from the previous step.
- Original text: Take the TableBody variable.
- Line to append: Add </tr> to close the table row.
I have also renamed the variables produced to TableBody.
- Then, outside of the last “For each” loop, add an “Append line to text” action to append the closed version of the HTML table body that has opened.
- Original text: Take the TableBody variable.
- Line to append: Insert the code below.
</table></body></html>I have renamed the variables produced from this action to TableBody. Then, click on the Save button.
- Then, to send the email of the Project details, “Launch Outlook” action in the workspace. To launch Outlook and create an instance variable of it. This action doesn’t require any parameters.
- Then, drag and drop the ‘Send email message through Outlook’ action to the workspace. Fill in all the required parameters as mentioned below.
- Outlook instance: By default, the OutlookInstance variable will be added.
- Account: Provide the Account Name from which you are sending.
- Send email message from: Here, I have selected the ‘Other mailbox‘ option because my Outlook has logged in with different accounts. If you have one account, you can choose the “Account” option.
- Send from: Provide the Account address.
- To: Enter the recipient’s Outlook address.
- Subject: Enter the Subject for the mail.
- Body: Insert the code provided below.
- Body is HTML: Toggle on the button, as the email body contains HTML table.
Body:
Hi, <br>
The table below shows the Project Details:
<br>
%TableBody%
<br>
Regards <br>
Project Management TeamThen, click on the Save button.
- On the final step, add a “Close Outlook” action that will close the Outlook tab opened on the desktop.
- Now, the flow is ready. You can see the entire flow in the screenshot below. Click on the Save icon and run the flow.
- Once the flow executes successfully, the recipient will receive an email containing the Project Details Table. Have a look at the image for your reference.
This way, you can pull the data from Excel into an HTML table and parse the table into an email body using Power Automate Desktop.
I hope you found this tutorial interesting and helpful. Please share your thoughts and suggestions in the comment section below.
Also, you may read the following PAD tutorials:
- Variables in Power Automate Desktop
- Power Automate DesktopGet Substring from String
- Power Automate Desktop Extract Data from an Excel
- Insert Row into Data Table Power Automate Desktop
After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.
