0

Based on code I found on the website, I export a sheet from an Excel workbook as a PDF attached to an email. I struggled to specify the folder where it is saved.

I get:

Run-time error '52'

When I click debug it highlights the line:

If Len(Dir(sPath, vbDirectory)) = 0

sPath = "company.sharepoint.com/sites/Teams Team/Shared Documents/General/CompanyDrive/Client/January2020 \Individual Exports"

We use the company OneDrive/SharePoint local sync to open documents, but it automatically converts to the SharePoint document and tries to save to SharePoint. We get a failed upload all the time.

Is there a way to avoid using the SharePoint path?

Sub Saveaspdfandsend()
    Dim xSht As Worksheet
    Dim xFileDlg As FileDialog
    Dim xFolder As String
    Dim xYesorNo As Integer
    Dim xOutlookObj As Object
    Dim xEmailObj As Object
    Dim xUsedRng As Range
    Dim xPath As String
    Dim NameOfWorkbook
    
    NameOfWorkbook = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
    
    Dim myPath As String
    Dim folderPath As String
    
    folderPath = Application.ActiveWorkbook.Path
    myPath = Application.ActiveWorkbook.FullName

    Set xSht = ActiveSheet

    '1. use the location where I open the file from as autosave location
    xFolder = folderPath + "\" + NameOfWorkbook + " " + xSht.Name + ".pdf"
    '2. create folder in save location if it doesn't exist
    Const SubFolder = "Individual Exports"
    Dim sPath As String
    sPath = folderPath & Application.PathSeparator & SubFolder
    If Len(Dir(sPath, vbDirectory)) = 0 Then
      MkDir sPath
    End If
     
    Set xUsedRng = xSht.UsedRange
    If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
        'Save as PDF file
        xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard
         
        'Create Outlook email
        Set xOutlookObj = CreateObject("Outlook.Application")
        Set xEmailObj = xOutlookObj.CreateItem(0)
        With xEmailObj
            .Display
            .To = ""
            .CC = ""
            .Subject = NameOfWorkbook + " " + xSht.Name
            .Attachments.Add xFolder
            If DisplayEmail = False Then
                '.Send
            End If
        End With
    Else
      MsgBox "The active worksheet cannot be blank"
      Exit Sub
    End If
End Sub
Community
  • 1
  • 1
Harm
  • 1
  • 2
  • Your path is invalid: there are spaces (usually replaced with %20 in URLs) and a mix of forward and backslashes. you need to clean it up before using it in code. You can hard code the URL to check the code works, then correct the string build until it gives you the correct result – Tragamor Feb 24 '21 at 15:00
  • https://stackoverflow.com/questions/42419486/how-to-download-a-file-from-sharepoint-with-vba/42422964 – Tragamor Feb 24 '21 at 15:01

0 Answers0