[TBaker14]

Hi again,

I've read through numerous posts relating to this topic, but I'm having challenges. What I would like is to create a macro that will send an email to defined recipients IF a range of cells have values that meet a certain criteria (either the colour code or the value).

I'll make a button to run the macro manually.

Any help would be appreciated. Perhaps someone can look up a specific post that relates to my question...cause there are so many, I can't find one.

Thanks!

TBaker14

[TBaker14]

Hmmm...perhaps another tough one since there have been no replies to date.

TBaker14

[TBaker14]

Still nothing, eh?

[Techyy]

I may give my hands. I had been trying this for a while.. will test it and let you know if i get through.

As of now, By clicking on a button, i am able to open a default e-mail template with some defaults mailing lists and copy the contents from Excel and but not able to paste it in Outlook Email..

[TBaker14]

Hey Techyy....thanks for the update! I appreciate it!

TBaker14

[Keebellah]

Hi, I get an error 'Can't find project library' but that won't bother me.
Just a question:
As far as I can ake out from your Auto_Open macro you sort column F on date?
You have several sort routines in thre that don't make sense to me.
Correct me if I'm wrong but you want the anniverasry dates sorted (how?) and then you want to send an email to the person or persons whose anniversary date is almost there, right?
Based upon these assumptions I guess that it's simple but please verify.
Thanks

[TBaker14]

Yeah...I've actually made significant changes to the spreadsheet since my original post. However, my question is still the same. Currently, it sorts by Anniversary date and highlights all dates in the current month red. I would like the email to send just the names of the employees with dates in the current month.

[Keebellah]

Is the red of any importance or just to show you which will be sent?
And to whom will the email be sent? is that a fixed email address or ... ?
I'll take a look at it tonight and see if I can work something out for you based upon your initial attachment

[TBaker14]

The red is just to show which will be sent.
It will be a fixed email address.

[Keebellah]

One thing of course, if it's a fixed email address, will it be one list of names + anniversary date or will it be separate emails?

[TBaker14]

Not sure what you mean. I would like the macro to send an email each time it's run with each name + annv date in the email.

Is that what you're asking?

[Keebellah]

I had to edit the Auto_Open and get rid of the error I kept getiing.

It's sorted in descending Anniversary date, the colouring you used as conditional formatting I left untoouched.

The button triggers the function to send the anniversary list and will only create a list of those that are red.

See if you can work it out from here, at least you have a working macro button.

[TBaker14]

Wow...that's great! I wish I understood the VBA code more so that I could edit it...but I'm still a newbie. I'll take it from here.

You're help is much appreciated.

TBaker14

[Keebellah]

Just ask if you need assistance.
If the post is solved don't forgte to press the button on top to mark it as solved

[TBaker14]

Wow...this is frustrating because I don't understand the difference between a Macro and the code you created to send the email. I'm trying to transfer your code to my new spreadsheet (made enhancements) and I really don't understand it the process. I tried copy and paste to create a new macro using your code, but then it doesn't show up as a macro. I tried putting it at the bottom of another macro I have (just as you have it underneath the Auto_Open - sort macro...but that doesn't work either.

I'm really confused. I've attached a recent copy of my spreadsheet. Do you think you could try to put it in the new one?

Thanks,

TBaker14