Create email body using HTML table

[kyndsi]

I am using an excel macro to pull data from an external application and generate an Outlook email message with the data that is generated. The macro works well, but the email message output is messy and difficult to align in a presentable fashion.

The macro pulls inventory counts for multiple items, then places them into a 2 column list in an email message. Currently, the columns are aligned using spaces to align the right column, but it is impossible to get a consistent and clean output, as spacing this way is dependent on font type and size on the end user's view.

Therefore, I would like to wrap the output in an HTML table to create a uniform and consistent layout for the email body.

The code that generates the data table is written as follows:

mail.Body = mail.Body & "Item Header Count Header" & Chr(10)
mail.Body = mail.Body & "Item 1 " & intCountArray(1)
mail.Body = mail.Body & "Item 2 " & intCountArray(2)
mail.Body = mail.Body & "Item 3 " & intCountArray(5)
mail.Body = mail.Body & "Item 4 " & intCountArray(3)
mail.Body = mail.Body & "Item 5 " & intCountArray(4)
mail.Body = mail.Body & "Total of Items " & intTotalFiles & Chr(10) & Chr(10)

The code above uses generic list items, actual items are of differing lengths.

I have seen some examples of code that create HTML tables, but none that match the format used in this macro.

 

[Nine Zero]

gimme a sec while i cook something up for you

 

[Nine Zero]

try something like this for you mail body

Sub Mail()
Dim item As Variant
Dim intCountArray(), counter As Integer
Dim mailbody, databody As String
mailbody = "< HTML>< body>< table border=1>< tr>< td>Item Header< /td>< td>Count Header< /td>< /tr>"
databody = ""
counter = 1
For Each item In intCountArray
    databody = databody & "< tr>< td>Item " & counter & "< /td>< td>" & intCountArray(counter) & "< /td>< /tr>"
Next item
mailbody = mailbody & databody & "< tr>< td>Total of Items< /td>< td>" & intTotalFiles & "< /td>< /tr>< /table>< /body>< /html>"


End Sub

EDIT: TRYING TO FIX THE ISSUE WITH MR.EXCEL SEEING THIS AS HTML FOR THE SITE LOL

 

[Domenic]

First, build your HTML code like this (note that a space has been added after each < so that the Board doesn't interpret the code as HTML code, therefore remove them from the code)...

    Dim sHTMLBody As String
    Dim i As Long
    
    sHTMLBody = "< table style=""width:100%"">"
    sHTMLBody = sHTMLBody & "< tr>"
    sHTMLBody = sHTMLBody & "< th>Item Header< /th>"
    sHTMLBody = sHTMLBody & "< th>Count Header< /th>"
    sHTMLBody = sHTMLBody & "< /tr>"
    
    For i = 1 To UBound(intCountArray)
        sHTMLBody = sHTMLBody & "< tr>"
        sHTMLBody = sHTMLBody & "< td>Item " & i & "< /td>"
        sHTMLBody = sHTMLBody & "< td>" & intCountArray(i) & "< /td>"
        sHTMLBody = sHTMLBody & "< /tr>"
    Next i
    
    sHTMLBody = sHTMLBody & "< /table>

Then assign the HTML string as follows...

mail.HTMLBody = sHTMLBody

Hope this helps!

 

[Domenic]

try something like this for you mail body

Sub Mail()
Dim item As Variant
Dim intCountArray(), counter As Integer
Dim mailbody, databody As String
mailbody = "******>"
databody = ""
counter = 1
"For Each item In intCountArray
    databody = databody & ""
Next item
mailbody = mailbody & databody & "[TABLE]
<tbody>[TR]
[TD]Item Header[/TD]
[TD]Count Header[/TD]
[/TR]
[TR]
[TD]Item " & counter & "[/TD]
[TD]" & intCountArray(counter) & "[/TD]
[/TR]
[TR]
[TD]Total of Items[/TD]
[TD]" & intTotalFiles & "[/TD]
[/TR]
</tbody>[/TABLE]
"""


End Sub

EDIT: TRYING TO FIX THE ISSUE WITH MR.EXCEL SEEING THIS AS HTML FOR THE SITE LOL

Add a space after each > so that the Board doesn't interpret the code as HTML code.

 

[Nine Zero]

Omg finally got it to post... thank to @Domenic ... didnt know the space trick for HTML tags thanks a bunch on that

 

[Domenic]

That's great, now the OP has two examples to look at.

Cheers!

 

[kyndsi]

Thank you very much to both of you for your replies. Your examples look to be perfect for my application. However, I should have explained better what I meant about using generic list items in my example.

The actual left column list items have unique names. I used "Item 1", etc. just for simplicity while creating my example for my post. I didn't consider that would affect the macro code, as I can see now that it does.

Actual mail body code looks like this:

[COLOR=#333333]mail.Body = mail.Body & "Inventory                          Item Count" & Chr(10)[/COLOR]
[COLOR=#333333]mail.Body = mail.Body & "Expired                           " & intCountArray(1)[/COLOR]
[COLOR=#333333]mail.Body = mail.Body & "Pending                           " & intCountArray(2)[/COLOR]
[COLOR=#333333]mail.Body = mail.Body & "Over 180 Days                 " & intCountArray(5)[/COLOR]
[COLOR=#333333]mail.Body = mail.Body & "Docs Received                  " & intCountArray(3)[/COLOR]
[COLOR=#333333]mail.Body = mail.Body & "Secondary Note                " & intCountArray(4)[/COLOR]
[COLOR=#333333]mail.Body = mail.Body & "Total of Items                   " & intTotalFiles & Chr(10) & Chr(10)[/COLOR]

Is there a way to enter a unique text item in each row, rather than the Item " & i & " counter?

My apologies for the original presentation.

 

[Domenic]

Again, remove the spaces after each < in the following code ....

    Dim vArrayIndices As Variant
    Dim vItem As Variant
    Dim vInventory As Variant
    Dim sHTMLBody As String
    Dim i As Long
    
    vArrayIndices = Array(1, 2, 5, 3, 4)
    vInventory = Array("Expired", "Pending", "Over 180 Days", "Docs Received", "Secondary Note")
    
    sHTMLBody = "< table style=""width:100%"">"
    sHTMLBody = sHTMLBody & "< tr>"
    sHTMLBody = sHTMLBody & "< th>Inventory< /th>"
    sHTMLBody = sHTMLBody & "< th>Item Count< /th>"
    sHTMLBody = sHTMLBody & "< /tr>"
    
    For Each vItem In vArrayIndices
        sHTMLBody = sHTMLBody & "< tr>"
        sHTMLBody = sHTMLBody & "< td>" & vInventory(vItem - 1) & "< /td>"
        sHTMLBody = sHTMLBody & "< td>" & intCountArray(vItem) & "< /td>"
        sHTMLBody = sHTMLBody & "< /tr>"
    Next vItem
    
    sHTMLBody = sHTMLBody & "< tr>"
    sHTMLBody = sHTMLBody & "< td>Total of Items< /td>"
    sHTMLBody = sHTMLBody & "< td>" & intTotalFiles & "< /td>"
    sHTMLBody = sHTMLBody & "< /tr>"
    
    sHTMLBody = sHTMLBody & "< /table>

Note that the index for an Array function is zero based.

Hope this helps!

 

[kyndsi]

This looks perfect. Thanks so much for your time on this.