Happy New Year!
I believe this is relatively simple so I'm hoping someone can solve it for me.
I have the code below in a workbook, which saves the Excel sheet as a PDF and then attaches to an email in Outlook. I've used this code for years with no issue but the file has always been on a local hard drive when the macro was used.
The file I'm using today is stored in SharePoint and used by various people. For some, the macro isn't working at all. My assumption is that it's having an issue when trying to create the temporary file name to SharePoint, then kill that file name.
Can someone look at the VBA code here and suggest a way to make the temporary file path direct towards the local drive versus the SharePoint site that the file is located in?
Thank you for taking a look!
Sub SendWorkSheetToPDF()
Dim wb As Workbook
Dim FileName As String
Dim strbody As String
Dim OutlookApp As Object
Dim OutlookMail As Object
On Error Resume Next
Set wb = Application.ActiveWorkbook
FileName = wb.FullName
xIndex = VBA.InStrRev(FileName, ".")
If xIndex > 1 Then FileName = VBA.Left(FileName, xIndex - 1)
FileName = FileName & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FileName
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Subject Goes Here"
.Body = "Body Goes Here"
.Attachments.Add FileName
.Display
End With
Kill FileName
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub
Bookmarks