VBA runtime error - Object does not support this method (when looped more then x times)

Hi Excel World,

I hoping someone can help me, I have spent far too many hours stubbornly trying to fix this but the problem which is clearly above my skill level.

I have the below Macro which in short copies data across from a spreadsheet to an email and populates the To, CC, subject, email body etc. It does not send it but leaves it open on your screen. I wanted to add another macro to loop through this existing one x amount of times depending on how big a certain list is to effectively create multiple emails.

Anyway it works fairly successfully up until about 40 loops and then I get the above error, I have no idea why and even more frustrating is that if I go the debug I can continue with the macro manually by pressing F8. I have tried tried error handling to add in pauses (I thought slowing it down would help), resume next (emails end up blank) and to start the loop again when it breaks but to no success. The error happens on " .To = Sheets("CHECK").Range("F5")"

Any ideas would be fantastic!

Macro 1 (Create the email)

Please Login or Register  to view this content.

Macro 2 (The loop)

Please Login or Register  to view this content.

I have a attached a pic to make sense of what Macro 2 is doing.


Thanks!

A picture is next to useless in diagnosing issues. Please post a sample workbook with some typical data, at least 40 rows.

Your code appeared to work OK for me in that it generated 94 draft emails.

What do you do as part of your selection process?