通过VBA从Excel发送电子邮件附件 [英] Sending email attachments from Excel via VBA

问题描述

我编写了一个宏，单击该宏即可通过Outlook发送自动电子邮件.一切运行顺利，但我只是不知道如何将文件附加到电子邮件中.在我到过的所有地方，用于将文件附加到电子邮件的示例代码都是针对静态命名文件的，例如，您将发送相同的文件名，并且每次都使用相同的路径.

I've written a macro that at the click of a button it sends an automated email via Outlook. Everything runs smoothly except I just can't figure out how to attach a file to the email. Everywhere I've looked, example code for attaching files to an email is for static named files, as in, you're sending the same file name, with the same path every time.

如果使用起来更方便，则运行此宏的按钮在我尝试附加的工作簿中.我不确定打开Windows资源管理器窗口是否最简单，最好以这种方式附加文件.

If it makes it more convenient, the button that runs this macro is inside the workbook that I'm trying to attach. I'm not sure if opening a Windows Explorer window is easiest and attaching the file that way would be best.

Sub mySub
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim WeekendingDate As Date

    With Worksheets("Macro Buttons")
        WeekendingDate = Range("N2").Value
    End With

    Set objOutlook = CreateObject("Outlook.Application")

    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
        Set objOutlookRecip = .Recipients.Add("blah@blah")
        objOutlookRecip.Type = olTo
       .Subject = "Blah " & WeekendingDate
       .Body = "blah blah blah"

       'Add attachments to the message
       [some code]


       For Each objOutlookRecip In .Recipients
           objOutlookRecip.Resolve
       Next
       If DisplayMsg Then
           .Display
       Else
           .Save
       End If
    End With
    Set objOutlook = Nothing
End Sub

推荐答案

您需要将Attachments.Add代码插入MailItem设置中:

You need the Attachments.Add code inserted into the MailItem setup:

With objOutlookMsg
    Set objOutlookRecip = .Recipients.Add("blah@blah")
    objOutlookRecip.Type = olTo
   .Subject = "Blah " & WeekendingDate
   .Body = "blah blah blah"
'Add attachments to the message [some code]
   .Attachments.Add "pathToFile"
   For Each objOutlookRecip In .Recipients
       objOutlookRecip.Resolve
   Next
   If DisplayMsg Then
       .Display
   Else
       .Save
   End If
End With
Set objOutlook = Nothing

在我自己的一个脚本中，我使用Dictionary对象和以下代码将附件集合传递给要附加的MailItem:

In one of my own scripts I pass a collection of attachments to the MailItem to be attached using a Dictionary object and the following code:

With oMailItem
        Set .SendUsingAccount = oOutlook.Session.Accounts.Item(iAccount)
        .To = EmailData("To")
        .CC = EmailData("CC")
        .BCC = EmailData("BCC")
        .Subject = EmailData("Subject")
        .Body = EmailData("Body")
        sAttachArray = Split(EmailData("AttachmentPaths"), ";")
        For Each sAttachment In sAttachArray
            .Attachments.Add(sAttachment)
        Next
        .Recipients.ResolveAll
        .Display    ' debug mode - uncomment this to see email before it's sent out
    End With

这篇关于通过VBA从Excel发送电子邮件附件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！