VBA Code to Convert Excel Range into HTML Table

excel spreadsheet tutorial

How to Convert Excel Range into HTML Table

As a VBA developer you may frequently come cross with a requirement of sending an email through Outlook along with an HTML table inside email body. Adding an HTML table in email body is very simple task if you do manually, just copy the range in Excel and paste it on email. It is little difficult in VBA to copy Excel range into email body. To convert Excel range into HTML table, you need to play with HTML tags. Below is the code which can make your life easy

Excel Tricks

Excel VBA Code to Convert Excel Range to HTML

'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
VBA Code To Copy Excel Range as HTML Table in Outlook Email

Below Excel Macro creates an email in outlook and call the above function to add Excel Range as HTML Table in Email Body

'This function creates an email in Outlook and call the ConvertRangeToHTMLTable function to add Excel range as HTML table in Email body
Sub CreateOutlookEmail()
    'Declare variable
    Dim objMail As Outlook.MailItem
    'Create new Outlook email object
    Set objMail = Outlook.CreateItem(olMailItem)
    'Assign To
    objMail.To = "test@gmail.com"
    'Assign Cc
    objMail.CC = "test2@gmail.com"
    'Assign Subject
    objMail.Subject = "Test Email"
    'Define HTML email body
    'Tip: Here i have converted range A1:F20 of Sheet1 in HTML table, you can modify the same as per your requirement
    objMail.HTMLBody = "<P><font size='2' face='Calibri' color='black'>This is a test email</font></P>" & ConvertRangeToHTMLTable(Sheet1.Range("A1:F20"))
    'Show the email to User
    objMail.Display
    'Send the email
    'objMail.Send
    'Close the object
    Set objMail = Nothing
End Sub

It is worth to mention that you must have MS Outlook installed in your system to use this code and you also need to add Outlook reference (Microsoft Outlook XX.X Object Library) in Excel VBA from Menu Bar (Tools>References…). You can read this post to see how to add Outlook Object Library in Excel Reference.

To use this code in your Excel file, follow below steps:

1. Open an Excel file
2. Press Alt+F11
3. Insert a Module (Insert>Module) from menu bar
4. Paste the code in the module
5. Now add a shape in Excel sheet
6. Give a name to the shape like ‘Create Email’
7. Right click on the shape and select ‘Assign Macro…’

Excel Tricks

8. Select ‘CreateOutlookEmail’ from the list and click on ‘Ok’ button

Excel Tricks

9. Done

Outlook Bulk Email Tool

 

 

 

 

 

 

 

 

Hope you liked this article !!

Subscribe our blog for new amazing excel tricks.

Click to below for some more interesting tricks and learning:

Please leave your valuable comments in Comments section:

 

Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.
Comments
  • thank you I was looking for this code from sometime

  • sandeep kothari says:

    Corrrect the 1st code
    Declare the variable “rInput”.

    • excelsirji says:

      Thanks for your response Sandeep. As we checked rInput is already declared as input parameter of ConvertRangeToHTMLTable function; hence need not to be declared separately.

  • Hello – I’ve been working with this but I can’t figure out how to change the header row to bold. I’m using the function for 3 seperate tables on the same sheet and using If rCell.Row = 1 this is the first row in the sheet, not in the range? How do I select the first row in the range?

    • Hi Jason,

      Can you try to replace If rCell.Row = 1 Then with If rCell.Row - rInput.Row = 0 Then.

      Regards
      ExcelSirJi Team

  • hello, i copy and pasted your code. in the email, i got a super small table with no code. the problem is rCell.Text is not displaying the text. When i replace rCell.Text with something else, like “hello”, it works perfectly.

    • Hi Gordon,

      We are not able to reproduce the error of small text. The error seems to be more related to data that you are trying to convert in HTML Table. Can you try the same code with any other Excel file?

      Regards
      ExcelSirJi

  • Hello – this macro works perfectly thank you! However there are empty rows in my range that I am converting to a table, but when I do the macro the table does not have blank rows. Is there a way I can leave the blank cells as blank rows in the output table?

    • Hi Morgan,

      You can try following code to fix the height of the row in 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'><font size='2' face='Calibri' color='black'>  "
          'Loop through each row in the range
          For Each rRow In rInput.Rows
              'Start new html row
              strReturn = strReturn & " <tr align='Center'; style='height:13.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:13.00pt'><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:13.00pt'>" & 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

      Regards,
      Your Excel Mate

Leave a Reply

Your email address will not be published. Required fields are marked *