HTML Email

Exercise Solution

Step 1 - We start by creating a Function to import the table from the worksheet in HTML format.

1. We define the output data type of the function

   Function TableHTML()as String
   

2. We declare the variables

   Dim LastColumn As Range
   Dim LastLine As Range
   Dim i As Range
   Dim j As Range
   Dim TabHtml As String
   

3. The TabHtml variable will be a "String" with the table contents in HTML language. We will add the data to this variable as the function is executed, starting with the opening tag of the HTML table

   TabHtml = "<table>"
   

4. We establish the range for the first column of the table

   Set LastLine = Range("A2", Range("A2").End(xlDown))
   

5. We create a For Each loop that will run through every cell within the range

   For Each i In LastLine
   

6. We create a conditional If to add a style if it's the first cell of the table.

   If i = Range("A2") Then
       TabHtml = TabHtml & "<tr style=""color:green;font-size:16px;"">"
   Else
       TabHtml = TabHtml & "<tr>"
   End If
   

7. We define the line interval based on the counter position of the previous loop

   Set LastColumn = Range(i, i.End(xlToRight))
   

8. We create a second loop inside the first one that will add the values of all the columns of a given row to the variable TabHtml

   For Each j In LastColumn
       TabHtml = TabHtml & "<td>" & j.Value & "</td>"
   Next j
   

9. We closed the line tag in HTML and started the next

       TabHtml = TabHtml & "</tr>"
   Next i
   

10. Once the loops are complete, we close the table and assign the result of the function to the HTML table (return TableHTML)

    TabHtml = TabHtml & "</table>"
    
    TaBeLaHTML = TabHtml
    

Step 2 - Now that we have the table formatted and ready to be used in an email, we will create a subroutine for sending.

1. We declare the variables

   Dim OlOut As Object
   Dim OlMail As Object
   Dim Result As String
   

2. We defined the variable OlOut as OutLook and OlMail as the creation of an email in Outlook

   Set OlOut = CreateObject("OutLook.Application")
   Set OlMail = OlOut.CreateItem(0)
   

3. We define the type of email, body text, title, the recipient and we send (note that for the email body we call the function we created previously).

   With OlMail
       .Bodyformat = 2
       .Display
       .HTMLbody = "<p>Greetings,</p>" & "The table below is updated with the health insurance quotes by age group.<br><br>" & TaBeLaHTML & .HTMLbody
       .Subject = "Email HTML"
       '.To = "JohnDoe@gmail.com"
       '.Attachments.add "D:\Documents\Quotes.xlsm"
       '.Send
   
   End With
   

Be careful:It is very common to send an email containing errors. Check your code very carefully before adding ".Send"

Consolidated answer

    Sub Solution()

    Dim OlOut As Object
    Dim OlMail As Object
    Dim Result As String

    Set OlOut = CreateObject("OutLook.Application")
    Set OlMail = OlOut.CreateItem(0)

    With OlMail
        .Bodyformat = 2
        .Display
        .HTMLbody = "<p>Greetings,</p>" & "The table below is updated with the health insurance quotes by age group.<br><br>" & TaBeLaHTML & .HTMLbody
        .Subject = "Email HTML"
        '.To = "fulano@gmail.com"
        '.Attachments.add "D:\Documents\Cotacao.xlsm"
        '.Send

    End With

    End Sub

    Function TableHTML()

    Dim LastColumn As Range
    Dim LastLine As Range
    Dim i As Range
    Dim j As Range
    Dim TabHtml As String

    TabHtml = "<table>"

    Set LastLine = Range("A2", Range("A2").End(xlDown))

    For Each i In LastLine
        If i = Range("A2") Then
            TabHtml = TabHtml & "<tr style=""color:green;font-size:16px;"">"
        Else
        TabHtml = TabHtml & "<tr>"
        End If

        Set LastColumn = Range(i, i.End(xlToRight))

        For Each j In LastColumn
            TabHtml = TabHtml & "<td>" & j.Value & "</td>"

        Next j

        TabHtml = TabHtml & "</tr>"

    Next i

    TabHtml = TabHtml & "</table>"

    TableHTML = TabHtml

    End Function