Excel Automation – Coding Basics

Bottom Line:

Learn to Add CSS to the HTML Report and print to the Gmail File attachment, which gives additional functionality to add symbols (such as movement indicators) and proper Table boarders.

Note: With this option, the entire report can be print to the file and attached in the Gmail. The Gmail body can be minimised.

This is part of the series where we are going to cover:

Files for Download:

The below is the sample VBA template, which can be modified/updated to cater for your needs. Also, the Range can updated to remove/include specific columns/set.

vba-code-for-html-css-portfolio-report-to-gmail Download

Given, there are no ActiveX Buttons placed in the workbook, the code to be launched from:

Visual Basic Window (Alt + F11) Run Run Sun/UserForm (F5).

The code can be found under Sheet1 (Portfolio).

Capture

Code:

Sub VD()

Dim objMessage

'# Assign CDO Obj to Variable
    Set objMessage = CreateObject("CDO.Message")

'# Define variables for Green Red Blue colour Span tags
Dim a, b, c, f As String

'# this is to refresh the stocks data from external source
    ThisWorkbook.RefreshAll
  
'# Assign Green Red Blue colour Span tags (which creates issue if defined in the code itself)
    a = ThisWorkbook.Worksheets(2).Range("F5").Value
    b = ThisWorkbook.Worksheets(2).Range("F6").Value
    c = ThisWorkbook.Worksheets(2).Range("G5").Value


'# Assign Gain/Loss summary related variables

    ' Gain
    GCount = ThisWorkbook.Worksheets(2).Range("B5").Value
    GAmount = ThisWorkbook.Worksheets(2).Range("B7").Value
    Gain = ThisWorkbook.Worksheets(2).Range("B6").Value
    GPerc = ThisWorkbook.Worksheets(2).Range("B8").Value
    GTotal = ThisWorkbook.Worksheets(2).Range("B9").Value
    
    ' Loss
    LCount = ThisWorkbook.Worksheets(2).Range("C5").Value
    LAmount = ThisWorkbook.Worksheets(2).Range("C7").Value
    Loss = ThisWorkbook.Worksheets(2).Range("C6").Value
    LPerc = ThisWorkbook.Worksheets(2).Range("C8").Value
    LTotal = ThisWorkbook.Worksheets(2).Range("C9").Value


    ' Total
    STotal = GCount + LCount
    
    ' Day Change
    DayChange = ThisWorkbook.Worksheets(2).Range("F2").Value
    DayChangePerc = Round(ThisWorkbook.Worksheets(2).Range("G2").Value * 100, 2)
    
' # Assign value for Arrows to variables
    darrow = ThisWorkbook.Worksheets("Summary").Range("F8").Value
    uarrow = ThisWorkbook.Worksheets("Summary").Range("F9").Value

'# Define Range, HTML and Loop Variables
Dim rng, rg3, rng4 As Range, cell As Range, HtmlContent, HtmlContent1, FinalHtmlContent As String, i As Long, j As Long, i1 As Long, j1 As Long

'# Row Count in the Sheet1
    rc = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

'# Assign Range variables from Sheet1 and Sheet2
    Set rng = ThisWorkbook.Worksheets(1).Range("A1:J" & rc)
    Set rng3 = ThisWorkbook.Worksheets(2).Range("A1:G2")
    Set rng4 = ThisWorkbook.Worksheets(2).Range("A4:C9")

'--------------------------------------------------------
' @@@ ** HTML File Section** @@@
'--------------------------------------------------------


' # Provide the path of the file, where report to be printed and which to be attached to Gmail
Dim sFile As Variant

   sFile = "C:\Users\venka\OneDrive\Desktop\Blog\Stocks Automation\Stocks Automation (Part-III) Add CSS to the HTML Report and print to the Gmail File Attachment\Demo portfolio.html"

'---------------------------------------------------------
' @@@ ** Beginning of CSS ** @@@
'---------------------------------------------------------

  'Open up the temp HTML File and define the CSS Attributes and Logic.
  Open sFile For Output As #1
  Print #1, "<html>"
  Print #1, "<head>"
  Print #1, "<style type=""text/css"">"
  Print #1, "table {font-size: 10px;font-family: Arial, Helvetica, sans-serif; border-collapse: collapse}" 'Optimum, Helvetica, sans-serif
  Print #1, "tr {border-bottom: thin solid #A9A9A9;border-top: thin solid #A9A9A9;}"
  Print #1, "tr:nth-child(even) {background-color: #f2f2f2;}"
  Print #1, "td {padding: 4px; margin: 3px; text-align: justify; justify;border-left: 1px solid #000;border-right: 1px solid #000;▼}"
  Print #1, "th { background-color: #A9A9A9; color: #FFF; font-weight: bold; font-size: 28px; text-align: center;}"
  Print #1, "tr:first-child {width: 25%;background-color: #4CAF50; color: white;}"
  Print #1, "</style>"
  Print #1, "</head>"
  Print #1, "<body>"

    '---------------------------------------------------------
    ' @@@ ** Define Sheet1 (Portfolio) Summary ** @@@
    '---------------------------------------------------------

' # Activate Sheet1
    ThisWorkbook.Worksheets(1).Activate

' @@ # Create Portfolio view as  HTML Table

    ' # Adding HTML Table Tag
    HtmlContent = "<h4><u> Demo Portfolio:</u></h4><br><table>"
    
' # Outer loop for rows to create Row-wise Data for all the columns
For i = 1 To rng.Rows.Count
    
    ' # Adding HTML Row Tag
    HtmlContent = HtmlContent & "<tr>"
    
    ' # Inner loop for Colums to join columns Data to create rows
    For j = 2 To rng.Columns.Count
    
        ' # Logic to add Color code for the data in specific columns excluding Header
        If (j = 8 Or j = 9 Or j = 10) And i > 1 Then
    
            ' # Caluculating Percentage and adding "%" symbol where Applicable/Required
            If (j = 8 Or j = 10) Then
                d = Round(Cells(i, j).Value * 100, 2) & " %"
            Else
                d = Cells(i, j).Value
            End If
            
            ' # Add GREEN Text Lable if Value more than or Equal "0" Else Add RED. Add respective ARROWs
            If Cells(i, j).Value >= 0 And (j = 8 Or j = 10) Then
                HtmlContent = HtmlContent & "<td>" & a & d & uarrow & c & "</td>"
            ElseIf Cells(i, j).Value <= 0 And (j = 8 Or j = 10) Then
                HtmlContent = HtmlContent & "<td>" & b & d & darrow & c & "</td>"
            ElseIf Cells(i, j).Value <= 0 And (j = 9) Then
                HtmlContent = HtmlContent & "<td>" & b & d & c & "</td>"
            Else
                HtmlContent = HtmlContent & "<td>" & a & d & c & "</td>"
            End If
         
        ' # Header -- Adding BLUE Text Lable
        ElseIf i = 1 Then
            HtmlContent = HtmlContent & "<td>" & f & Cells(i, j).Value & c & "</td>"
        Else
            HtmlContent = HtmlContent & "<td>" & Cells(i, j).Value & "</td>"
        End If
    
    ' #  Inner Loop
    Next
        
        ' # HTML Row closing Tag
        HtmlContent = HtmlContent & "</tr>"
        
' # Outer Loop
Next

    ' # HTML Table closing Tag
    HtmlContent = HtmlContent & "</table>"

    '--------------------------------------------------------
    ' @@@ Define Sheet2 (Overview) Summary @@@
    '--------------------------------------------------------

' # Activate Sheet2
ThisWorkbook.Worksheets(2).Activate

' @@ # Create Summary view as  HTML Table

' # Adding HTML Table Tag
    HtmlContent1 = "<h4><u> Overall Summary:</u></h4><br><table>"

' # Outer loop for rows to create Row-wise Data for all the columns
For i1 = 1 To 2
    
    ' # Adding HTML Row Tag
    HtmlContent1 = HtmlContent1 & "<tr>"
    
    ' # Inner loop for Colums to join columns Data to create rows
    For j1 = 1 To 7

        ' # Logic to add Color code for the data in specific columns excluding Header
        If (j1 = 3 Or j1 = 4 Or j1 = 6 Or j1 = 7) And i1 > 1 Then
            
            ' # Caluculating Percentage and adding "%" symbol where Applicable/Required
            If (j1 = 4 Or j1 = 7) Then
                d = Trim(Round(ActiveSheet.Cells(i1, j1).Value * 100, 2)) & " %"
            Else
                d = ActiveSheet.Cells(i1, j1).Value
            End If
    
            ' # Add GREEN Text Lable if Value more than or Equal "0" Else Add RED. Add respective ARROWs
            If Cells(i1, j1).Value >= 0 And (j1 = 4 Or j1 = 7) Then
                HtmlContent1 = HtmlContent1 & "<td>" & a & d & uarrow & c & "</td>"
            ElseIf Cells(i1, j1).Value <= 0 And (j1 = 4 Or j1 = 7) Then
                HtmlContent1 = HtmlContent1 & "<td>" & b & d & darrow & c & "</td>"
            ElseIf Cells(i1, j1).Value <= 0 And (j1 = 3 Or j1 = 6) Then
                HtmlContent1 = HtmlContent1 & "<td>" & b & d & c & "</td>"
            Else
                HtmlContent1 = HtmlContent1 & "<td>" & a & d & c & "</td>"
            End If
            
        ' # Header -- Adding BLUE Text Lable
        ElseIf i1 = 1 Then
            HtmlContent1 = HtmlContent1 & "<td>" & f & ActiveSheet.Cells(i1, j1).Value & c & "</td>"
        Else
            HtmlContent1 = HtmlContent1 & "<td>" & ActiveSheet.Cells(i1, j1).Value & "</td>"
        End If
    
    ' #  Inner Loop
    Next
        
        ' # HTML Row closing Tag
        HtmlContent1 = HtmlContent1 & "</tr>"
        
' #  Outer Loop
Next
    
    ' # HTML Table closing Tag
    HtmlContent1 = HtmlContent1 & "</table>"

'--------------------------------------------------------

' # Closing Tags for HTML
  Print #1, HtmlContent1
  Print #1, "<br>"
  Print #1, "<br>"
  Print #1, HtmlContent
  Print #1, "<br>"
  Print #1, "<br>"
  Print #1, "</body>"
  Print #1, "</html>"
  Close

'---------------------------------------------------------
' @@@ ** End of CSS ** @@@
'---------------------------------------------------------

'---------------------------------------------------------
' @@ Assign Gmail Parameters
'---------------------------------------------------------

'# Assign Subject (Date & Time are the functions that fetch current Date and Time respectively)
objMessage.Subject = "Update: Demo Portfolio Report | " & Date & " | " & Time

'# Assign From and To Address
objMessage.From = "YOUR GMAIL" '@@ Gmail from which you willing to send email '@@ (Should be enabled with Less Secure App Access)
objMessage.To = "To Address" '@@ Single/List

    If DayChange > 0 Then
        ColorCode = a
        arrow = uarrow
    Else
        ColorCode = b
        arrow = darrow
    End If
    

'# Define Final HTML Body variable
FinalHtmlContent = "Hi Venkat," & "<br>" & "<h4>" & a & GCount & " stocks are gaining" & " out of " & STotal & " stocks" & c & " with total " & a & "Profit" & c & " of " & a & "<u>" & Gain & " (" & GPerc & " %) " & "</u>" & c & " on " & a & "total" & c & " of " & a & "<u>" & GAmount & " (" & GTotal & " %)" & "</u>" & c & "</h4>" _
& "<h4>" & b & LCount & " stocks are losing" & " out of " & STotal & " stocks" & c & " with total " & b & "Loss" & c & " of " & b & "<u>" & Loss & " (" & LPerc & " %) " & "</u>" & c & " on " & b & "total" & c & " of " & b & "<u>" & LAmount & " (" & LTotal & " %)" & "</u>" & c & "</h4>" _
& "<b>" & "Today's Profit/Loss is: " & ColorCode & DayChange & c & " (" & ColorCode & DayChangePerc & " %" & c & ") " & ColorCode & arrow & c & "</b>" & "<br>" & "<br>" & "<br>"

'# Assign Final HTML Body variable to Gmail HTML Body
objMessage.HTMLBody = FinalHtmlContent

'# Attach the file that we printed output to
objMessage.AddAttachment "E:\Demo portfolio.html"


'# Define Gmail Servier Configurations
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" 'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "GMAIL ID" '@@ same as in From Address (Your UserID on the SMTP server)
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*******" 'Your password on the SMTP server '@@ Replace ******* with your password
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 'Server port (typically 25)
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True 'Use SSL for the connection (False or True)
objMessage.Configuration.Fields.Update

'# Send Gmail Command
objMessage.Send

'# Save Workbook
ThisWorkbook.Save


End Sub

Samples:

Below are the Web and Mobile versions of the Gmail reports respectively.

Web Version:

This slideshow requires JavaScript.

Mobile Version:

This slideshow requires JavaScript.

For any challenges regarding the code or any part of the post, do Contact Us.

Happy Learnings!

Tag: Excel Automation

Stocks Automation (Part-III): Add CSS to the HTML Report and print to the Gmail File Attachment