Solved Problem with attaching a report to a mail (1 Viewer)

[KitaYama]

I'm trying to
1- Export a report as pdf
2- zip and password protect the pdf,
3- Attach it to a mail
4- Send it to a customer.

If I step into the code with F8, everything is just fine. Not even a single error. The task is done.
But when I run the same code normally, the line that adds the attachment to the mail throws the following error.

I assumed the zip file is not ready. Added the following to the end of the function that creates the zip file:

Do Until Len(Dir(zippedFileName)) > 0
    DoEvents
Loop

now the error is changed as following:

If I click Debug button the following line is highlited:

.Attachments.Add zippedFileName

From there, If I use either F5 or F8 the code continues and the file will be attached.

Can anyone advice what causes this behavior and how I can bypass this error?
And why DoEvents loop is not enough to wait for the file being ready to be added?

thank you.

 

[Minty]

How are you creating the zip file?

If you are using a shell command you could use a routine ShellandWait I've got somewhere that will do what it says on the tin.

 

[KitaYama]

How are you creating the zip file?

Yes, as you guessed it's done by shell command.

    Randomize
    Password = GeneratePassword(10)
    ClipBoard Password
    If MsgBox("Do you Want to Passward Protect the file？", vbYesNo + vbDefaultButton1 + vbQuestion) = vbYes Then
        If fFileExists(FilenameToBeZipped) Then DeleteThisFile FilenameToBeZipped, False
        Shell WinRarPath & " a -afzip -p" & Password & " -ep " & FilenameToBeZipped & " " & ThisFile
    Else
        Shell WinRarPath & " a -afzip " & " -ep " & ZipName & " " & FilenameToBeZipped
    End If

I thought above DoEvents loop can be used to wait until the file is ready. Am I wrong?

Thank you.

 

[arnelgp]

wait until the file is ready. Am I wrong?

you are only testing for the "existence" of the file.
it may exists but Winrar may not yet finished zipping the file.

 

[Minty]

I think this thread will assist you.

ShellAndWait which actually works, returns the program's Exit Code, WindowStyle spec

Following is my solution for ShellAndWait which actually works, provides back to VBA the Exit Code of the called external program, AND allows the WindowStyle to be specified. Rem /************************************************************************************/ Rem /* FILENAME ...

www.access-programmers.co.uk

It might need adapting for 64 bit if that's what you are using.

 

[arnelgp]

aside from testing for the "existence", you may want to test if the file is still being used:

' https://exceloffthegrid.com/vba-find-file-already-open/
Function IsFileOpen(filename As String)

    Dim fileNum As Integer
    Dim errNum As Integer

    'Allow all errors to happen
    On Error Resume Next
    fileNum = FreeFile()

    'Try to open and close the file for input.
    'Errors mean the file is already open
    Open filename For Input Lock Read As #fileNum
    Close fileNum

    'Get the error number
    errNum = Err

    'Do not allow errors to happen
    On Error GoTo 0

    'Check the Error Number
    Select Case errNum

        'errNum = 0 means no errors, therefore file closed
        Case 0
            IsFileOpen = False
 
            'errNum = 70 means the file is already open
        Case 70
            IsFileOpen = True

            'Something else went wrong
        Case Else
            IsFileOpen = errNum

    End Select

End Function

Do Until Len(Dir(zippedFileName)) > 0
DoEvents
Loop
Do While IsFileOpen("thepath+filename to check") <> 0 '0 meaning file is not Open by any other process
'code goes here
DoEvents
Loop

 

[KitaYama]

@Minty thanks for helping.

I used the mentioned code, but I received several errors. It's 21:30 local time, I'm home and I tested the code with a remote desktop app. So maybe it's because of permission or something in that line. I will check again as soon as I'm back to my desk (10 hours from now)

thanks again.

 

[KitaYama]

@arnelgp
I added a 2 seconds sleep to be sure winrar is done with the file.
The code run without any problem.

Since I can't be sure how long the code have to wait (depending on the size of the pdf) I removed the sleep and decided to use your code.
It did the magic. Everything fine now.

Million thanks.

 

[KitaYama]

I think this thread will assist you.

It might need adapting for 64 bit if that's what you are using.

I tested shellandwait but as I explained earlier, I received an error. It seems it happens when the file name and path is in Japanese characters (Kanji).
For now, @arnelgp's solution seems to be simpler and my problem is solved. So I don't try to find a solution for that.

I really appreciate you trying to help and put your time to solve this.
Million thanks.