Optimize Macro for Sending Automatic Email via Lotus Notes

[Kaustubh1040]

Dear Friends

Need your help. I am looking for a VBA code to send automated email ( taking values and email id from an Excel File) through Lotus notes. The attached macro is working fine but need some changes in it.

I will be grateful if someone can help in making the required changes in the attached file.

Current Scenario:

a) Create an Email in Lotus notes based on given data in excel file
b) A Popup asking for Word file consisting of message in it.
c) Send the email taking email ids from excel file and message from the word file.

Now, I want the following changes to be made in the attached macro file

a) The current macro is asking a Word File for every email id. For example, If my file is consists of 100 email ids then it will ask for Word file for 100 times. How to remove this? I want the word file to be asked for one time only. And then same message should be used for all the email ids.

b) Before sending email, It ask for the confirmation. Once again, this confirmation is asked for every single email ids? How to remove this? I don't want any confirmation to send email.

c) Currently, the macro is taking too much time to process the emails. For one email id is taking approximately one minutes. How to optimize the macro to increase the speed of sending email ids. It is not possible for an users to wait for 100 minutes to send emails to 100 email ids.

d) Sometimes, the macro get hanged at second line items saying "Microsoft Excel is waiting for another application to complete an OLE action". Please help to fix this problem.

e) How to optimize this VBA code? Currently it is consuming too much time and memory. User can't work on any other application while running this macro. It is possible to run this macro in the backed, so that the user can work simultaneously on other applications.


The Macro file is attached with this post for your refernce.

[maniacb]

To prevent the code from asking to confirm each email, you can edit out the three lines below with an apostrophe at each line

Please Login or Register  to view this content.

I'll take more time to review it

[Kaustubh1040]

To prevent the code from asking to confirm each email, you can edit out the three lines below with an apostrophe at each line

Please Login or Register  to view this content.

I'll take more time to review it

Thanks for answering one of my queries.

Request to help for other queries also. I can understand it will take time and don't worry, this project is not my urgent list. I can wait.

[maniacb]

I don't have access to LotusNotes and therefore can only make recommendations on untested code.