Auto E-Mail to preselected list.

This is for a mailing of the standings for the NCAA tournament. I have done a search and found the following link,

HTML Code: 

. and have applied this to my sheet but I am at a loss as to how to pick up each address from column "D" on the E-Mail sheet.

Thanks

JIm O

Further in that site is this page, which shows how to cycle through a column and test for the @ symbol to determine if an email address is there...

http://www.rondebruin.nl/mail/folder1/mail5.htm

Hello Jim,

Here is a simplified version of the macro. This macro has been added to the attached workbook and to the button on the "Standings" worksheet.

Please Login or Register  to view this content.

Leith, great stuff. I need to spend more time with the SendMail stuff.

One thought, you should be able to fill your Addresses array directly without looping or even redimming.:

Please Login or Register  to view this content.

The "I" variable needed to be declared, but in this situation you won't need it at all.

One other thought, you might be able to fill in the RNG all in one step as well, with:

Please Login or Register  to view this content.

What do you think? We may only be talking milliseconds, but what the hey!

My two cents...

Hello Jerry,

Thanks for the input. Let me explain the logic behind my decisions. VBA will generally and automatically adjust a standard Array, zero based, to a Range which is one based and always 2-D and vice versa. I chose to use the loop to ensure the string Array required by the SendMail function is a always a zero based array. SendMail function invokes the default email client and may not convert the 1 based array correctly to a zero based array. The macro would then miss the first email address in the list. If there is only email being sent and there is no address then the email client will throw an error.

The code structure I use for setting the Rng object variable will not error if the cells are empty. It simply defaults to the first cell in the range. The SpecialCells property will. Also, you can not check this property first to verify there are no empty cells. You have to trap the error using an On Error Resume or On Error Goto statement. Personally, I like to avoid runtime errors whenever possible.

Hello Jim,

Try using valid email addresses on the email sheet. Here is the error I got when I ran the code. Look familiar?

Run-time error '1004'

Unknown recipient found in the recipient list. Use a valid name and try again.