Menu
I am using VBA to create a table in an outlook email. I have figured out how to generate the table, but my problem is that I need to dynamically adjust the number of rows in the table. For some emails, there will be two rows of data, for others there will be three, etc.
In the code below rowstocontact is a Collection. I know I want to loop through the Collection and add a row for each item in the collection, but I can't figure out how to insert a loop within the html code that is creating the table.
Any help is greatly appreciated!! Thanks.
bodytext = "<head><style>table, th, td {border: 1px solid gray; border-collapse:" & _
"collapse;}</style></head><body>" & _
"<table style=""width:60%""><tr>" & _
"<th bgcolor=""#bdf0ff"">Reviewee</th>" & _
"<th bgcolor=""#bdf0ff"">Manager(s)</th>" & _
"<th bgcolor=""#bdf0ff"">Project code</th>" & _
"<th bgcolor=""#bdf0ff"">Requested</th>" & _
"<th bgcolor=""#bdf0ff"">Type</th>" & _
"<th bgcolor=""#bdf0ff"">Due</th></tr><tr>" & _
"<td ""col width=10%"">" & Range("D" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("L" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("M" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("AJ" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("V" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("AK" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("AK" & rowtocontact(1)) & "</td>" & _
"</tr></Table></body>"
725
You need to split the HTML into 3 parts and have a string variable for each:
In the second section of code you can iterate through the collection (of row references) and dynamically build the table by adding <tr>...</tr> blocks for as many items as there are in your Collection.
After setting the string for 'everything after the rows' you then concatenate all three parts together to get the final HTML string.
Here is the sample code - note I added a worksheet reference (ws) as a best practice and also dropped off the final <td> per row as it seemed like a duplicate of the value on column AK for which you did not have a header. You can adjust as suits anyway:
Option Explicit
Sub CreateEmailHtml()
Dim ws As Worksheet
Dim coll As New Collection
Dim lngCounter As Long
Dim strBeforeRows As String
Dim strRows As String
Dim strAfterRows As String
Dim strAll As String
' get a worksheet reference
Set ws = Sheet1
' test collection
coll.Add 2
coll.Add 4
coll.Add 6
' HTML before rows
strBeforeRows = "<head><style>table, th, td {border: 1px solid gray; border-collapse:" & _
"collapse;}</style></head><body>" & _
"<table style=""width:60%""><tr>" & _
"<th bgcolor=""#bdf0ff"">Reviewee</th>" & _
"<th bgcolor=""#bdf0ff"">Manager(s)</th>" & _
"<th bgcolor=""#bdf0ff"">Project code</th>" & _
"<th bgcolor=""#bdf0ff"">Requested</th>" & _
"<th bgcolor=""#bdf0ff"">Type</th>" & _
"<th bgcolor=""#bdf0ff"">Due</th></tr>"
' iterate collection
strRows = ""
For lngCounter = 1 To coll.Count
strRows = strRows & "<tr>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("D" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("L" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("M" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("AJ" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("V" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("AK" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "</tr>"
Next lngCounter
' HTML after rows
strAfterRows = "</table></body>"
' final HTML - concatenate the 3 string variables
strAll = strBeforeRows & strRows & strAfterRows
Debug.Print strAll
End Sub
So, given this sample data:
You get this HTML as an output - it is formatted nicely through use of the Tidy button in the stack snippet editor for readability:
<head>
<style>
table,
th,
td {
border: 1px solid gray;
border-collapse: collapse;
}
</style>
</head>
<body>
<table style="width:60%">
<tr>
<th bgcolor="#bdf0ff">Reviewee</th>
<th bgcolor="#bdf0ff">Manager(s)</th>
<th bgcolor="#bdf0ff">Project code</th>
<th bgcolor="#bdf0ff">Requested</th>
<th bgcolor="#bdf0ff">Type</th>
<th bgcolor="#bdf0ff">Due</th>
</tr>
<tr>
<td "col width=10%">foo2</td>
<td "col width=10%">bar2</td>
<td "col width=10%">baz2</td>
<td "col width=10%">quux2</td>
<td "col width=10%">qux2</td>
<td "col width=10%">quuux2</td>
</tr>
<tr>
<td "col width=10%">foo2</td>
<td "col width=10%">bar2</td>
<td "col width=10%">baz2</td>
<td "col width=10%">quux2</td>
<td "col width=10%">qux2</td>
<td "col width=10%">quuux2</td>
</tr>
<tr>
<td "col width=10%">foo6</td>
<td "col width=10%">bar6</td>
<td "col width=10%">baz6</td>
<td "col width=10%">quux6</td>
<td "col width=10%">qux6</td>
<td "col width=10%">quuux6</td>
</tr>
</table>
</body>HTH
60
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
