excel - 如何将附件添加到电子邮件并从 excel 发送到多个地址

我在https://stackoverflow.com/questions/48775858/how-to-add-an-attachment-to-an-email-using-vba-in-excel/48776096#48776096?newreg=c70a6b475f9f4a42ade49de17aa995f3找到了以下脚本:

Sub emailtest()

Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Range
Dim rngBody As Range
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

With ActiveSheet
Set rngTo = .Range("E2")
Set rngSubject = .Range("E3")
Set rngBody = .Range("E4")
End With

With objMail
.To = rngTo.Value
.Subject = rngSubject.Value
.Body = rngBody.Value
.Attachments.Add "Z:\PHS 340B\Letters of Non-Compliance\..Resources\W9 Form\VPNA W-9 01 09 2017"
.Display 'Instead of .Display, you can use .Send to send the email _
            or .Save to save a copy in the drafts folder
End With

Set objOutlook = Nothing
Set objMail = Nothing
Set rngTo = Nothing
Set rngSubject = Nothing
Set rngBody = Nothing

End Sub

我将 Set rngTo = .Range("E2") 更改为 Set rngTo = .Range("G8:G38") ,运行脚本它在 :.to = rngTo.Value 上给出 440 错误

我究竟做错了什么?

回答1

蒂姆上面说的。尝试这个。

Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Range
Dim rngBody As Range
Dim emails as string
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

With ActiveSheet
Set rngTo = .Range("G8:G38")
Set rngSubject = .Range("E3")
Set rngBody = .Range("E4")
End With

For Each E In rngTo
    emails = emails & E & ";"
Next

With objMail
.To = emails
.Subject = rngSubject.Value
.Body = rngBody.Value
'.Attachments.Add "Z:\PHS 340B\Letters of Non-Compliance\..Resources\W9 Form\VPNA W-9 01 09 2017"
.Display 'Instead of .Display, you can use .Send to send the email _
            or .Save to save a copy in the drafts folder
End With

Set objOutlook = Nothing
Set objMail = Nothing
Set rngTo = Nothing
Set rngSubject = Nothing
Set rngBody = Nothing

相似文章

c# - Excel 尽管对象被释放,但不会关闭

因此,我编写了一个类来处理打开的excel文件,但是即使我调用了close函数,excel也不会关闭。这在我开始使用多个工作簿之前有效,所以我认为这与此有关,但我不知道如何解决这个问题。任何帮助表示赞...