BULK email send: Adding "sent" check and select criteria?

**coolkev99** · 07-06-2020, 01:22 PM

Please forgive my excel/VBA ignorance. I found a code snippet online that I miraculously was able to adapt and test (it works!) to send emails in-bulk/batch. However I need 3 things added to the code that I haven't figured out...

Overview: What this VBA does is send emails to a fixed email address, only the subject and body change based on what is in the spreadsheet. (emails a ticketing system that routes based on ticket number in the subject.) I linked the macro to a button for 1-click goodness. However...

Here are the 3 things I am trying to add:

1.) Add a confirmation "are you sure you want to send emails?" once button/macro is clicked.
2.) Only send emails to records with "x" in designated column. We manually mark records that need an email send with an "x", so wanting to send emails only to those. Right now code sends to ALL in spreadsheet.
3.) Have the code add an "x" to another(different) column to signify that an email has been sent to that record. Code will not send to that record again unless "x" is cleared.

Here is pic a basic test worksheet of what I'm doing...
BULK.jpg

VBA:

Please Login or Register  to view this content.

Worksheet is attached!

**ORoos** · 07-06-2020, 10:36 PM

Hi coolkev99,

Please try attached file with the updated VBA script.

- You will get a pop-up message to confirm you want to go ahead or cancel.
- Only lines with an x in column C and blank/empty in column D will be processed.
- Once emails are processed, 'eMail sent' will be in column D (change test as required). This will need to be cleared to send again (see above).
- A confirmation pop-up message will let you know once the task is completed.

NOTE: For testing I have changed the .Send with .Display so emails open up on the screen. Once you are happy all works as required, change it back to .Send

Trust this helps.