CODEWITHSUNDEEP
vbaexcel
Steve
Steve

Reputation: 71

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

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.

Upvotes: 6

Views: 34623

Answers (6)

rhythmo
rhythmo

Reputation: 949

I came across the same issue

This was my code

 ' Construct the PDF file name
    fileName = projectReference & " - Doc Transmittal #" & transmittalNumber & " - " & summaryName & " " & dateTimeStamp & ".pdf"
    
    ' Get the desktop path
    desktopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")

'Print the active sheet to PDF
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    fileName:=desktopPath & "\" & fileName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

The issue was with the filename string. In the filename, I had these characters "<<" and ">>" which was causing the error "Document not saved". I removed the characters and that fixed the issue. It might be a good idea to look for any special characters in the filename. Hope this helps.

Upvotes: 0

shaolincss
shaolincss

Reputation: 3

I ran into the same problem and I think the problem (at least how I resolved it) is by changing the format of date in windows. If the date format is like DD/MM/YYYY or MM/DD/YYYY (for US), the macro think that it is a folder. You have to format the date in the windows setting to DD-MM-YYYY or similar.

Settings

Upvotes: 0

user2216667
user2216667

Reputation: 31

Check your path, take it out and see what it does or play around with it. I put a spreadsheet on a new machine and it failed when trying to place it in the user\documents folder

Upvotes: 0

M Ryder
M Ryder

Reputation: 11

Make sure that you have created your folder structure into which the file will be saved. You can code your solution to check that the folders have been created and if not, it can created them. I have had a similar problem before. Check that the folder path used in the following code exists:

strPath = Environ$("temp") & "\"

If it does not exist, create it and try again. You will probably find that it then works fine.

Upvotes: 1

0m3r
0m3r

Reputation: 12499

I see nothing wrong with your code, which Excel Office are you using?

To add your default signature try this:

Dim Signature As String

With OutMail
    .Display
End With

Signature = OutMail.HTMLBody

'Create message
On Error Resume Next
With OutMail
    .To = "[email protected]"
    .CC = "[email protected]"
    .BCC = ""
    .Subject = "Daily Dashboard"
    .HTMLBody = "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" & vbNewLine & Signature
    .Attachments.Add strPath & strFName
    .Display
'        .Send
End With

Also instead of using vbNewLine try & "<br>" &

Upvotes: 1

ThunderFrame
ThunderFrame

Reputation: 9461

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 |.

Upvotes: 7

Related Questions