excel - 在电子邮件正文中发送 Excel 表格

Question

我正在使用此代码通过 VBA 发送电子邮件，但我需要以 Body 形式发送表格.
此代码仅发送一个单元格而不是一个范围。
如何粘贴 Range("B5:D10")作为邮件正文中的表格？

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
    .To = Range("B1").Value
    .Cc = Range("B2").Value
    .Bcc = Range("B3").Value
    .Subject = Range("B4").Value
    .Body = Range("B5").Value
    .Send
End With
On Error GoTo 0
Set OutMail = Nothing

Answer 1

您可以通过设置 HTMLBody 来实现。而不是 Body .但是，要控制消息的格式，您必须具备 HTML 的基本知识。

其背后的想法如下:您必须将范围内容与 HTML 标签放在一起，如下所示:

Dim rng As Range, cell As Range, HtmlContent As String, i As Long, j As Long
Set rng = Range("B5:D10")
HtmlContent = "<table>"

For i = 5 To rng.Rows.Count + 4
    HtmlContent = HtmlContent & "<tr>"
    For j = 2 To rng.Columns.Count + 2
        HtmlContent = HtmlContent & "<td>" & Cells(i, j).Value & "</td>"
    Next
    HtmlContent = HtmlContent & "</tr>"
Next
HtmlContent = HtmlContent & "</table>"

然后，将此表放入消息中:

With OutMail
    '...
    .HTMLBody = HtmlContent
    '...
End With