excel - 如何在起诉 VBA 的 Outlook 电子邮件中添加 PDF 附件？

Question

我想弄清楚如何使用 VBA 将 PDF 文档附加到 Outlook 电子邮件。这是我工作的公司的客户服务代表的一个项目。

他们在 Outlook 收件箱中收到此 PDF。有没有办法可以引用这封电子邮件的标题（可能在单元格中），以便将 PDF 附件添加到我的程序发送的电子邮件中？还是随邮件一起转发？

Sub RFAEmail()

    'Current Code:

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim strbody1 As String
    Dim strbody2 As String
    Dim strbody3 As String
    Dim strbody4 As String
    Dim strbody5 As String
    Dim strbody6 As String
    Dim strbody7 As String
    Dim strbody8 As String
   
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    strbody1 = "" & Space(1) & Range("B5")
    strbody2 = "Customer:" & Space(1) & Range("C5")
    strbody3 = "" & Space(1) & Range("D5")
    strbody4 = "Current:" & Space(1) & Range("E5")
    strbody5 = "Proposed:" & Space(1) & Range("F5")
    strbody6 = "Changes:" & Space(1) & Range("H5")
    strbody7 = "Other Notes:" & Space(1) & Range("I5")
    strbody8 = "PDF" & Space(1) & Range("G5")
    
    strbody = strbody1 & vbNewLine & strbody2 & vbNewLine & strbody3 & vbNewLine & strbody4 & vbNewLine & strbody5 & vbNewLine & strbody6 & vbNewLine & strbody7 & vbNewLine & strbody8
 

    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "JOB CHANGE" & Space(1) & Range("B5")
        .Body = strbody
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    
    MsgBox "Request Sent", vbApplicationModal, "Complete"

End Sub

标签： excelvbaoutlook

Answer 1

您需要将附件保存在磁盘上，以便以后发送。Attachment.SaveAsFile方法将附件保存到指定路径。例如：

Sub SaveAttachment()  
 Dim myInspector As Outlook.Inspector  
 Dim myItem As Outlook.MailItem  
 Dim myAttachments As Outlook.Attachments 

 Set myInspector = Application.ActiveInspector  
 If Not TypeName(myInspector) = "Nothing" Then  
   If TypeName(myInspector.CurrentItem) = "MailItem" Then  
     Set myItem = myInspector.CurrentItem  
     Set myAttachments = myItem.Attachments  
     'Prompt the user for confirmation  
     Dim strPrompt As String  
     strPrompt = "Are you sure you want to save the first attachment in the current item to the Documents folder? If a file with the same name already exists in the destination folder, it will be overwritten with this copy of the file."  
     If MsgBox(strPrompt, vbYesNo + vbQuestion) = vbYes Then  
       myAttachments.Item(1).SaveAsFile Environ("HOMEPATH") & "\My Documents\" & _  
       myAttachments.Item(1).DisplayName  
     End If  
   Else  
     MsgBox "The item is of the wrong type."  
   End If  
 End If  
End Sub

Attachments.Add方法在Attachments 集合中创建一个新附件。附件的来源可以是文件（由带有文件名的完整文件系统路径表示）或构成附件的 Outlook 项目。

Sub AddAttachment() 
 Dim myItem As Outlook.MailItem 
 Dim myAttachments As Outlook.Attachments 

 Set myItem = Application.CreateItem(olMailItem) 
 Set myAttachments = myItem.Attachments 
 myAttachments.Add "C:\Test.doc", _ 
 olByValue, 1, "Test" 
 myItem.Display 
End Sub