VBA to create PDF and folder if doesn't excist

crxftw

Registered User.
Local time
Tomorrow, 01:42
Joined
Jun 9, 2011
Messages
81
Hello. I'm not very good at using VBA, but I've managed to write a code that saves the current report as pdf to specific folder, if this folder already exists, then pdf will be created, if folder doesn't exist then folder will be created and then pdf, which will be attached to Outlook mail after.

It's somehow working ok, but sometimes when I execute it, it creates the specific folder, but doesn't save the pdf as I get OutputTo error ' Microsoft Access can't save the output data to the file you've selected.'

I can't find where is the problem or is it correct even in the code, because it is working sometimes.

Code:
Private Sub cmdEmail_Click()

'Sets report caption name according to the invoice number
Reports!Invoice.Caption = [txtInvoiceNumber]

'Check if e-mail is set for a client
If Not IsNull([txtEmail]) Then

    Dim myCurrentDir As String
    Dim myInvoiceDir As String
    Dim myInvoiceOutput As String

    'Set directories
    myCurrentDir = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
    myInvoiceDir = myCurrentDir & "Invoices" & "\" & txtClient & "\"
    myInvoiceOutput = myInvoiceDir & txtInvoiceNumber & ".pdf"
    
    'Check if directory exists
    If Len(Dir(myInvoiceDir, vbDirectory)) = 0 Then
        'Make new one if directory doesn't exist
        MkDir myInvoiceDir
    Else
        'Save report as PDF and set OutputFile to myInvoiceOutput
        DoCmd.OutputTo acOutputReport, [Report].[Name], acFormatPDF, myInvoiceOutput, , , , acExportQualityPrint
        'Execute Outlook SendMessage function with attachment path myInvoiceOutput
        SendMessage True, myInvoiceOutput
    End If

    Else
        MsgBox ("No E-mail address available for this client")

End If
End Sub

Please I hope someone can help me with this. Thanks!
 
If the control "txtClient" contains characters that are not allowed in file/folder names it should not create the folder.
If it's a long name and the path/filename exceeds 256 characters it wil result in the error you describe.


To get the path of the database "currentproject.Path" is shorter and better to read than your current solution (you need to ad a trailing "\")
 
Hi.

txtClient is query of firstname and lastname. For example clients firstname in its table is John and lastname Smith, query makes them to one field like firstname & " " & lastname, it doesn't cause that for sure. The thing is that folder is always created, but the OutputTo output file seems to get wrong parameters or path for some reason. When I restart the database and press my E-mail button then sometimes it works, when I close one report and open another it doesn't. Every value is correct when I add the strings to VBA watch.
 
Oops missed that. You have the send mail part in the else part of your dir test. If a directory is created no mail wil be send.
 
Mail part is in else part, it's SendMessage function.

Anyway I moved all my database to hard drives root folder and for some reason it all works now. No errors so far.
 

Users who are viewing this thread

Back
Top Bottom