Is it possible to have Excel send me an e-mail if certain conditions are met?

[TnD_Guy]

Okay, so take a look at my attachment. Can Excel automatically e-mail me if someone has less than 90 days until their expiration date? Like, Excel would send me an email (using Outlook) that would say "Cait Sith's certification will expire as of 1/30/17" or something like that? Or maybe I could set this up within outlook? Because I don't want to have to leave the spreadsheet open in Excel all the time, but I have my outlook open pretty much all the time I'm at my desk.

What are my options here? Thank you

[Logit]

You could either one.

For email ... yes ... your workbook would need to be open all the time.

The second option is to have a pop-up form show (in conjunction with maybe highlighting the employee's name on the spreadsheet) advising you their deadline has arrived.

The simpler of the two would be the second option and probably the most reasonable. It would show the first thing in the morning workday when you start your workbook.
No need for an email.

[TnD_Guy]

You could either one.

For email ... yes ... your workbook would need to be open all the time.

The second option is to have a pop-up form show (in conjunction with maybe highlighting the employee's name on the spreadsheet) advising you their deadline has arrived.

The simpler of the two would be the second option and probably the most reasonable. It would show the first thing in the morning workday when you start your workbook.
No need for an email.

How do I do that second option?

[Richard Buttrey]

Hi,

There's one other option.

You could have Windows Scheduler Open the Workbook after hours and in the Workbook Open event have a macro that sent any necessary email and then closed the workbook.

[TnD_Guy]

Hi,

There's one other option.

You could have Windows Scheduler Open the Workbook after hours and in the Workbook Open event have a macro that sent any necessary email and then closed the workbook.

My computer would have to be on for this to happen, right? Because Windows Scheduler can't do anything if my machine is off.

[Logit]

.
Here is a sample small project to look at. It may not look (now) like you want it to but it does the job.
You can use this project or we can implement the code into something else you prefer.

Test the project by entering dates 90 out from today. In the other column if you enter TODAYS date it will fire that warning.

Let me know.

User Form One code:

Please Login or Register  to view this content.

.
.
Code to call Forms:

Please Login or Register  to view this content.

[Logit]

.
Here is another method using Conditional Formatting :

Open attachment

[Keebellah]

I use a vbs script to open Excel and trigger any macro using windows Scheduler.

The vbs script is in the attached zip file



Give the script a name and place it in the same folder as where your Excel workbook is present

[Logit]

.
One last version.

This code in a routine module:

Please Login or Register  to view this content.

And this code in the ThisWorkbook module:

Please Login or Register  to view this content.

This version will show a Warning Message when the file is opened plus highlight the appropriate row.

[TnD_Guy]

I use a vbs script to open Excel and trigger any macro using windows Scheduler.

The vbs script is in the attached zip file



Give the script a name and place it in the same folder as where your Excel workbook is present

So I just extract this zip and place it in the same folder as the Excel spreadsheet?

[Keebellah]

Yes, and you have to edit the script to indicate the excel's filename and als which macro is to be run.

[TnD_Guy]

..........

[TnD_Guy]

Yes, and you have to edit the script to indicate the excel's filename and als which macro is to be run.

So I will need to write a macro too? I'm not exactly great at writing macros, but I can probably figure that part out.

The spreadsheet is saved on a network drive that is always on (not saved locally on my HDD). So this VBS will automatically run all the time and when the conditions are met in the spreadsheet, the macro will e-mail me?

[Keebellah]

Yes, your Excel file must contain a macro named (fer example) CheckAndSend which may NOT have any prompts or Userforms or whatever.
It checks the condition(s) and composes the mail message, sends it, updates the worksheet that that mail was sent, saves the workbook and closes the workbook or Excel too.

[TnD_Guy]

Yes, your Excel file must contain a macro named (fer example) CheckAndSend which may NOT have any prompts or Userforms or whatever.
It checks the condition(s) and composes the mail message, sends it, updates the worksheet that that mail was sent, saves the workbook and closes the workbook or Excel too.

Here's a concern. How often will this thing run?

Like, once a guy's training date has gotten close, the VBS Script will open the workbook on the server (which is running 24/7), trigger the macro and send the e-mail (which I hope I use some Concatenates and stuff on), and then save and close the workbook. All good stuff, right?

Okay, so that's great. But will it do that again the next day? Will the recipients get bombarded by e-mails every day when the VBS runs?

Also, what might my macro look like for this? Will I need to set up a special company e-mail account for my macro to use? Can I set recipients based on a database of e-mail addresses? Like, if so-and-so employee has a date that is less than 90 days from today, automatically send an email to manager such-and-such?

[Keebellah]

I understand your concern and I also read that you have absolutely no idea what you were asking.
Well, our question is if you can send an e-mail if certain conditions are met.
Well, you've got your answer(s) to that and it's up to you specify the conditions set up the formulas and write the macro that takes care of that.
All my script does it allows you to trigger excel and run that specific macro
When it runs, that's up to you.
It it's on the network and you want it to run anytime even if you do not log in then you can ask the network administrator to use the network scheduler to execute the vbs script every day at a specied hour or maybe twice a day, your choice.
If you only want it to run when you log in, you can use the PC's windows schedule and schedule it there.
In this case your own outlook account is used and it's sent in your name, on the server you can ask the network administrator if there is an account you can use and set your email as a reply to.

The recipients won't get bombarded with emails if you make sure that the code sets a value indicating that that mail has been sent so it wont send the same one again.
I think you will have to set the conditions options, message body, subject etc etc .
You asked the question now you'll have to do so (more) homework

[Keebellah]

I downloaded the files posted and will take a look.
I advice to to do a search for similar questions and look at the solutions / options offered.

[Keebellah]

If it runs from the server and you can ask the network administrator to set up the scheduler so that ti's triggered by the server at the intervals YOU decide
You will need to write the macro and edit that accordingly in vbs script.
From the server you will have to use the exchange sever and use a designated email address, the sender could be a [email protected] and the reply to be set to be sent to you or the one monitoring it.

If you want to run it when you log in, you setup Windows Scheduler on your system and it will run from there using your own outlook.

I've attached Logit's file but without the userform which is not suitable. The conditional formatting sets the day column to red if <=90 days and no mail has been sent.
The lay-out I modified including three new columns and a textbox explaining it a little.
You still have to do your part like what you want in the message and subject and who is the recipient etc etc.
I'm off to bed here its past midnight.

[Logit]

Thanks for the Add Rep.

[TnD_Guy]

If it runs from the server and you can ask the network administrator to set up the scheduler so that ti's triggered by the server at the intervals YOU decide
You will need to write the macro and edit that accordingly in vbs script.
From the server you will have to use the exchange sever and use a designated email address, the sender could be a [email protected] and the reply to be set to be sent to you or the one monitoring it.

If you want to run it when you log in, you setup Windows Scheduler on your system and it will run from there using your own outlook.

I've attached Logit's file but without the userform which is not suitable. The conditional formatting sets the day column to red if <=90 days and no mail has been sent.
The lay-out I modified including three new columns and a textbox explaining it a little.
You still have to do your part like what you want in the message and subject and who is the recipient etc etc.
I'm off to bed here its past midnight.

Thank you so much!!! Very helpful!! Sorry to have kept you awake so late.

[Keebellah]

No problem, have you been able to test it and see it it works, with the script and so?

[e4excel]

This seems like a very interesting requirement.....

[Keebellah]

It is and the problem is that sometimes the requirement is interesting but doable but the results and consequences, and of course maintaining the data is what's forgotten.
I've done quite a few of these and used i myself too.

[TnD_Guy]

No problem, have you been able to test it and see it it works, with the script and so?

Well, I don't know when I'll be able to test it because I'm going to need to use my company's e-mail server and that means I'll have to get IT involved to help me set that up. And those guys are (understandably) always busy with other projects. I'm tremendously appreciative that you've taken the time to put it together for me, though!! I'm sure it will work out for me just fine.

[Keebellah]

I don't see the problem to test it.
You can use your own system and the Windows Scheduler, it will only work when you log in but then you can see if and how it works,
Then it's just a question of sending a request to the IT department to do their part.
IT departments are always busy but even their colleagues are also their clients.

[TnD_Guy]

I don't see the problem to test it.
You can use your own system and the Windows Scheduler, it will only work when you log in but then you can see if and how it works,
Then it's just a question of sending a request to the IT department to do their part.
IT departments are always busy but even their colleagues are also their clients.

I'm just worried that if I run a macro without knowing how to properly set it up, I may wind up crashing our network by inadvertently sending 80,000 e-mails in a nanosecond because I forgot something important.

I suppose if I'm really worried, I can just pull the Ethernet cable out of my computer and mess around with it offline and then, at worst, it may mess up my computer if I do something dumb, right? And I can live with that.

so I just drop the VBS in the same directory as the workbook. Can I configure the VBS to what time I want it to trigger the macro? I could probably mess with the output too so I could see it do something other than sending an e-mail (like displaying a value in a cell or something) to see if it works and if it only does it once and all that.

I will check this out tomorrow. thank you for your help on this project!!

[Keebellah]

YOU DON'T CONFIGURE THE VBS YOU SET UP THE WINDOWS SCHEDULER TO TRIGGER THE VBS
http://www.makeuseof.com/tag/windows...r-gives-power/

Or else another Windows version

[Keebellah]

Maybe this will shed some light on how to send bulk email using Excel
https://www.datanumen.com/blogs/batc...via-excel-vba/
The vbs script will still be required once to want to trigger it using a scheduler.

[amaranthum]

Hello everyone,

I am new to excel but have come accross this topic as it is very interesting to my field.

I would need the excel to notify me via email as a person responsible, but also my contract manager when certain contract is closing expiration (for renewal procedure). So once a column Termination notice is activated, id est when a date indicated there comes, just an email with subject renewal and body which would be a text of column Name - so we know what contract it is. Obviously, if this could be done on a regular basis, without me having to sit in the file Hope I don't ask way too much.

Any help greatly appreciated...

Thanks Slavica

[Logit]

amaranthum:

Please do not utilize someone else's thread for your own.

Please start a new thread and be certain to use a Thread Title that accurately describes the issue with your project.

Thank you.

[TnD_Guy]

Maybe this will shed some light on how to send bulk email using Excel
https://www.datanumen.com/blogs/batc...via-excel-vba/
The vbs script will still be required once to want to trigger it using a scheduler.

I wish I could give you several million reps for helping me.

Your script worked perfectly and was super easy to use. Now I just need to figure out how to write a macro that will send the e-mails I'm trying to send. I'll check that link you provided.

[Keebellah]

What you do have now?
Have you been able to put together the macro?
Attach a file if you require help

[TnD_Guy]

What you do have now?
Have you been able to put together the macro?
Attach a file if you require help

I don't have a macro yet. I'm reading up on it. So far, I've found many examples of using buttons to send batch emails and stuff. But I'm looking for one that will check conditions and send e-mails based on whether or not those conditions are true.

I'm sorta trying to frankenstein something together using bits and pieces of other macros in examples. And then I will need to sit with IT to arrange for the use of the [email protected] e-mail address.

Edit: Man, I am looking at this and it's complicated. I just don't know the syntax very well

[TnD_Guy]

What you do have now?
Have you been able to put together the macro?
Attach a file if you require help

So, attached is my workbook. I can get Windows Scheduler to run the VBS you provided, and I can get the VBS to run the macro. Now I just need the macro to do what it does, and I'm realizing that this (macro stuff) is going to be the most complicated part of this whole operation.

As you can see, I have a VERY basic macro that checks the condition of "days left" and returns a "Send" or "Okay" string when I run it. My question (for now) is how do I make it check whether or not a message has already been sent (I'm sure it'll be like "If F7 = "Send" Then..." or something)? And also, how do I get the macro to run on more than just one cell? My VERY basic macro just checks one cell. And on my actual workbook, I have dozens of names. Is there a way to tell it to do a range, rather than just defining on specific cell to check and write to?

[Keebellah]

I used your first file and added the macro:
CheckAndSendMail
This is the macro that needs to be called using the vbs script.
If you open the file nothing happens, it just opens so you can modify the file
There are no email addresses in it so it won't do anything.
The Send Mail macro is called from witinh that macro, you will have to wirte the text for the subject line and the mail body, I yous placed some text in it.

The mail send will NOT send yet, the .Sedn options is disabled, it will just Save and Display the message for testing.

The file is saved and closed upon completion.
Place a ' in front of Application.Quit to keep the file open while testing then do not forget to remove the ' and also activate the .Send and disable .Save and .Display

Hope this helps you on the way

[TnD_Guy]

I used your first file and added the macro:
CheckAndSendMail
This is the macro that needs to be called using the vbs script.
If you open the file nothing happens, it just opens so you can modify the file
There are no email addresses in it so it won't do anything.
The Send Mail macro is called from witinh that macro, you will have to wirte the text for the subject line and the mail body, I yous placed some text in it.

The mail send will NOT send yet, the .Sedn options is disabled, it will just Save and Display the message for testing.

The file is saved and closed upon completion.
Place a ' in front of Application.Quit to keep the file open while testing then do not forget to remove the ' and also activate the .Send and disable .Save and .Display

Hope this helps you on the way

It works perfectly. Thank you!! This is all I need.

I still wish I understood the syntax a bit better because I have no idea how to do things like line breaks in the e-mail body and stuff, but I think I can figure this out.

One last question. How do I add a cc recipient? I figured it would be the same as the toList, just like:

ccList = ws.Cells(lRow, "J").Value

with all the cc recipients listed in column J. So I tried this, but got a syntax error.

[Keebellah]

What's in column J?

[Keebellah]

Please Login or Register  to view this content.

See the red text

[TnD_Guy]

Please Login or Register  to view this content.

See the red text

Hmmmmm... I'm still getting a syntax error for that line:

Please Login or Register  to view this content.

The line with this

Please Login or Register  to view this content.

appears to be giving me a syntax error. Any ideas?

Edit: By the way, column J is just e-mail addresses.

[Keebellah]

What is the exact error?

[Keebellah]

Here's the screen shot of the result with the code I posted, nothing changed:

[TnD_Guy]

I've attached a screenshot of the error that pops up when I run the macro.

I suppose I can try to copy and paste your whole code that you've put up, but I changed a couple of things in the body. I can just re-do them.

Here's the thing, though. My code works when I take out the line with the ccList on it. So I gotta figure that whatever I am doing wrong is coming from that line.

Edit: I went ahead and just copied and pasted your code over the one I was working with and it now works. I guess maybe there was an extra space or comma or something that I didn't see. Thank you for your help!!

[TnD_Guy]

It seems to be working properly. One last thing I'd like to bug you about. Can you tell me how to do a hyperlink in the message? I see the thing for the line break is

Please Login or Register  to view this content.

So I've been using that. But basically, I know that with HTML, my hyperlink would look like:

Please Login or Register  to view this content.

How would I do something similar in this VBA stuff?

Edit: Or is this even possible here? I see that at the bottom, my message is set to Plain Text (1). I'm guessing that a hyperlink or italics or whatever aren't going to be an option using Plain Text, eh?

[Keebellah]

You can change the body to html but then there are some changes you have to to to the text you pass to it.
I rarely use html body text so I will also have to look it up, I've got a sample somewhere.

[Keebellah]

Here's a quick link I found you only need to take a look at the msg text
http://stackoverflow.com/questions/2...-email-program
But i'll look further in my saved modules

[TnD_Guy]

Easy question, but here's the vbs you gave me:

Please Login or Register  to view this content.

What do I need to put at the end to tell it to save & close the workbook and then close Excel after it runs?

[Keebellah]

The vbs code is complete as it is.
The code to close the workbook is in the Macro you run

Please Login or Register  to view this content.

Check the text in RED in the macro you have renamed to CheckAndSendMail30

[Keebellah]

One more thing, I overlooked the macro you run when workbook opens, this should not be there, instead do this:

Workbook Open and Before Close event:

Please Login or Register  to view this content.

In the check mail macro two sections:

Please Login or Register  to view this content.

Then the closing of the workbook will not ask to be saved again due to the TODAY() formulas in the worksheet.
If the workbook is open when you are editing you van always press F9 to force calculation