Sending Outlook Emails from/using Excel VBA - Outlook and Excel

Dear Excel VBA experts,

I have a problem and hopefully you could help me out here. I have also attached an excel file for scenarios, as well as my desired outcome.

What I wanted on the final outcome is to have emails that I have created in Excel, to be sent out to the respective addresses (to, cc and/or bcc), along with the message and the signature.

In the attached example, there should be a total of 3 emails to be sent out (assuming that "to", "cc" and "bcc" are included as 1 email, as the content of the body is the same).

For example, in scenario 1, there should be 3 email addresses in the "to" and "cc" fields and 1 email address in the "bcc" field. With the message of the header (Excel Cell Range A1 to D1) in Excel as well as how it is shown in Excel of Cell Range A2 to D2, and finally the signature.

In scenario 2, 1 email address in the "to", "cc" and "bcc" field with header (Excel Cell Range A1 to D1) as well as Excel Cell Range A3 to D7, and the signature at the bottom.

In scenario 3, only 1 address in the "to" field, with header (Excel Cell Range A1 to D1) and Excel Cell Range A8 to D9, and the signature at the bottom.

So basically, column F is the "criteria" to check how many "lines" of data to input on the body before it decides to create a new email to send.

I hope that with my attachment and a little of explanation here, you understand completely what I desire to achieve.

Your help is deeply appreciated in making my work life easier!!!

I have written some code based on your posted workbook example - if the workbook is set up this way then the following code should work. It will calculate what data to attach to each email based on how your example workbook it setup. I have set up the code to Display the email - all you need to do is comment this out and uncomment the .send - if you have code set to send then the email will be sent automatically so I will leave this to you to decide - it is commented in the code as follows. I have attached an example workbook with the code so make sure it works to your specifications. I would recommend when you use this on real data that you leave the code to Display only just until the code has been tested to your satisfaction that the correct data is being placed into the email. You can change the various attributes such as the signature and leading comment in the body of the email - this again has been commented in the code so modify it to your needs. If you need any help then post back.
I have placed a button on the worksheet to run the code. There is error handling in the code so if there are any errors then please make sure you record the entire error when posting back or do a screen dump.

Please Login or Register  to view this content.

Fantastic - if you need any help just ask and thank you for the reps. Much appreciated

You would need to send the body as HTML and you can do this using html tags however you need to use the .htmlbody parameter and not .body. If you attach a word document with the signature exactly how you want I will write some html code to add for the signature. If you know html then have a look at http://www.excelforum.com/excel-prog...mail-body.html
If it is easier just attach a word document with the signature layout and I will write the html to add to the macro

Originally Posted by smuzoen

You would need to send the body as HTML and you can do this using html tags however you need to use the .htmlbody parameter and not .body. If you attach a word document with the signature exactly how you want I will write some html code to add for the signature. If you know html then have a look at http://www.excelforum.com/excel-prog...mail-body.html
If it is easier just attach a word document with the signature layout and I will write the html to add to the macro

Hi Smuzoen,

Was reading your post on Auto generating Emails. I need a Help on the same line.

Need a help from you, Please.
In the Macro should only pick those lines who's "Forcasted Deliveries Start Date" in column G is less than todays date and "Balance Qty" in Column I has some value (more than Zero).
I am referencing the attached file

Your help in this would be highly appreciated.

Thanks,
Chetan

Chetan,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Hello everyone,
New to this forum. I have been looking at the email sending coding. This is very nicely done. What I have been trying to do is similar and yet different. What I need is a macro that will respond to a yes or no in a column then copy a worksheet to another adding the fields to email. I'm not sure if I can used some of the code. Please let me know if this makes sense to you or not.
Please see new thread.