Adding a PDF Attachment to an Outlook Email

Question

I am trying to figure out how to attach a PDF document to an Outlook email using VBA. This is a project for the Customer Service Representatives at the company I work for.

They receive this PDF into their Outlook inbox. Is there a way I can reference the title of this email (perhaps in a cell) so that the PDF attachment is added to the email my program is sending out? Or forwarded along with the email?

I really appreciate any help anyone can offer. I have no idea where to start.

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

Eugene Astafiev Eugene Astafiev · Accepted Answer · 2020-06-19T21:52:15

You need to save the attached file on the disk to be able to send it out later. The Attachment.SaveAsFile method saves the attachment to the specified path. For example:

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

The Attachments.Add method creates a new attachment in the Attachments collection. The source of the attachment can be a file (represented by the full file system path with a file name) or an Outlook item that constitutes the attachment.

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

Adding a PDF Attachment to an Outlook Email

1 Answers