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...
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.
Bookmarks