Error Run-Time Error '1004' Document not saved.

Question

I have been successfully running a macro which saves my Excel sheet as a PDF and emails my Executive team.

I redesigned it, by creating a new sheet, and updated the code accordingly.

Sub NewDashboardPDF()

' New Executive Daily Dashboard Macro
'
' Create and email the Executive TEAM the Daily Dashboard.
    Dim strPath As String, strFName As String
    Dim OutApp As Object, OutMail As Object

' Create and email the Daily Report to Mitch/Dave/John/Jason ALL PAGES.
    Sheets("Executive Dashboard").Select
    strPath = Environ$("temp") & "\" 'Or any other path, but include trailing "\"
    strFName = Worksheets("Executive Dashboard").Range("V2").Value & " " & Format(Date, "yyyymmdd") & ".pdf"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strPath & strFName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

     'Set up outlook
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
     'Create message
    On Error Resume Next
    With OutMail
        .to = [email protected]
        .CC = "[email protected]"
        .BCC = ""
        .Subject = "Daily Dashboard"
        .Body = "All, " & vbNewLine & vbNewLine & _
                        "Please see the attached daily dashboard." & vbNewLine & _
                        "If you have any questions or concerns, please do not hesitate to contact me." & vbNewLine & _
                        "Steve"
        .Attachments.Add strPath & strFName
        .Display
        .Send
    End With
     'Delete any temp files created
    Kill strPath & strFName
    On Error GoTo 0

End Sub

The error message I get is Run-Time Error '1004' Document not saved. The document may be open or an error may have been encountered.

When I debug, the following section is highlighted with the arrow on the last line.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strPath & strFName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

All references to the old sheet were updated to the new one so I do not believe that is the issue.

On another note, I would love to know how to create this email with my default email signature included. Currently it is just formatted as a plain text email.

Answer 1

The Document not saved error message is the clue that the PDF file is not writable, probably because it is open in your PDF reader. I can repeat the error if I have the PDF document open while trying to save the document from VBA.

If you don't have the document open, there is a chance that Windows has inadvertently left a lock on the file. You may need to restart your PC to clear the lock.

If the file doesn't yet exist, then you'll need to confirm that you can actually create files in the directory location.

You will encounter a similar error if the value in V2 contains characters that ultimately make the filename invalid, such as \, /, :, *, ?, ", <, > or |.