VbaFin.com

Visual Basic for Financial Professionals
   Home      HTML
Let's assume you need to distribute a report that has a table which you want to include in the body of your email.
The table looks like this:
 
Fund
Price
Change 
% Change 
Bernanke Bears 
 3.17
-0.99
-31.23
Made Off Pyramid 
 0.72
-0.76
-105.56%
Golden Socks
2.19
0.01
0.46%
Lemon Brothers
0.03
-0.05
-166.67%
Green Stone Turbo
2.18
-0.53
-24.31%

You can generate the table using the following code:

Function CreateHTML() As String
   
    Dim tmpStr As String
    tmpStr = "<table width= 50% bordercolor=#ff5500 cellspacing=0 cellpadding=3 border=1>"
    tmpStr = tmpStr & "<tbody>"
    tmpStr = tmpStr & "<tr valign=top bgcolor=#00ff55>"
    tmpStr = tmpStr & "<td width=34%><font color=#ff5500><strong>Fund</td>"
    tmpStr = tmpStr & "<td width=22% align=center><font color=#ff5500><strong>Price</td>"
    tmpStr = tmpStr & "<td width=22% align=center><font color=#ff5500><strong>Change</td>"
    tmpStr = tmpStr & "<td width=22% align=center><font color=#ff5500><strong>% Change</td></tr>"
    tmpStr = tmpStr & "<tr valign=top bgcolor=#f0f0f0>"
    tmpStr = tmpStr & "<td>Bernanke Bears</td>"
    tmpStr = tmpStr & "<td align=right>3.17</td>"
    tmpStr = tmpStr & "<td align=right><font color=red>-0.99</td>"
    tmpStr = tmpStr & "<td align=right><font color=red>-31.23</td></tr>"
    tmpStr = tmpStr & "<tr valign=top bgcolor=#00ff55>"
    tmpStr = tmpStr & "<td>Made Off Pyramid</td>"
    tmpStr = tmpStr & "<td align=right>0.72</td>"
    tmpStr = tmpStr & "<td align=right><font color=red>-0.76</td>"
    tmpStr = tmpStr & "<td align=right><font color=red>-105.56%</td></tr>"
    tmpStr = tmpStr & "<tr valign=top bgcolor=#f0f0f0>"
    tmpStr = tmpStr & "<td>Golden Socks</td>"
    tmpStr = tmpStr & "<td align=right>2.19</td>"
    tmpStr = tmpStr & "<td align=right>0.01</td>"
    tmpStr = tmpStr & "<td align=right>0.46%</td></tr>"
    tmpStr = tmpStr & "<tr valign=top bgcolor=#00ff55>"
    tmpStr = tmpStr & "<td>Lemon Brothers</td>"
    tmpStr = tmpStr & "<td align=right>0.03</td>"
    tmpStr = tmpStr & "<td align=right><font color=red>-0.05</td>"
    tmpStr = tmpStr & "<td align=right><font color=red>-166.67%</td></tr>"
    tmpStr = tmpStr & "<tr valign=top bgcolor=#f0f0f0>"
    tmpStr = tmpStr & "<td>Green Stone Turbo</td>"
    tmpStr = tmpStr & "<td align=right>2.18</td>"
    tmpStr = tmpStr & "<td align=right><font color=red>-0.53</td>"
    tmpStr = tmpStr & "<td align=right><font color=red>-24.31%</td></tr></tbody></table>"
    CreateHTML = tmpStr

End Function  

The same result can be achieved by using Excel to generate the html code for you. The following example demonstrates this assuming the above table is a Range named "FundsTable":
 
Function CreateHTML() As String
    
    
Dim ShtNW As Long, xlWbk As Workbook, fs As FileSystemObject
    Dim tmpFile As String, c As Long
    
    On Error Goto ErrHdl
    With Application
        ShtNW = .SheetsInNewWorkbook
        If ShtNW > 1
Then .SheetsInNewWorkbook = 1
        .ScreenUpdating =
False
    End With
    Range("FundsTable").Copy
    Set xlWbk = Workbooks.Add
    With Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With
    Application.CutCopyMode = False
    With ThisWorkbook
        For c = 1 To ThisWorkbook.Names("FundsTable").RefersToRange.Columns.Count
            Columns(c).ColumnWidth = .Names("FundsTable").RefersToRange.Columns(c).ColumnWidth
        Next c
    End With
    xlWbk.SaveAs Format(Now, "mmddyyyyhhmmss") & ".htm", xlHtml
    tmpFile = xlWbk.Name
    ThisWorkbook.Activate
    xlWbk.Close
False
    Set fs = New FileSystemObject
    With fs
        With .OpenTextFile(tmpFile, ForReading)
            GenrateHTML = .ReadAll
            .Close
        End With
       .DeleteFile tmpFile, True
    End With
 
ErrHdl:
    If ShtNW > 1 Then Application.SheetsInNewWorkbook = ShtNW
    Set fs = Nothing
    Set xlWbk = Nothing
    If Err.Number  Then MsgBox Err.Description, vbCritical, "Generate HTML"
 
End Function