Inserting a table into HtmlBody of email in vba

[Tablecloth98]

I'm trying to insert a table whose content are contained on a spreadsheet (Let's say within the range A1:D4 as an example) into an email. I've written the body out in HTMLBody format and have followed the instructions from this guide to insert the table: Convert Excel Range Into HTML Table Through VBA 2024

When I run the Macro however, it produces an error and highlights some lines of the function in red. I've copied and pasted this directly from the guide so I'm not sure why it isn't working. Does anybody know why it's producing the error?

 

[Domenic]

It looks like copying and pasting the code from that page kinda messed it up a bit. Try copying and pasting the following code instead...

'Following function converts Excel range to HTML table
Public Function ConvertRangeToHTMLTable(rInput As Range) As String
'Declare variables
 Dim rRow As Range
Dim rCell As Range
Dim strReturn As String
'Define table format and font
 strReturn = "<Table border='1' cellspacing='0' cellpadding='7' style='border-collapse:collapse;border:none'> "
'Loop through each row in the range
For Each rRow In rInput.Rows
 'Start new html row
strReturn = strReturn & " <tr align='Center'; style='height:10.00pt'> "
For Each rCell In rRow.Cells
'If it is row 1 then it is header row that need to be bold
 If rCell.Row = 1 Then
strReturn = strReturn & "<td valign='Center' style='border:solid windowtext 1.0pt; padding:0cm 5.4pt 0cm 5.4pt;height:1.05pt'><b>" & rCell.Text & "</b></td>"
 Else
 strReturn = strReturn & "<td valign='Center' style='border:solid windowtext 1.0pt; padding:0cm 5.4pt 0cm 5.4pt;height:1.05pt'>" & rCell.Text & "</td>"
 End If
 Next rCell
 'End a row
strReturn = strReturn & "</tr>"
 Next rRow
 'Close the font tag
strReturn = strReturn & "</font></table>"
'Return html format
ConvertRangeToHTMLTable = strReturn
End Function

Hope this helsp!

 

[Tablecloth98]

That's solved it thank you so much. You've saved me hours of troubleshooting

 

[Domenic]

You're very welcome, cheers!