Sending Excel Tables with Cell Colours through Out...

VishnuReddy1997 · ‎07-09-2024

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.

phanip · ‎07-10-2024

good article @VishnuReddy1997

AjinkyaRPA2023 · ‎07-16-2024

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.