Loop to send Emails with Cell range issues

[jawkkp]

Hi Friends,

I'm having problems with the below code. I'm trying to set up a loop to send emails based on cell values. The email To and Body are also based on cell values, which is where my problems exist. I get errors each time it gets to the line with the value for the email address.

I really have no idea what I'm doing, I got most of this code from another source which I would reference, but don't remember...

Any help identifying the issues is most appreciated.

-jawkkp

Please Login or Register  to view this content.

[Leith Ross]

Hello jawkkp,

I have rewritten the macro. The email addresses are in "AT4:AT27". The email body is in cells "AU4:AU27".

Please Login or Register  to view this content.

[jawkkp]

This works great. Thank you so so so much.

[Leith Ross]

Hello jawkkp,

You're welcome. Here is a description of the problems I found.

There was a LOT wrong with the code you posted. Rewriting it correctly was easier. Indenting when you code makes it much easier to spot problems and to read. Once I did this step it was easy to see the variables for the subject, recipient (mail to address), and email body had been declared twice and the names were different. For instance, you have Mail_Subject and MailSub define. A good way of preventing duplicate variables or using variables that have not been declared is to include Option Explicit at the very top of all code in the module.

A really big issue was this line in the For Next loop...

Please Login or Register  to view this content.

This changed the value of every cell in the range "AT4:AT27" to a "Y". Making all the information in your email equal "Y": Subject, Recipient, and Body.

Generally speaking, if your code generates an error then there is a problem you need to address. It is a very bad practice to ignore the error like using On Error Resume Next with no error handler. Trapping the error and alerting the user and then exiting is a good strategy. You should always include the error number as well as the description. Error descriptions are not unique but error numbers are. The Error number can also help you identify the software component where the error occurred. Here is a example of how to do it.

Please Login or Register  to view this content.

[jawkkp]

Good advice. I really had no idea what I was doing, so I appreciate your help

Hello jawkkp,

You're welcome. Here is a description of the problems I found.

There was a LOT wrong with the code you posted. Rewriting it correctly was easier. Indenting when you code makes it much easier to spot problems and to read. Once I did this step it was easy to see the variables for the subject, recipient (mail to address), and email body had been declared twice and the names were different. For instance, you have Mail_Subject and MailSub define. A good way of preventing duplicate variables or using variables that have not been declared is to include Option Explicit at the very top of all code in the module.

A really big issue was this line in the For Next loop...

Please Login or Register  to view this content.

This changed the value of every cell in the range "AT4:AT27" to a "Y". Making all the information in your email equal "Y": Subject, Recipient, and Body.

Generally speaking, if your code generates an error then there is a problem you need to address. It is a very bad practice to ignore the error like using On Error Resume Next with no error handler. Trapping the error and alerting the user and then exiting is a good strategy. You should always include the error number as well as the description. Error descriptions are not unique but error numbers are. The Error number can also help you identify the software component where the error occurred. Here is a example of how to do it.

Please Login or Register  to view this content.