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) 'To objMail.To = Sheet1.Range("A" & lCounter).Value 'Cc objMail.CC = Sheet1.Range("B" & lCounter).Value 'Subject 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 Next 'Show confirmation message to user MsgBox "Done", vbInformation End Sub