Send outlook email based on employee start date?

Hi,

I'm new with VBA and I've been working on a macro to help automate sending a welcome email to new employees in my company based on their start date. I successfully managed to mess up the macro completely last Friday by trying to edit it to take the start date into account, so I'm in a desperate need of help from VBA experts.

I'll attach an example excel to this message, if this let's me do it, so you get the idea (without the macro though since that is gone, but it was really basic). I managed to create the basic macro containing the message, recipient, cc/bcc and attachments, but there I ran out of knowledge.

Basically I'd need the macro to:
-> check the start date of the employee in the row, and if its today or within ~3 days to the future (I'll fix the formula in column D, it's just there now to show you what I was thinking about), send the employee a welcome email and add their supervisor as a cc. If the date in the column is in the past, the macro shouldn't send email.

Any idea if this is even possible? I'm aware that I'll propably have to run the macro, but that's okay as long as it would check who to send the emails to.

Thanks, if someone can help me forward with this one

vba-help.PNG
VBA-example-excel.xlsx

Do you want to run the macro manually? You could have a macro to send the email automatically when the formula in column D changes to "Send".

Originally Posted by Mumps1

You could have a macro to send the email automatically when the formula in column D changes to "Send".

How would you detect that?

Could use a Worksheet_Calculate event based on the formula in column D.

Originally Posted by Mumps1

Could use a Worksheet_Calculate event based on the formula in column D.

I have not see ranman256's solution yet, but if you check every cell for "Send" when the sheet is recalculated you would repeatedly send to destinations that you've already sent to. You would need some sort of indicator that the email has been sent in addition to what the OP showed.

Originally Posted by ranman256

attached is solution.

• This solution adds a "sent" date which solves the problem I raised in my previous post. This solution could also be modified to run every time the Calculate event occurs.
• No need to zip this
• I suggest that people post .xlsx and .xlsm formats rather than .xls, which can cause compatibility issues in some cases. It became obsolete 13 years ago.

when the sheet is recalculated you would repeatedly send to destinations that you've already sent to

@6StringJazzer
Now why didn't I think of that?

Originally Posted by Mumps1

@6StringJazzer
Now why didn't I think of that?

You probably did but since the post didn't mention it I thought I would.