Skip to main content

Notifications

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.

 

VishnuReddy1997_0-1720500386369.png

 

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:

 

VishnuReddy1997_1-1720500509840.png

 

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:

 

VishnuReddy1997_2-1720500949532.png

 

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

*This post is locked for comments

  • AL-16100634-0 Profile Picture AL-16100634-0
    Posted at
    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.

  • phanip Profile Picture phanip
    Posted at
    Sending Excel Tables with Cell Colours through Outlook using Power Automate Desktop.

    good article @VishnuReddy1997