I need to convert excel data to HTML table and send it through an email using office 365 outlook actions
how can we convert the data into HTML Table and insert in the Email body
Accepted Solutions
Hi @kiran_g
I should have observed earlier your excel file and how it looks like. Well, it would be quite complicated to make the html structure out of excel file using PAD built-in actions. I think best approach would be to use VBScript which converts the excel data to html and give the html code as output which can be used for sending email.
Like below
1. First, set variable which stores the excel file path
2. use vbscript action which execute the below vbscript
Option Explicit
Dim objExcel, objWorkbook, objWorksheet
Dim i, j, lastRow, lastCol
Dim html
' Create an Excel application object
Set objExcel = CreateObject("Excel.Application")
' Open the workbook
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFilePath%")
' Get the first worksheet
Set objWorksheet = objWorkbook.Sheets(1)
' Get the last used row and column
lastRow = objWorksheet.UsedRange.Rows.Count
lastCol = objWorksheet.UsedRange.Columns.Count
' Start the HTML document
html = "<html><body><table border='1' cellspacing='0' cellpadding='5'>"
' Loop through each cell and add it to the HTML table
For i = 1 To lastRow
html = html & "<tr>"
For j = 1 To lastCol
' Check if the cell is merged
If Not objWorksheet.Cells(i, j).MergeCells Then
' Normal cell, add to the HTML table
html = html & "<td style='" & GetCellStyle(objWorksheet.Cells(i, j)) & "'>" & objWorksheet.Cells(i, j).Text & "</td>"
Else
' Merged cell
If objWorksheet.Cells(i, j).MergeArea.Cells(1, 1).Address = objWorksheet.Cells(i, j).Address Then
' If this is the top-left cell of the merged area
html = html & "<td style='" & GetCellStyle(objWorksheet.Cells(i, j)) & "' colspan='" & objWorksheet.Cells(i, j).MergeArea.Columns.Count & "' rowspan='" & objWorksheet.Cells(i, j).MergeArea.Rows.Count & "'>" & objWorksheet.Cells(i, j).Text & "</td>"
End If
End If
Next
html = html & "</tr>"
Next
' Close the HTML table and document
html = html & "</table></body></html>"
' Print the entire HTML content to the console
WScript.Echo html
' Create an HTML file
'Dim objFSO, objFile
'Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set objFile = objFSO.CreateTextFile("C:\path\to\your\output\file.html", True)
' Write the HTML content to the file
'objFile.Write html
'objFile.Close
' Clean up
objWorkbook.Close False
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
'Set objFile = Nothing
'Set objFSO = Nothing
' Function to get the cell style as inline CSS
Function GetCellStyle(cell)
Dim style
style = ""
' Add font styles
style = style & "font-family:" & cell.Font.Name & ";"
style = style & "font-size:" & cell.Font.Size & "pt;"
If cell.Font.Bold Then style = style & "font-weight:bold;"
If cell.Font.Italic Then style = style & "font-style:italic;"
' Add cell color
If cell.Interior.ColorIndex <> -4142 Then
style = style & "background-color:" & ColorIndexToHex(cell.Interior.Color) & ";"
End If
' Add font color
style = style & "color:" & ColorIndexToHex(cell.Font.Color) & ";"
' Print the style for debugging
'WScript.Echo "Cell(" & cell.Row & "," & cell.Column & ") Style: " & style
GetCellStyle = style
End Function
' Function to convert Excel color to hex
Function ColorIndexToHex(color)
Dim hexColor
hexColor = Right("000000" & Hex(color), 6)
ColorIndexToHex = "#" & hexColor
End Function
In Run vbscript action, enable these options as well
Now use the VBScriptOutput variable which contains the html content and use that to send email
Input excel file used for demo:-
Output - email obtained
Nived N 🚀
LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs
🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌
Hi @kiran_g
I should have observed earlier your excel file and how it looks like. Well, it would be quite complicated to make the html structure out of excel file using PAD built-in actions. I think best approach would be to use VBScript which converts the excel data to html and give the html code as output which can be used for sending email.
Like below
1. First, set variable which stores the excel file path
2. use vbscript action which execute the below vbscript
Option Explicit
Dim objExcel, objWorkbook, objWorksheet
Dim i, j, lastRow, lastCol
Dim html
' Create an Excel application object
Set objExcel = CreateObject("Excel.Application")
' Open the workbook
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFilePath%")
' Get the first worksheet
Set objWorksheet = objWorkbook.Sheets(1)
' Get the last used row and column
lastRow = objWorksheet.UsedRange.Rows.Count
lastCol = objWorksheet.UsedRange.Columns.Count
' Start the HTML document
html = "<html><body><table border='1' cellspacing='0' cellpadding='5'>"
' Loop through each cell and add it to the HTML table
For i = 1 To lastRow
html = html & "<tr>"
For j = 1 To lastCol
' Check if the cell is merged
If Not objWorksheet.Cells(i, j).MergeCells Then
' Normal cell, add to the HTML table
html = html & "<td style='" & GetCellStyle(objWorksheet.Cells(i, j)) & "'>" & objWorksheet.Cells(i, j).Text & "</td>"
Else
' Merged cell
If objWorksheet.Cells(i, j).MergeArea.Cells(1, 1).Address = objWorksheet.Cells(i, j).Address Then
' If this is the top-left cell of the merged area
html = html & "<td style='" & GetCellStyle(objWorksheet.Cells(i, j)) & "' colspan='" & objWorksheet.Cells(i, j).MergeArea.Columns.Count & "' rowspan='" & objWorksheet.Cells(i, j).MergeArea.Rows.Count & "'>" & objWorksheet.Cells(i, j).Text & "</td>"
End If
End If
Next
html = html & "</tr>"
Next
' Close the HTML table and document
html = html & "</table></body></html>"
' Print the entire HTML content to the console
WScript.Echo html
' Create an HTML file
'Dim objFSO, objFile
'Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set objFile = objFSO.CreateTextFile("C:\path\to\your\output\file.html", True)
' Write the HTML content to the file
'objFile.Write html
'objFile.Close
' Clean up
objWorkbook.Close False
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
'Set objFile = Nothing
'Set objFSO = Nothing
' Function to get the cell style as inline CSS
Function GetCellStyle(cell)
Dim style
style = ""
' Add font styles
style = style & "font-family:" & cell.Font.Name & ";"
style = style & "font-size:" & cell.Font.Size & "pt;"
If cell.Font.Bold Then style = style & "font-weight:bold;"
If cell.Font.Italic Then style = style & "font-style:italic;"
' Add cell color
If cell.Interior.ColorIndex <> -4142 Then
style = style & "background-color:" & ColorIndexToHex(cell.Interior.Color) & ";"
End If
' Add font color
style = style & "color:" & ColorIndexToHex(cell.Font.Color) & ";"
' Print the style for debugging
'WScript.Echo "Cell(" & cell.Row & "," & cell.Column & ") Style: " & style
GetCellStyle = style
End Function
' Function to convert Excel color to hex
Function ColorIndexToHex(color)
Dim hexColor
hexColor = Right("000000" & Hex(color), 6)
ColorIndexToHex = "#" & hexColor
End Function
In Run vbscript action, enable these options as well
Now use the VBScriptOutput variable which contains the html content and use that to send email
Input excel file used for demo:-
Output - email obtained
Nived N 🚀
LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs
🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌
Helpful resources
Dear Community Members, As you may have read, the Power Platform communities are transitioning to a new platform and to access will be set to READ-ONLY mode during the transition. Key DatesTo ensure current learners have adequate support in the final week of the cohort, the Power Up Program Community will transition to READ-ONLY mode starting July 22nd, the platform will transition to READ-ONLY mode until July 28th. Power Apps, Power Automate, and Power Pages communities will be read-only July 16-22, 2024.During this period, members will not be able to start new threads or Kudo, Comment, or Reply to any posts, but will be able to search and review past threads or solutions. On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities. Learners will be able to sign in to the new Power Up Program community experience, starting July 29th. If you registered for community using your learnwithpowerup account, you will not receive an email, but should be able to sign in successfully if you are signed in to My hub. If you need help with your community account, please submit a request at aka.ms/PPCommSupport We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!
Since its inception in 2022, the Power Up Program has evolved based on feedback from learners and Microsoft Partners and customers. Today's Power Up learners can expect to learn the fundamentals of Microsoft Power Platform in the accelerated seven-week, video-based Power Up Maker course. Hear from Principal Program Manager, Dimpi Gandhi to discover the latest enhancements and meet the Microsoft MVPs, Rory Neary and Charlie Phipps, who partnered with the Microsoft Power Up Program to create the Power Up Maker course to guide learners to use the Microsoft Power Platform to develop custom applications, build dazzling report dashboards, or create efficiencies through automation.
The Power Up Program is a free upskilling program where nontechnical people can learn the fundamentals of Microsoft Power Platform. The Power Up Maker course is a seven-week self-paced virtual learning plan that include video-based objectives featuring Power Apps, Power BI and Power Automate. As a member of the Power Up Community, you can grow your skills and build connections. You can post questions to get help with the curriculum and hands-on exercises from experts and peers in the product boards. Check out the Community Information & Feedback board to find help or provide feedback with the community experience, and please take time to post in the Social board to tell us more about yourself. If you're new to the Power Up Program and looking for information to register. You can sign up at PowerUp.Microsoft.com.
