Macro to loop through workbooks in folder and attach and email using address in closed WB

I have a number of excel workbooks stored in a folder. Most of these workbooks contain an email address in cell A3 (the rest have A3 as a blank cell). I need to email each workbook as an attachment along with a short message in the body of the email. Where a workbook doesn't contain an email address, it will be skipped.

I need a macro that can:

1. Ask user to select the directory folder containing these files (that part is easy)
2. Begin a loop on the workbooks in this folder and:
3. If closed workbook has no email address in A3 then skip to next WB in folder (nice if it could append/prefix the file name with string NOT_SENT)
4. If closed workbook has email address in A3 then:
5. Start new email in Outlook
6. Copy the email address from cell A3 to the To: field in Outlook
7. Attach the closed workbook to the email
8. Enter a short message in the body of the email (the message is to be hard coded in the VBA)
9. Send the email and then repeat this loop with the next workbook in the folder

I realise that the above is quite an involved process however it can probably be broken down into smaller procedures (which is why I broke it down into a list).

I have no experience with working with closed workbooks nor with causing Excel VBA to interact with Outlook. Any assistance with even part of this code is appreciated.

Solved. Thanks for the help.

Originally Posted by spiwere

Dear mc84excel,

It would be great if you could share the solution on the forum. I'm also looking for a similar kind of a solution.

It will be really helpful if you could share whatever you have, and will be in the interest of all the others as well. Hope this will not be a problem.

I'm also wondering if there is something on similar lines for Google mail.

Please advise.

Best regards,
Spi

Hello Spi,

I confess I was a little disappointed in the lack of response to solving this thread.
That aside, I could share what I have except that I no longer have the original solution... (the code no longer requires the email body message hard coded in VBA, instead you now compose the generic message for the mail-out in HTML and save it into the same folder as the workbooks. It will then include this message with each attached workbook. Also I switched the email format to HTML instead of text but now I am having some issues with the signatures). But if you are still interested, let me know.

With Google mail - No idea. I suggest you open a new thread for that.

Originally Posted by spiwere

Hi,

Thanks for getting back and offering help!

Yes, I'm very much interested. Would really appreciate if you could share that. I'll go through and seek assistance if required.

Appreciate your response!

By the way, I am facing some disappointment too as haven't got any response on my last thread related to
AutoRefresh of Pivots:-(

Located at: http://www.excelforum.com/excel-char...html?p=3091505

Best regards,
Spi

Sorry for the delay in replying. RealLife got in the way. I will post the code soon.

I have difficulty using Pivot Tables so I am unable to assist with that request.

See code below. Instructions for setting up are included in comments at the top of the module. If you have any quick questions on this, post them in this thread and I will see if I can clarify any point.

Please Login or Register  to view this content.

Note that you will need to customise the code to suit your needs (i.e. change the test email address and the cell reference storing the recipients address).

BTW the code I posted wont work "as is". I put in two safety checks to prevent sending the emails out before you are ready for them to go.

To remove the safety checks, simply comment (or remove) the following lines:

• Public Sub EmailAllWorkbooksInFolder: Lines 500-520
• Private Function Send Email: Line 60