VBA - Save Worksheet as PDF - Not Working Since 365 Upgrade

[lager1001]

I have the below code set that has worked for several years for a specific report but is no longer working since Office was upgraded to 365. I am now getting a Run-Time error '1004': Application-defined or object-defined error after the message box asks for confirmation. I'm wondering if has something to do with library resources? Macro security settings appear to be ok. Any ideas?
Sub SendAsPDF
Dim sBlatt As String
Dim sPdfDateiF5 As String
Dim OutApp As Object
Dim OutMail As Object

If MsgBox("Send the order as a E-Mail?", 4, "Frage") = vbYes Then


sPdfDateiF5 = "FilePath" & ".pdf"

(ERROR OCCURS DURING BELOW CODE)
ActiveWorkbook.Sheets("Form Pallet").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=sPdfDateiF5, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

After this code it sends the attached PDF to a distribution list via Outlook.

 

[Domenic]

Your code refers to the active workbook, and it assumes that the active workbook contains a sheet called "Form Pallet". Is this in fact the case?

 

[jolivanes]

What is "FilePath" here?

sPdfDateiF5 = "FilePath" & ".pdf"

 

[lager1001]

What is "FilePath" here?

sPdfDateiF5 = "FilePath" & ".pdf"

That is the file path where the PDF is saved to. I just called it "FilePath" for the sake of this post.

 

[lager1001]

What is "FilePath" here?

sPdfDateiF5 = "FilePath" & ".pdf"

Yes,

Your code refers to the active workbook, and it assumes that the active workbook contains a sheet called "Form Pallet". Is this in fact the case?

Yes, it is saving that worksheet to PDF.

 

[Domenic]

So then the error occurs after the line that exports to PDF. Can you post the line that gets highlighted when the error occurs, and confirm the actual error message that is displayed?

 

[lager1001]

So then the error occurs after the line that exports to PDF. Can you post the line that gets highlighted when the error occurs, and confirm the actual error message that is displayed?

That is correct. Again, this macro worked for 3 years, then suddenly an office upgrade to 365, and now the 1004 error AFTER the sPdfDateiF5 = "FilePath" & ".pdf" line.

The error is a message box with "Run-Time error '1004': Application-defined or object-defined "

 

[Domenic]

Did you make sure that your variable sPdfDateiF5 has been assigned a valid path, and that you have permission to access the directory?

 

[lager1001]

Did you make sure that your variable sPdfDateiF5 has been assigned a valid path, and that you have permission to access the directory?

Hi, yes. the path is valid. Again, this macro has worked for 3 years and this issue didn't occur until office was upgraded to 365 which makes me think it is related to that upgrade and some incompatibility between 2013 and 365. here is the full code as it has worked for 3 years. No processes, folder accesses, etc have changed. I have changed the folder path and removed the email addresses from the below code for privacy.

Private Sub Workbook_Open2()

Sheets("Makro Box").Range("C3") = Application.UserName

End Sub

Sub Box()

Dim sBlatt As String
Dim sPdfDateiF5 As String
Dim OutApp As Object
Dim OutMail As Object

If MsgBox("Send the order as a E-Mail?", 4, "Frage") = vbYes Then

' speichern unter als PDF:
sPdfDateiF5 = "Filepathhere" & "\" & "BOX Order" & "_" & Format(Date, "MM.DD.YYYY") & "_" & ActiveWorkbook.Sheets("Form Box").Range("E3") & ".pdf"

' speichert das aktuelle Blatt (=ActiveSheet) als PDF
ActiveWorkbook.Sheets("Form Box").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=sPdfDateiF5, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False


Set OutApp = CreateObject("Outlook.Application")


Set OutMail = OutApp.CreateItem(0)


OutMail.to = ""
OutMail.CC = ""
OutMail.BCC = ""
OutMail.Subject = " Orders"
OutMail.Body = "Best regards"


OutMail.Attachments.Add sPdfDateiF5


OutMail.Send

Sheets("Form Box").Range("E3").Value = Sheets("Form Box").Range("E3").Value + 1

'Track

Sheets("Form Box").Range("G3").Copy
Sheets("Track Box").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Makro Box").Range("C3").Copy
Sheets("Track Box").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B5").Copy
Sheets("Track Box").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B6").Copy
Sheets("Track Box").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B7").Copy
Sheets("Track Box").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B8").Copy
Sheets("Track Box").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B9").Copy
Sheets("Track Box").Range("G" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B10").Copy
Sheets("Track Box").Range("H" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B11").Copy
Sheets("Track Box").Range("I" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B12").Copy
Sheets("Track Box").Range("J" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B13").Copy
Sheets("Track Box").Range("K" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B14").Copy
Sheets("Track Box").Range("L" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B15").Copy
Sheets("Track Box").Range("M" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B16").Copy
Sheets("Track Box").Range("N" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B17").Copy
Sheets("Track Box").Range("O" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B18").Copy
Sheets("Track Box").Range("P" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("D5").Copy
Sheets("Track Box").Range("Q" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("D6").Copy
Sheets("Track Box").Range("R" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("D7").Copy
Sheets("Track Box").Range("S" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("F5").Copy
Sheets("Track Box").Range("T" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("F6").Copy
Sheets("Track Box").Range("U" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("F7").Copy
Sheets("Track Box").Range("V" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("F8").Copy
Sheets("Track Box").Range("W" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("H5").Copy
Sheets("Track Box").Range("X" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("H6").Copy
Sheets("Track Box").Range("Y" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("H7").Copy
Sheets("Track Box").Range("Z" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B21").Copy
Sheets("Track Box").Range("AA" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B22").Copy
Sheets("Track Box").Range("AB" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B23").Copy
Sheets("Track Box").Range("AC" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("B24").Copy
Sheets("Track Box").Range("AD" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("D21").Copy
Sheets("Track Box").Range("AE" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("D22").Copy
Sheets("Track Box").Range("AF" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("D23").Copy
Sheets("Track Box").Range("AG" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("D24").Copy
Sheets("Track Box").Range("AH" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("F21").Copy
Sheets("Track Box").Range("AI" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("F22").Copy
Sheets("Track Box").Range("AJ" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("F23").Copy
Sheets("Track Box").Range("AK" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("F24").Copy
Sheets("Track Box").Range("AL" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("H21").Copy
Sheets("Track Box").Range("AM" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("H22").Copy
Sheets("Track Box").Range("AN" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("H23").Copy
Sheets("Track Box").Range("AO" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Sheets("Form Box").Range("H24").Copy
Sheets("Track Box").Range("AP" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False


' delete box Makro

Range("C3,B8:B21,D8:D20,F8:F20,H8:H20,B24:B27,D24:D27,F24:F27,H24:H28,I24:I27") _
.Select
Range("I24").Activate
Selection.ClearContents
Range("B8").Select
ActiveWorkbook.Save

End If
End Sub

 

[Domenic]

If the path is correct and you have permission to access the folder, it should work. Just to be sure, enter the following line of code in the Immediate Window and press the ENTER key. If the path is valid, it should return the folder name. Does it return the folder name?

? Dir("Filepathhere", vbDirectory)