need a macro that will send an email or emails from a list of emails

Column A = persons name
column B = persons email address
Column C = date email needs to be sent
Column D = has email already been sent Y/N

Can optionally create column E if it makes this macro easier = Does an email need to be sent today Y/N

I will have a macro that continuously adds new addresses to the bottom of this list, it will also populate the date column at this point

I want a macro that I can run daily that will check Columns C&D (or E) and send an email to that person if the date in column C is today (or earlier) AND the person hasn't already had an email sent to them.
So the macro needs to also update Column D for each email it sends, might be helpful to have it add the date the email was sent in Column F as well.

The macro can either email each person individually or BCC them all on to one email, I don't expect much more than 10-15 email addresses per day.

Here you go

Please Login or Register  to view this content.

Thanks,

what criteria does the code use to determine if a cell is not an email ?

I have tested this and purposely missed the ".co.uk" off the test email I was using, the macro runs ok when using .display. (doesn't flag the mail as bad)
However when I switch to .Send those bad emails create an error and trigger the error handler.

Error #-2147467259
Outlook does not recognise one or more names.

The process to check emails was written by one of the gurus here. It definitely is not foolproof, as you have found.

Originally Posted by maniacb

The process to check emails was written by one of the gurus here. It definitely is not foolproof, as you have found.

Is there anything that can be done in this case to have it ignore the error ?
I am guessing not as its essentially an outlook error.

I have put further checks in, earlier in the process to try and prevent bad email addresses making it through but that will not be infallible.

Try this approach. Instead of notifying you of the error in VBA, this process will just continue sending emails, but you won't know that their is a bad email if the internal check doesn't catch the error. I'm assuming the email error you received is generated through VBA, but if it is generated by Outlook, we may be out of luck.

Please Login or Register  to view this content.

Originally Posted by maniacb

Try this approach. Instead of notifying you of the error in VBA, this process will just continue sending emails, but you won't know that their is a bad email if the internal check doesn't catch the error. I'm assuming the email error you received is generated through VBA, but if it is generated by Outlook, we may be out of luck.

Please Login or Register  to view this content.

This does exactly what you said.
If I check the sent items the bad email does not show as a sent item, and it does not get flagged in excel.

I assume from your post that there is no way to work around this ?


Regards
James

It sounds like the VBA produced the error code. Let's try the following approach to annotate the bad email in your spreadsheet.

Please Login or Register  to view this content.

That produces the same result.