我正在使用 VBA 在 Outlook 电子邮件中创建一个表。我已经弄清楚如何生成表,但我的问题是我需要动态调整表中的行数。对于某些电子邮件,将有两行数据,对于其他电子邮件,将有三行,依此类推。
在下面的代码中rowstocontact
is a Collection
。我知道我想循环Collection
并为集合中的每个项目添加一行,但我不知道如何在创建表的 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"">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>"
您需要将 HTML 分成 3 部分,并为每个部分设置一个字符串变量:
在代码的第二部分中,您可以迭代(行引用)集合并通过添加动态构建表<tr>...</tr>
可以容纳尽可能多的项目Collection
.
将字符串设置为“行后的所有内容”后,您可以将所有三个部分连接在一起以获得最终的 HTML 字符串。
这是示例代码 - 请注意我添加了工作表参考(ws
)作为最佳实践,也落入了决赛<td>
每行,因为它看起来像是列上值的重复AK
您没有标题。无论如何,您可以根据需要进行调整:
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
因此,给定以下示例数据:
![enter image description here](https://i.stack.imgur.com/jnnHb.png)
你得到这个 HTML 作为输出 - 它通过使用很好地格式化Tidy
堆栈片段编辑器中的按钮以提高可读性:
<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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)