Deleting a file from Sharepoint using VBA

The below code generates a PDF and emails it to a certain email address.

The file is created in Sharepoint.

Kill PdfFile is unable to find the file.

How can I remove this file?

Private Sub SendFile()

  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
  
  Sheet1.Visible = True
  Sheet1.Activate
 
  Title = Sheet1.Range("E8") & " " & Sheet1.Range("B20")
 
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
 
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  With OutlApp.CreateItem(0)
   
    .Subject = Title
    .To = "[email protected]"
    .Body = "Hi," & vbLf & vbLf _
          & "The report is attached in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile 'This is where things go sideways
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

Solution

The reason this is failing is your use of the existing filename/path to create the PDF filename/path. A common method of creating a temporary file, but it can have issues.

It would appear that .ExportAsFixedFormat along with .SaveAs etc. are happy to take filename/paths that look like a URL:

https://<sharepointsite>.sharepoint.com/sites/<site name>/<folder>/<folder>/<filename.xlsx>

However, it would seem that Kill will not allow this to be used. I believe FSO.DeleteFile will have the same issue.

Instead, you need to convert that site URL into a normal file path. The easiest way to find the path you'd need would be to browse to the file in Windows Explorer, examine the folder path and learn the differences.

Alternatively, choose an alternative location for your temporary pdf file. Your Temp folder can be found using tempfolder = Environ$("temp")