如何在Excel中使用VBA向电子邮件添加附件 | 如何在Excel中使用VBA向电子邮件添加附件

本文介绍了如何在Excel中使用VBA向电子邮件添加附件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有以下代码，但无法正常工作.我对VBA也很陌生.该代码可以填充电子邮件模板，但是一旦我添加.Attachment.添加它就无法正常工作.

I have the following code but it is not working. I am fairly new to VBA as well. The code works to populate the email template but as soon as I add the .Attachment.Add it does not work.

Sub CreateMail()

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")
    .Attachments.Add "Z:\PHS 340B\Letters of Non-Compliance\..Resources\W9 Form\VPNA W-9 01 09 2017"
End With

With objMail
    .to = rngTo.Value
    .Subject = rngSubject.Value
    .Body = rngBody.Value
    .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

推荐答案

尝试一下:

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

在Outlook而非Excel中工作时，您需要使用.Attachments.Add.

You need to use the .Attachments.Add when working within Outlook not Excel.

这篇关于如何在Excel中使用VBA向电子邮件添加附件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！