使用VBA动态创建HTML表格行 [英] Dynamically create HTML table rows with VBA
问题描述
我使用VBA在Outlook电子邮件中创建表格。我已经想出了如何生成表格，但我的问题是我需要动态调整表格中的行数。对于某些电子邮件，会有两行数据，其他数据将有三行，等等。
在 rowstocontact 是一个集合。我知道我想通过循环集合并为集合中的每个项目添加一行，但我无法弄清楚如何在html代码中插入循环正在创建表格。
非常感谢任何帮助！
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>管理员< / th> &安培; _
< th bgcolor =＃bdf0ff>项目代码< / th> &安培; _
< th bgcolor =＃bdf0ff>请求< / th> &安培; _
< th bgcolor =＃bdf0ff>类型< / th> &安培; _
< th bgcolor =＃bdf0ff>到期< / th>< / tr>< tr> &安培; _
< tdcol width = 10％> &安培;范围（D和rowtocontact（1））& < / TD> 中&安培; _
< tdcol width = 10％> &安培;范围（L和rowtocontact（1））& < / TD> 中&安培; _
< tdcol width = 10％> &安培;范围（M和rowtocontact（1））& < / TD> 中&安培; _
< tdcol width = 10％> &安培;范围（AJ和rowtocontact（1））& < / TD> 中&安培; _
< tdcol width = 10％> &安培;范围（V和rowtocontact（1））& < / TD> 中&安培; _
< tdcol width = 10％> &安培;范围（AK和rowtocontact（1））& < / TD> 中&安培; _
< tdcol width = 10％> &安培;范围（AK和rowtocontact（1））& < / TD> 中&安培; _
< / tr>< /表>< / body>
您需要将HTML分成3部分，每个行的一个字符串变量： 在代码的第二部分中，您可以遍历集合（行引用）并通过为 设置'位于行之后的所有内容'的字符串后，将所有三个部分连接在一起以获取最终的HTML字符串。 下面是示例代码 - 注释我添加了一个工作表引用（ 您将此HTML作为输出 - 通过使用堆栈片段编辑器中的 p> 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 Any help is greatly appreciated!! Thanks.
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 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 ( So, given this sample data: You get this HTML as an output - it is formatted nicely through use of the
HTH 这篇关于使用VBA动态创建HTML表格行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
集合中的项目添加< tr> ...< / tr> 块来动态构建表格。 / code>。
ws ）作为最佳做法， < td> 每行，因为它好像是您没有的列 AK 上的值的重复一个标题。您可以随时调整：
Option Explicit
Sub CreateEmailHtml（）
Dim ws作为工作表
Dim coll作为新集合
Dim lngCounter As Long
Dim strBeforeRows As String
Dim strRows As String
Dim strAfterRows As String
Dim strAll As String
'获取工作表参考
Set ws = Sheet1
'测试集合
coll.Add 2
coll.Add 4
coll.Add 6
'行之前的HTML
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>管理员< / th> &安培; _
< th bgcolor =＃bdf0ff>项目代码< / th> &安培; _
< th bgcolor =＃bdf0ff>请求< / th> &安培; _
< th bgcolor =＃bdf0ff>类型< / th> &安培; _
< th bgcolor =＃bdf0ff>到期< / th>< / tr>
'迭代集合
strRows =
For lngCounter = 1 To coll.Count
strRows = strRows& < TR> 中
strRows = strRows& < tdcol width = 10％> &安培; ws.Range（D& coll（lngCounter））。Value& < / TD> 中
strRows = strRows& < tdcol width = 10％> &安培; ws.Range（L& coll（lngCounter））。Value& < / TD> 中
strRows = strRows& < tdcol width = 10％> &安培; ws.Range（M& coll（lngCounter））。Value& < / TD> 中
strRows = strRows& < tdcol width = 10％> &安培; ws.Range（AJ& coll（lngCounter））。Value& < / TD> 中
strRows = strRows& < tdcol width = 10％> &安培; ws.Range（V& coll（lngCounter））。Value& < / TD> 中
strRows = strRows& < tdcol width = 10％> &安培; ws.Range（AK& coll（lngCounter））。Value& < / TD> 中
strRows = strRows& < / TR> 中
Next lngCounter
'行后的HTML
strAfterRows =< / table>< / body>
'final HTML - 连接3个字符串变量
strAll = strBeforeRows& strRows& amp; strAfterRows
Debug.Print strAll
End Sub
<因此，给出这个样本数据：
Tidy 按钮进行格式化，以便读取：
< head> <风格> table，th，td {border：1px solid gray;边界崩溃：崩溃; }< / style>< / head>< body> < table style =width：60％> < TR> < th bgcolor =＃bdf0ff> Reviewee< / th> < th bgcolor =＃bdf0ff> Manager（s）< / th> < th bgcolor =＃bdf0ff>项目代码< / th> < th bgcolor =＃bdf0ff>请求< / th> < th bgcolor =＃bdf0ff>类型< / th> < th bgcolor =＃bdf0ff>由于< / th> < / TR> < TR> < tdcol width = 10％> foo2< / td> < tdcol width = 10％> bar2< / td> < tdcol width = 10％> baz2< / td> < tdcol width = 10％> quux2< / td> < tdcol width = 10％> qux2< / td> < tdcol width = 10％> quuux2< / td> < / TR> < TR> < tdcol width = 10％> foo2< / td> < tdcol width = 10％> bar2< / td> < tdcol width = 10％> baz2< / td> < tdcol width = 10％> quux2< / td> < tdcol width = 10％> qux2< / td> < tdcol width = 10％> quuux2< / td> < / TR> < TR> < tdcol width = 10％> foo6< / td> < tdcol width = 10％> bar6< / td> < tdcol width = 10％> baz6< / td> < tdcol width = 10％> quux6< / td> < tdcol width = 10％> qux6< / td> < tdcol width = 10％> quuux6< / td> < / TR> < / table>< / body> 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. 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>"
<tr>...</tr> blocks for as many items as there are in your Collection.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
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>
