VBA 在电子邮件的 HTMLBody 中生成行，具体取决于带值的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22230095/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA generating lines inside HTMLBody of an Email depending on the number of rows with value
提问by user3271518
So I get a request to order statements and I have a macro that automatically orders them but now I want the same macro to send the email out at the end. I am at the 70% solution but I am stuck at one critical part. I don't get a set number or statements to order daily some days its 2 some its 20.
所以我收到了一个订购语句的请求，我有一个自动订购它们的宏，但现在我想要相同的宏在最后发送电子邮件。我处于 70% 的解决方案，但我被困在一个关键部分。我没有固定的数字或报表来每天订购，有时是 2 天，有时是 20 天。
Question: How do I generate a line of text in the body of an email, dependent on the number of rows that have a value.
问题：如何根据具有值的行数在电子邮件正文中生成一行文本。
Here is what I have so far:
这是我到目前为止所拥有的：
Sub Email()
Dim OutApp As Object
Dim OutMail As Object
Dim Email As String
Dim ws As Worksheet
Set ws = Worksheets("Data")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
K = 2
Do While ws.Cells(K, 1) <> "" ' While column A has cells with data do
' this matches 'Name Of' & cell value and puts it in column E (E1=Name of Joe)
ws.Cells(K, 5) = "Name Of " & ws.Cells(K, 1).Value
K = K + 1
Loop
Email = "Hello, <br><br>" & _
"The following Statements were ordered today: <br><br>" & _
'===================================================================
"<br>" & ws.Cells(k, 5).Value & _
' I am trying to get this line to be generated depending on how many
' rows have data in column A so if there are 5 names there are 5 lines
'==================================================================
"<br><br> Thank you." & _
"<br><br><br> <i> Call if you have any questions </i>"
With OutMail
.to = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "Statements Ordered"
.HTMLBody = Email
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
So I am looking to replace the line of "<br>" & ws.Cells(k, 5).Value & _because that just generates the last row with data because of the last value of K.
因此，我希望替换行，"<br>" & ws.Cells(k, 5).Value & _因为由于 K 的最后一个值，它只会生成带有数据的最后一行。
I tried to insert the do while ws.cells(k, 1) <> ""but no Joy with that to work. I looked into creating my own function but that was way over my head.
我试图插入do while ws.cells(k, 1) <> ""但没有 Joy 来工作。我考虑过创建自己的函数，但这超出了我的想象。
Clarification:
澄清：
The current email looks like this
当前电子邮件看起来像这样
Hello,
The following Statements were ordered today:
Name of Joe
Name of Bob
Name of Billy
Thank you.
Call if you have any questions.
What I am trying to do is have VBA look at Column A and see how many rows there are with data and that generates the line Name of & the value of the cell
我想要做的是让 VBA 查看 A 列并查看有多少行数据并生成该行 Name of & the value of the cell
I also added comments to the code.
我还在代码中添加了注释。
回答by
This will loop through everything in column 5, and put all data into the variable 'myvalue'.
这将遍历第 5 列中的所有内容，并将所有数据放入变量“myvalue”中。
This should be enough to get you started.
这应该足以让您入门。
btm = ws.Cells(Rows.Count, 5).End(xlUp).Row
for i = 1 to btm
myvalue = myvalue & "<br>" & ws.Cells(i, 5).Value
next i
Email = "Hello, <br><br>" & _
"The following Statements were ordered today: <br><br>" & _
"<br>" & myvalue & _
"<br><br> Thank you." & _
"<br><br><br> <i> Call if you have any questions </i>"
