Sending Excel Tables with Cell Colours through Outlook using Power Automate Desktop.
Introduction
In this blog post, we will explore how to use Power Automate Desktop to send an Excel table, including cell colours, through Outlook. This can be particularly useful when you want to share data in a visually appealing and easy-to-understand format.
Prerequisites
Before we start, make sure you have the following:
- Power Automate Desktop installed on your machine.
- An Excel file with a coloured table that you want to send.
- Microsoft Outlook set up on your machine.
Input File:
The input file looks like as shown in below image.
Steps:
Step 1: Launch Power Automate Desktop
Start Power Automate Desktop and create a new flow.
Step 2: Run VBScript Action
Use the Run VBScript action and create the table of the excel as a Html Table.
VBscript Code:
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Desktop\Power Automate Desktop\Practice\Folder A\Input.xlsx")
Set objWorksheet = objWorkbook.Worksheets(1)
' Define variables to store HTML content
Dim htmlBody
htmlBody = "<html><body><table border='1'>"
' Loop through rows and columns to read data and cell colors from Excel
For row = 1 To objWorksheet.UsedRange.Rows.Count
htmlBody = htmlBody & "<tr>"
For col = 1 To objWorksheet.UsedRange.Columns.Count
' Get cell value
cellValue = objWorksheet.Cells(row, col).Value
' Get cell background color
cellColor = objWorksheet.Cells(row, col).Interior.Color
' Convert Excel color to HTML color
htmlColor = RGBToHTMLColor(cellColor)
' Append cell with color to HTML
htmlBody = htmlBody & "<td style='background-color:" & htmlColor & "'>" & cellValue & "</td>"
Next
htmlBody = htmlBody & "</tr>"
Next
htmlBody = htmlBody & "</table></body></html>"
' Close Excel objects
objWorkbook.Close False
objExcel.Quit
' Output HTML body
WScript.Echo htmlBody
' Function to convert RGB color to HTML color
Function RGBToHTMLColor(rgb)
Dim red, green, blue
red = (rgb Mod 256)
green = ((rgb \ 256) Mod 256)
blue = ((rgb \ 256 \ 256) Mod 256)
RGBToHTMLColor = "#" & Right("0" & Hex(red), 2) & Right("0" & Hex(green), 2) & Right("0" & Hex(blue), 2)
End Function
Step 3: Launch Outlook
Now, use the Launch Outlook to launch the Outlook.
Step 4: Send Email message through Outlook
Now, use the Send Email message through outlook action to create a new email. Fill in the necessary details like the recipient’s email address, subject, etc. and in the body give the VBScript generated output variable in the body.
Power Automate Desktop Code and Image:
Flow Screenshot:
CODE:
Scripting.RunVBScript.RunVBScript VBScriptCode: $'''Set objExcel = CreateObject(\"Excel.Application\")
Set objWorkbook = objExcel.Workbooks.Open(\"C:\\Desktop\\Power Automate Desktop\\Practice\\Combine PDFS\\Folder A\\Input.xlsx\")
Set objWorksheet = objWorkbook.Worksheets(1)
\' Define variables to store HTML content
Dim htmlBody
htmlBody = \"<html><body><table border=\'1\'>\"
\' Loop through rows and columns to read data and cell colors from Excel
For row = 1 To objWorksheet.UsedRange.Rows.Count
htmlBody = htmlBody & \"<tr>\"
For col = 1 To objWorksheet.UsedRange.Columns.Count
\' Get cell value
cellValue = objWorksheet.Cells(row, col).Value
\' Get cell background color
cellColor = objWorksheet.Cells(row, col).Interior.Color
\' Convert Excel color to HTML color
htmlColor = RGBToHTMLColor(cellColor)
\' Append cell with color to HTML
htmlBody = htmlBody & \"<td style=\'background-color:\" & htmlColor & \"\'>\" & cellValue & \"</td>\"
Next
htmlBody = htmlBody & \"</tr>\"
Next
htmlBody = htmlBody & \"</table></body></html>\"
\' Close Excel objects
objWorkbook.Close False
objExcel.Quit
\' Output HTML body
WScript.Echo htmlBody
\' Function to convert RGB color to HTML color
Function RGBToHTMLColor(rgb)
Dim red, green, blue
red = (rgb Mod 256)
green = ((rgb \\ 256) Mod 256)
blue = ((rgb \\ 256 \\ 256) Mod 256)
RGBToHTMLColor = \"#\" & Right(\"0\" & Hex(red), 2) & Right(\"0\" & Hex(green), 2) & Right(\"0\" & Hex(blue), 2)
End Function''' ScriptOutput=> htmlBody ScriptError=> ScriptError
Outlook.Launch Instance=> OutlookInstance
Outlook.SendEmailThroughOutlook.SendEmail Instance: OutlookInstance Account: $'''vishnuvardhanreddy@acmet.com''' SendTo: To_Email Body: htmlBody IsBodyHtml: True IsDraft: False
Output Email:
Conclusion
And that’s it! With Power Automate Desktop, you can easily automate the process of sending coloured Excel tables through Outlook. This not only saves time but also ensures that your data is presented in a visually appealing way.
Comments
-
Sending Excel Tables with Cell Colours through Outlook using Power Automate Desktop.
Nice article.But just wanted to confirm that Vb scripting has been deprecated from latest windows version. Tried running vb script with latest windows 10 and iam not able to execute the script using PAD.
-
Sending Excel Tables with Cell Colours through Outlook using Power Automate Desktop.
good article @VishnuReddy1997
*This post is locked for comments