How can I use a range to define email recipients?

[mwolf]

Hello,

So first some background: I am using Excel 2007 and I use Outlook 2000 for email. I am automating a report that we currently do manually in anticipation of increased reporting volume. I have some VBA experience but I would still consider myself pretty novice and I've never really done anything this complex before. I have searched the internet and Excel help files for over an hour looking answers but have found none.

I have the report built and all the other functionality working how I want it. After looking at how to send emails from Excel I think I am going to just use SendKeys. The issue I am having is that I want to define the recipients of the email based on a range of cells in the report. The reason for this is that I will not be the only one using this report, so instead of "hardcoding" the recipients within the macro itself, I want to make it very easy for anyone else using this report to add or remove email addresses to a range of cells.

Any advice? Like I said I looked through help files for over an hour for some way to do this and I could not come across any examples that are even close to what I want to do.

 

[TheNoocH]

welcome to the board...

check out this link...
http://www.rondebruin.nl/sendmail.htm

 

[mwolf]

Thanks for the link! I found an example that after doing some work on I got to do what I want.

 

[mwolf]

On more question regarding this...

For the body I want to use cell references for certain data (which is fine, I can do that) but what I can't do is bold or return to start a new line.

Right now what I'm using looks similar to this:

.Body = "Good Morning Everyone " & Worksheets("BB Summary").range("E8").Value

Obviously the body is longer, I just am showing an example. One way I got around not being able to format or start a new line with the .Body object is by using SendKeys after the email is created. However, with that I can't really use cell references which is necessary.

I've seen examples using Msg and I've tried that but I can't even get the text to show up in my email body.

I feel like there must be a simple way to do this but I just haven't been able to find it yet...