Hoping someone can please assist. I have the below code which generates a PDF and emails it a certain email address. The issue I am having however, is the file which is created is always created in Sharepoint and I am having some difficulty with the kill
command. When using Kill PdfFile
as it is unable to find the file it created in the first place. Is anyone able to advise how I can 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
CodePudding user response:
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")