Closing an Edited Lotus Notes Email Using VBA

Hey there,

I created an Excel-based reporting system, which uses individual files in a shared folder. The users enter their updates in their files, and then use macros in the file to
1. Request approval for their updates from their managers, and
2. Release the approved data for consolidation.

The issue I am encountering has to do with item 1. The macro to request approval opens up a Lotus Notes e-mail, populates it with the appropriate contacts, subject, and body, and then copies the relevant cells in Excel and pastes them as a picture into the Lotus Notes email (allowing the approver to view the update on their computer or BlackBerry).

The macro has worked very well in the past, but we've had one periodically reoccurring issue. From time to time when the macro is run (generally the first time the macro has been used in a while), the email will generate appropriately but the picture of the excel cells will not appear. When this happens if the user closes the email, and reruns the macro everything works properly.

We've requested that our users use this work around, but ideally I'd like to fix this in a better way. From my experience, the code to copy and paste the Excel cells in Lotus Notes always works the second time, so what I want to do is create code that would
1. create the email like it usually does
2. close this first e-mail
3. recreate the email

So far I have been able to open and close a blank email, but I have not been able to close an email that has been edited (adding in the recipient names, subject, body, etc.). When I try to close the edited email, Lotus Notes opens a window asking "Do you want to send, save, or discard your changes? Choose Cancel to continue editing". I tried using SendKeys to send "D" for discard, but I just get a little note saying that Numlock has turned off.

Anyone have any suggestions?
I'm including my code below. Special thanks to NateO for putting together the code that I worked from.

Please Login or Register  to view this content.

Thank you for your time and assistance

JSugar

Thank you so much for your response! Yes, I am still trying to find a solution to this problem. I will take a look at what you have provided and give it a shot. I'll follow up tomorrow after I have had a chance to do some testing. Thank you again for your time and assistance!

Thanks again, Chippy! I think the delay is a great solution so I am going to go ahead and implement it for our next reporting cycle. I'll follow up if I see the issue reappear after I implement this fix. Thanks again!!