VBA to create PDF and folder if doesn't exist!

[crxftw]

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.

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!

[boblarson]

Well, for starters your report name needs to be put in using a string.

So:

DoCmd.OutputTo acOutputReport, "Report Name Here", acFormatPDF, myInvoiceOutput, , , , acExportQualityPrint

Where is this click event? Is it on the report itself or on a form?

[crxftw]

OnClick event is on the report itself. If I use Report.Name then it automatically picks the report I have open, but guess I could fix that.

Anyway, I moved all my database files to hard drives root folder and all suddenly seems to work, no errors so far.