SAVING A PDF SPREADSHEET IN EXCEL DOESN'T WORK

[jujuarucih]

The aim is for the code to generate the pdf, save it and attach it to an email in outlook.

The following error appears:

Runtime error '1004':
The document was not saved. It may be open or there may have been an error during saving

Private Sub CommandButton13_Click()
    Dim ws As Worksheet
    Dim rng As Range
    Dim fileName As String

    Set ws = ThisWorkbook.Sheets("PDF")

    Set rng = Union(ws.Range("E2"), ws.Range("G9"), ws.Range("M9"))

    fileName = "New Contract_" & ws.Range("G9").Value & "_" & ws.Range("M9").Value & "_" & ws.Range("E2").Value

     ws.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
        Environ("USERPROFILE") & "\Downloads\" & fileName, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
    Set outlookApp = CreateObject("Outlook.Application")
    Set outlookMail = outlookApp.CreateItem(0)

    With outlookMail
        .Subject = fileName
        .Body = "Dear Leslie. Please follow the attached contract! "
        .To = "leslie.almeida@ui.com"

        .Attachments.Add Environ("USERPROFILE") & "\Downloads\" & fileName & ".pdf"

        .Display
        
    End With

    Set outlookMail = Nothing
    Set outlookApp = Nothing

 

[dmt32]

Hi,
untested but see if this update to your code resolves the issue

Private Sub CommandButton13_Click()
    Dim ws          As Worksheet
    Dim rng         As Range
    Dim strFileName As String, strFolderName As String
    
    strFolderName = Environ("USERPROFILE") & "\Downloads\"
    
    On Error GoTo myerror
    Set ws = ThisWorkbook.Sheets("PDF")
    
    Set rng = Union(ws.Range("E2"), ws.Range("G9"), ws.Range("M9"))
    
    strFileName = "New Contract_" & rng.Areas(2).Value & "_" & _
                                    rng.Areas(3).Value & "_" & _
                                    rng.Areas(1).Value & ".pdf"

    ws.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strFolderName & strFileName, Quality:= _
                           xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                           OpenAfterPublish:=False

    Set outlookApp = CreateObject("Outlook.Application")
    Set outlookMail = outlookApp.CreateItem(0)
    
    With outlookMail
        .Subject = Split(strFileName, ".")(0)
        .Body = "Dear Leslie. Please follow the attached contract! "
        .To = "leslie.almeida@ui.com"
        
        .Attachments.Add strFolderName & strFileName
        
        .Display
        
    End With
    
myerror:
    Set outlookMail = Nothing
    Set outlookApp = Nothing
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    
End Sub

Dave