Run-time error 1004

[josros60]

Getting this run-time error 1004 and when go debug highlight the the following line of code:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=OpenPDFAfterCreating

thank you,

 

[Domenic]

You've given us the error number, but can you gives us the description that goes along with it as well? Also, did you make sure that the path referred to in PDFFile is a valid one, and that you have permissions to access the folder, and that it doesn't refer to the root folder (ie. C:\) ?

 

[josros60]

Hi,

this is message i get with 1004 error, Document no saved. The document may be open, or an error may have been encountered when saving and here is the path:

DestFolder = "C:\Users\jose.rossi\Desktop\Excel Files\PDF\RemittanceEXCEL_PDF"

part of the code:

MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"

thanks,

 

[Domenic]

Yes, but you're using the variable PDFFile as the argument for Filename. I'm assuming that you've assigned it a string that consists of both the path and the filename. What string have you assigned it?

 

[DanteAmor]

Put your complete code

 

[josros60]

hi,

this is the complete code:

Sub create_and_email_4pdf()' Author - Philip Treacy  ::   http://www.linkedin.com/in/philiptreacy
' http://www.MyOnlineTrainingHub.com/vba-to-create-pdf-from-excel-worksheet-then-email-it-with-outlook
' Date - 14 Oct 2013
' Create a PDF from the current sheet and email it as an attachment through Outlook


Dim EmailSubject As String, EmailSignature As String
Dim CurrentMonth As String, DestFolder As String, PDFFile As String
Dim Email_To As String, Email_CC As String, Email_BCC As String, Email_body As String
Dim OpenPDFAfterCreating As Boolean, AlwaysOverwritePDF As Boolean, DisplayEmail As Boolean
Dim OverwritePDF As VbMsgBoxResult
Dim OutlookApp As Object, OutlookMail As Object
CurrentMonth = ""


' *****************************************************
' *****     You Can Change These Variables    *********


    EmailSubject = "NAVIGATA a/c " & " " & ActiveSheet.Range("A3") & "," & " " & "Statement Of Account $ " & Format(Range("I1").Value, "$#,##0.00;($#,##0.00)") 'Change this to change the subject of the email. The current month is added to end of subj line
    OpenPDFAfterCreating = False    'Change this if you want to open the PDF after creating it : TRUE or FALSE
    AlwaysOverwritePDF = False      'Change this if you always want to overwrite a PDF that already exists :TRUE or FALSE
    DisplayEmail = True 'Change this if you don't want to display the email before sending.  Note, you must have a TO email address specified for this to work
    Email_To = ActiveSheet.Range("B3")   'Change this if you want to specify To email e.g. ActiveSheet.Range("H1") to get email from cell H1
    Email_CC = "accounts.receivable@navigata.ca"
    Email_BCC = ""
    Email_body = "<p style='font-family:calibri;font-size:15'>" & "Hi" & ActiveSheet.Range("A2") & "," & "<br>" & "<br>" & ActiveSheet.Range("W3") & " " & "<br>" & "<br>" & "If you have any question regarding the attached statement, please let me know." & "<br>" & "<br>" & "<br>" & "<br>" & "Regards,"
' ******************************************************
    
    'Prompt for file destination
    With Application.FileDialog(msoFileDialogFolderPicker)
        
        If .Show = True Then
        
            DestFolder = "C:\Documents and Settings\rossj1\Desktop\Excel Files\REMITTANCE\EXCEL_PDF" '.SelectedItems(1)
            
        Else
        
            MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"
                
            Exit Sub
            
        End If
        
    End With


    'Current month/year stored in H6 (this is a merged cell)
    CurrentMonth = Mid(ActiveSheet.Range("H6").Value, InStr(1, ActiveSheet.Range("A3").Value, " ") + 1)
    
    'Create new PDF file name including path and file extension
    PDFFile = DestFolder & Application.PathSeparator & ActiveSheet.Range("A100") _
               & "_" & CurrentMonth & ".pdf"


    'If the PDF already exists
    If Len(Dir(PDFFile)) > 0 Then
    
        If AlwaysOverwritePDF = False Then
        
            OverwritePDF = MsgBox(PDFFile & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", vbYesNo + vbQuestion, "File Exists")
        
            On Error Resume Next
            'If you want to overwrite the file then delete the current one
            If OverwritePDF = vbYes Then
    
                Kill PDFFile
        
            Else
    
                MsgBox "OK then, if you don't overwrite the existing PDF, I can't continue." _
                    & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
                
                Exit Sub
        
            End If


        Else
        
            On Error Resume Next
            Kill PDFFile
            
        End If
        
        If Err.Number <> 0 Then
        
            MsgBox "Unable to delete existing file.  Please make sure the file is not open or write protected." _
                    & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
                
            Exit Sub
        
        End If
            
    End If
   


    'Create the PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=OpenPDFAfterCreating


    'Create an Outlook object and new mail message
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
        
    'Display email and specify To, Subject, etc
    With OutlookMail
        
        .Display
        .To = Email_To
        .CC = Email_CC
        .BCC = Email_BCC
        .Subject = EmailSubject & CurrentMonth
        '.Body = Email_body
        .HTMLBody = Email_body & "<br>" & .HTMLBody
        .Attachments.Add PDFFile
                
        If DisplayEmail = False Then
            
            .send
            
        End If
        
    End With
    
End Sub

 

[DanteAmor]

The problem is that in this line of your code you are bringing a diagonal "/" of a date (A diagonal is not allowed in the name of a file), because I suppose that in H6 you have a date, but I do not know what you have in A3.



But if you only want the month number you can change this:

CurrentMonth = Mid(ActiveSheet.Range("H6").Value, InStr(1, ActiveSheet.Range("A3").Value, " ") + 1)

For this:

CurrentMonth = format(ActiveSheet.Range("H6").Value, "MM")

 

[josros60]

Hi,

i changed it to your suggestion, but the keep geting the error highlighting this line:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=OpenPDFAfterCreating

thanks

 

[DanteAmor]

What does the error say?
exactly what do you have in the cells?
exactly what do you have in the variable PDFfile at the time of saving?

 

[josros60]

this error i get:

Run-time error 1004:


Document not saved. The document may be open, or an error may have been encountered when saving.