时间戳

首页 > 解决方案 > 如何使用 Visual Basic 添加一系列单元格作为来自 Excel 的自定义 SMTP 电子邮件的正文？

excel - 如何使用 Visual Basic 添加一系列单元格作为来自 Excel 的自定义 SMTP 电子邮件的正文？

问题描述

单击 Excel 中的按钮后，我将工作表上的一系列单元格作为电子邮件的正文发送。电子邮件发送正确，但我无法弄清楚如何将实际的单元格范围添加为正文。这是我目前在 Excel 的 Visual Basic 中使用的代码以及用于 Windows 2000 库的 Micrsoft CDO：

Sub Email_Figures_Click()
Dim myMail As CDO.Message

Set myMail = New CDO.Message

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "HIDDEN"
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "HIDDEN"
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "HIDDEN"
myMail.Configuration.Fields.Update

With myMail
.Subject = "HIDDEN"
.From = "HIDDEN"
.To = "HIDDEN"

    Dim myRng As Range
    Set myRng = Nothing

    'Only the visible cells in the selection
    Set myRng = Sheets("Monthly Figures").Range("A1:B29").SpecialCells(xlCellTypeVisible)

    If myRng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub

    End If

.HTMLBody = myRng
End With
On Error Resume Next
myMail.Send
MsgBox ("Email has been sent!")
Set myMail = Nothing
End Sub

任何带有 HIDDEN 字样的东西都是为了保护客户。

任何帮助表示赞赏。

标签： excelvbaemailsmtp

解决方案

如果我理解正确，您不能简单地将范围分配给.HTMLBody. 您可能必须“手动”构建 HTML-String 并具有类似于

html_text = "<table>" & vbCrLf & "<tr>"
For Each Row In myrng.Rows
    For Each cell In Row.Cells
        html_text = html_text & "<td>" & cell.Text & "</td>"
    Next cell
    html_text = html_text & "</tr>" & vbCrLf
Next Row
html_text = html_text & "</table>"
.HTMLBody=html_text

更换你的线路

.HTMLBody = myRng

.

推荐阅读