File from OneDrive is not attached to Outlook message: Download error

Question

I have a VBA script in Excel 2016 that exports a worksheet to PDF, and then creates an email in Outlook 2016:

Tabelle8.ExportAsFixedFormat Type:=xlTypePDF, Filename:= 
  ThisWorkbook.Path & "\" & ExportFilename, Quality:=xlQualityStandard 
  , IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish _
  :=False
strPDF = ThisWorkbook.Path & "\" & ExportFilename & ".pdf"
Set OutlookApp = CreateObject("Outlook.Application")
Set strEmail = OutlookApp.CreateItem(0)


With strEmail
  .To = recipient
  .CC = ""
  .Subject = subject
  .HTMLBody = text
  .Attachments.Add strPDF
  .Display
EndWith

This works correctly as long as the workbook is on the local drive, but fails once it is in OneDrive. On OneDrive,

MsgBox strPDF

returns

and somehow, this causes the error "Download failed":

Why is this and how to fix?

Answer 1

Using Temp folder or Application path to export the .PDF:

If the goal just to attach the .PDF file to the Outlook mail item, then instead of exporting the .PDF to:

ThisWorkbook.Path

...(which returns the path where the current workbook is saved), you could instead export it to:

Application.Path

...which returns the path to the Excel installation; in my case it's:

C:\Program Files (x86)\Microsoft Office\root\Office16

so you'd change this line as follows:

strPDF = Application.Path & "\" & ExportFilename & ".`.PDF`"

...or, export it to the Windows temp folder:

 strPDF = Environ("temp") & "\" & ExportFilename & ".pdf"

especially if the .PDF's only purpose is to attach to the email. In my case, the Windows Temp folder is:

C:\Users\[WindowsLoginName]\AppData\Local\Temp

Either way, you still have (at least temporary) access to the file in whichever destination you choose.

---

Keep a Copy:

If you also need to keep a copy of the file on OneDrive, then you have a few options.

If the .PDF was previously saving properly to OneDrive, but Excel couldn't attach it to the Outlook mail item, then you could export the file, with something like:

'export PDF to workbook path
strPDF_save = ThisWorkbook.Path & "\" & ExportFilename & ".pdf"
Tabelle8.ExportAsFixedFormat xlTypePDF, strPDF

'export PDF to temp folder
strPDF_temp = Environ("temp") & "\" & ExportFilename & ".pdf"
Tabelle8.ExportAsFixedFormat xlTypePDF, strPDF

'create Outlook object and send email as attachment
Set OutlookApp = CreateObject("Outlook.Application")
With OutlookApp.CreateItem(0)
  .To = recipient
  .Subject = Subject
  .HTMLBody = Text
  .Attachments.Add strPDF
  .Display 'display the email before sending
End With

_{(I also removed some extraneous code leftover from recording the macro.)}

---

Map a Drive Letter to OneDrive:

If you're going to be using OneDrive to save/retrieve files on a regular basis, I'd suggest mapping a drive letter to the OneDrive folder.

1. Go to https://onedrive.live.com.  

2. Write down or Copy the CID number in the address bar:

 

3. Hit the Windows Key, then Right-click Computer, and click "Map Network Drive".

  4. In the Map Network Drive dialog, choose a drive letter that you will use to refer to OneDrive (perhaps O:). In the Folder textbox, enter:

https://d.docs.live.net/ Your CID Number

Click Reconnect at Logon, and then click Finish.

5. You will be prompted to enter your Microsoft Account User ID & Password.

The drive will be mapped! At this point you can create a shortcut to the drive letter on your desktop, etc, and you can use the drive letter like a local drive for Saving/Opening files, etc.

---

Final Thought:

I have to point out that you're kinda defeating the purpose of Cloud Storage by saving the file on OneDrive and then emailing it as an attachment.

Best practice now tells us to save the file in a shared or sharable location, then email a link to it instead – all of which can be done with the power of Office 365.

Doing so reduces the risk of:

Raising spam flags

Some email clients will mark emails containing large files as spam and drop the incoming message into a junk mail folder.

Delivery failure

Even in the cloud age, some email clients have strict file size limits. Sending a link instead of a bulky file ensures a smooth delivery to the intended recipient.

Consuming space

Managing your organization’s data and storage keeps your IT staff hopping. Sending and receiving large files – especially when there’s a lighter alternative – makes their lives more difficult. _(Source)

---

More Information:

• Office 365 Tips Worth Sharing: Email a Link, Not an Attachment

• MSDN : Workbook.ExportAsFixedFormat Method

• Links vs Attachments - What method should you use?

• MSDN : Application.Path Property

• Office.com : Share OneDrive files and folders

• MSDN : Environ Function

• Office.com: Change Permissions or Stop sharing OneDrive files or folders

• Using the REST API to programmatically access a user's Microsoft Account (such as OneDrive)