(Solved) – VBA/Excel – File Path not attaching PDF to Email Draft

  • by

The goal of the below VBA code is to create several draft emails populated with From, To, CC, Subject, Body, Attachments from an Excel file.

However whenever I attempt to run the code an error message is thrown that says “Cannot find the attachment, no data source found”, even though when I click on the link in the Excel file it brings up the PDF I intend to attach.

The network file path is:

fake.fakeYdrivefakeFinanceFinanceIMC FoldersProvider InvoicesInvoice & PaymentsAP_Calendar_2020.pdf

The link in the cell saved as a link, with no quotations. When I click on the link the file opens up. I have replaced identifying information with the word “fake” in the above.

I have attempted to remove the hyperlink, put the link in quotes, and done lots of searching. I have a feeling it’s something to do with how VBA is interpreting the network file path, but I am unsure.

What are the possible reasons this is happening and what can I do to resolve this issue?

Thank you for your help!

Public Sub DraftOutlookEmails()
    'Microsoft Outlook XX.X Object Library is required to run this code
    'Variable declaration
    Dim objOutlook As Outlook.Application
    Dim objMail As Outlook.MailItem
    Dim lCounter As Long

    'Set objects
    Set objOutlook = Outlook.Application
    'Read details from Excel sheet and draft emails
    For lCounter = 6 To 8    'You can change the counter as per requirement
        'Create a new email item
        Set objMail = objOutlook.CreateItem(olMailItem)
        objMail.To = Sheet1.Range("A" & lCounter).Value
        objMail.CC = Sheet1.Range("B" & lCounter).Value
        objMail.Subject = Sheet1.Range("C" & lCounter).Value
        'Email Body
        objMail.Body = Sheet1.Range("D" & lCounter).Value
        'Add Attachment

        objMail.Attachments.Add Sheet1.Range("F" & lCounter).Value
        'objMail.Attachments.Add (attachment)
        'Draft email
        objMail.Close (olSave)
        'Close the object
        Set objMail = Nothing
    'Show confirmation message to user
    MsgBox "Done", vbInformation
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *