Power Community

Power Community

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:DesktopPower Automate DesktopPracticeFolder AInput.xlsx")
Set objWorksheet = objWorkbook.Worksheets(1)
' Define variables to store HTML content
Dim htmlBody
htmlBody = ""
' Loop through rows and columns to read data and cell colors from Excel
For row = 1 To objWorksheet.UsedRange.Rows.Count
    htmlBody = htmlBody & ""
    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 & ""
    Next
    htmlBody = htmlBody & ""
Next
htmlBody = htmlBody & "
" & cellValue & "
" ' 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:DesktopPower Automate DesktopPracticeCombine PDFSFolder AInput.xlsx")
Set objWorksheet = objWorkbook.Worksheets(1)
' Define variables to store HTML content
Dim htmlBody
htmlBody = ""
' Loop through rows and columns to read data and cell colors from Excel
For row = 1 To objWorksheet.UsedRange.Rows.Count
    htmlBody = htmlBody & ""
    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 & ""
    Next
    htmlBody = htmlBody & ""
Next
htmlBody = htmlBody & "
" & cellValue & "
" ' 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.

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Maplytics Optimizes Routes and Boosts Productivity for T-GREX

T-GREX, a company founded by brothers Roberto and Matteo, brings together their 30 years of experience in the precision...

More Articles Like This

- Advertisement -spot_img