使用 VBA 在电子邮件中插入文本正文和 Excel 表格正文
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44972829/
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
Insert text body and excel table body in email using VBA
提问by Emily
The ideal format of the email I wish to automate. Certain parts blacked out for privacy.
我希望自动化的电子邮件的理想格式。为了隐私，某些部分被涂黑。
I am trying to create an email template for business use that can be sent using VBA because the ultimate goal is that the user can only fill in the blanks via userform therefore the text remains unchanged. I already have the userform, and have coded in such a way that the proper blanks are filled in in the text portion and are included in the email body, however I have not figured out how to include the table as well.
我正在尝试创建一个可以使用 VBA 发送的用于商业用途的电子邮件模板，因为最终目标是用户只能通过用户表单填写空白，因此文本保持不变。我已经有了用户表单，并以这样一种方式进行编码，即在文本部分填充正确的空白并包含在电子邮件正文中，但是我还没有弄清楚如何包含表格。
Here is what I have so far as my attempt to add the table:
以下是我尝试添加表格的内容：
Sub SendEmail(what_address As String, subject_line As String, mail_body As String, claim_info As Range)
Sub SendEmail(what_address As String, subject_line As String, mail_body As String, claim_info As Range)
Dim olApp As Outlook.Application Set olApp = CreateObject("Outlook.Application")
Dim olApp As Outlook.Application Set olApp = CreateObject("Outlook.Application")
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = what_address
olMail.Subject = subject_line
olMail.Body = mail_body
olMail.HTMLBody = RangeToHtml.claim_info
olMail.Send
End Sub Sub SendClaimsEmail()
End Sub Sub SendClaimsEmail()
Dim mail_body_message As String
Dim tracking_number As String
Dim amount_paid As String
Dim date_paid As String
Dim payment_due As String
Dim claim As Range
Set claim = Nothing
On Error Resume Next
'Only send the visible cells in the selection.
Set claim = Selection.SpecialCells(xlCellTypeVisible)
Set claim = Sheets("Sheet1").RangeToHtml("B2:C9").SpecialCells(xlCellTypeVisible, xlTextValues)
On Error GoTo 0
mail_body_message = Sheet1.Range("A1")
tracking_number = Sheet1.Range("G2")
amount_paid = Sheet1.Range("G3")
date_paid = Sheet1.Range("G4")
payment_due = Sheet1.Range("G5")
mail_body_message = Replace(mail_body_message, "replace_tracking", tracking_number)
mail_body_message = Replace(mail_body_message, "replace_amountpaid", amount_paid)
mail_body_message = Replace(mail_body_message, "replace_datepaid", date_paid)
mail_body_message = Replace(mail_body_message, "replace_pmtdueto", payment_due)
Call SendEmail("[email protected]", "Subject Line", mail_body_message, claim)
MsgBox "Complete!"
MsgBox "完成！"
End Sub
结束子
回答by mmurrietta
I believe your problem lies in trying to combine olMail.Bodyand olMail.HTMLBodyin the same message. From my experience you need to choose one and the table will not work with olMail.Body.
我相信你的问题在于试图结合olMail.Body和olMail.HTMLBody在同一个消息中。根据我的经验，您需要选择一个，并且该表将无法使用olMail.Body。
I suggest converting mail_bodyto valid HTML and appending to olMail.HTMLBody:
我建议转换mail_body为有效的 HTML 并附加到olMail.HTMLBody：
Sub SendEmail(what_address As String, subject_line As String, mail_body As String, claim_info As Range)
Dim olApp As Outlook.Application Set olApp = CreateObject("Outlook.Application")
Dim olApp As Outlook.Application Set olApp =
CreateObject("Outlook.Application")
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = what_address
olMail.Subject = subject_line
'assuming your mail_body uses vbLf for linefeeds you can just do this
mail_body = "<p>" & replace(mail_body,vblf,"</br>") & "</p>"
olMail.HTMLBody = mail_body & "</br>" & RangeToHtml.claim_info
olMail.Send
End Sub
