Pause Excel macro until MYOtherFile.vbs is finished

[johncassell]

Hi all, the project I'm working on does the following:

1. Runs company reports from Excel
2. Saves the reports as MyFileXXX.xls
3. Calls a .vbs script which opens Outlook new message, adds MyFileXXX.xls as an attachment then sends the e-mail

My code is set to do this

1. Saves the .xls file to my 'temp' folder
2. Creates the new email (via vbs script) and adds the attachment
3. Pauses for 10 seconds (to give the email chance to send)
4. Moves the .xls file from 'temp' folder to 'proper' folder

The problem I am having is that only very rarely the email is created but for some reason the attachment isn't successfully attached in that 10 second window. The 'Move File' code then moves the file. The email then looks for the file (but of course it is missing) and so sends the email without an attachment.

Ideally, instead of me saying 'Pause for 10 seconds' - I'd like to be able to say. Pause until the vbs file has sent the email.

Is there a way for me to check that the vbs script has finished?

Thanks
John

[Keebellah]

Hi, you could probably have the vbs script exit with an exit code that is passed as a parameter to Excel
Use this exit code to check if the mail has been sent, you will need a loop in Excel which checks every 1 or 2 seconds until the exit code is received and depending of this code you do the next steps.
Another way I used to to this was to have the vbs scrpt create a dummy file (no extension) named OK when it completed successfully en NOK if not
Make sure the previous OK and NOK are deleted before the next vbs runs and just check if one of these files exist.
O yes, and these files I created the %TEMP% folder
Hope this helps you a little.
You could also use Application.OnTime to run a macro to test and have Excel continue doing other things.

[johncassell]

Hi Keebellah, thanks very much for the reply. I've had a little look into the exit code but feel that creating an 'OK' file as you suggested would be the best way to go about it.

thanks again for the reply and the great response :-)

John