Have excel send an email alert automatically based on conditional formatting of a cell 1

[liamm]

Hi, I have an excel spreadsheet with some conditional formatting based on expiry dates.
The column headers are:
Column E - Date Defeated
Column F - Expiry Date
Column G - Date Re-instated

The conditional formatting covers column F and changes format based on the following:
If cell is blank no formatting
If cell =AND(F4<TODAY(),G4="") then cell turns Red
If cell =AND(F4=TODAY(),G4="") then cell turns Amber

The question I have is - can I add a code that checks the spreadsheet for Amber or Red cells at 00:01 hours every day (without the spreadsheet been open) and if there are any present it sends a generic enmial to a list of email addresses?

Hope this all makes sense. I describe myself as an intermediate excel user and not a programmer.

Many thanks,
Liam


If you can help, GREAT
If I can help, EVEN BETTER

 

[SkipVought]

hi,

conditional formatting based on expiry dates

Hence, your eMail alert can be based on expiry dates and not CF.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

(without the spreadsheet been open)

I think not.

 

[Andrzejek]

add a code that checks the spreadsheet for Amber or Red cells [...]without the spreadsheet been open

We have a 'Catch 22' here...

Can you start/open this spreadsheet from Task Scheduler at 1 minute after midnight?
Do you have Outlook? Or better yet - Microsoft Exchange?

Have fun.

---- Andy

 

[SkipVought]

I missed the closed workbook thing.

SOMETHING has to be open and running! Excel? Outlook? What application will YOU have open at 00:01 to run a procedure automatically?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[liamm]

Hi guys, thanks for your responses. The spreadsheet is accessed across a network by different people working a 24 hour shift pattern. Rather than me checking every morning or the guys needing to check every night (the spreadsheet only gets updated occasionally), I wanted the spreadsheet to automatically send out an alert.

The sheet is on a network drive if that makes any difference (sounds like it doesn't).
I may have to come up with a more rigourous manual check each day

Liam

If you can help, GREAT
If I can help, EVEN BETTER

 

[mintjulep]

So why not have the spreadsheet send out a notice every time that it is updated, rather than on a schedule?

 

[Andrzejek]

liamm,
You would have an actual chance of getting your answer and solve your problem if you would actually read the responses given already and answer questions asked.

Have fun.

---- Andy

 

[liamm]

Hi guys,
taking mintjulep's suggestion -

So why not have the spreadsheet send out a notice every time that it is updated, rather than on a schedule?
​

I don't think this would work because the spreadsheet might not be updated for a week or so but in the mean time an expiry date might pass.
I am just thinking whether it might be useful to get the guys to open the spreadsheet at least once a day and if there is an expiry date due it would then send an email - any thoughts appreciated oon this please.
liamm

If you can help, GREAT
If I can help, EVEN BETTER

 

[liamm]

Hi Andy, sorry for the delay in getting back. We do have Exchange and as stated in my last post I am thinking of getting the guys to launch the spreadsheet each day to 'check' if there are any expiry dates. I don't think task scheduler is an option as this is accessed on multiple accounts & machines on a 24/7 basis
liamm

If you can help, GREAT
If I can help, EVEN BETTER

 

[strongm]

>I don't think task scheduler is an option as this is accessed on multiple accounts & machines on a 24/7 basis

Why don't you think task scheduler is an option?

 

[Andrzejek]

If you would decide the Task Scheduler would work (around midnight every night), you can ‘commit’ a cell in your spreadsheet somewhere where you could keep the Date of the e-mails sent. You could put some code in Workbook_Open() event where you could check if your workbook is open just after midnight and the cell with your Date is other than today’s date, change this cell to today’s date. (If this cell already has today’s date, that means e-mails were already sent today, so you are done.) And then you can use Microsoft Exchange (which you said you have) to send e-mails to whoever you want with the Subject text a Body as text or HTML, your choice.

You can find an example of how to use Exchange to send e-mails from VBA code, or I can send you the example. Very easy to use.


Have fun.

---- Andy

 

[strongm]

>You could put some code in Workbook_Open() event where you could check if your workbook is open just after midnigh

Or you could pass startup switches to Excel

 

[liamm]

Thanks for the replies guys.
Andy, I think I will try this suggestion - I have seen some code for sending mail via Exchange so will try this out.
If I get stuck I will come back for some help

Many thanks,
Liam

If you can help, GREAT
If I can help, EVEN BETTER

 

[byundt]

If you can guarantee that Excel will be still running at midnight (even with the workbook containing the macro being closed), you could use an Application.OnTime method macro to check the expiry dates and send the emails. Chip Pearson shows how to set them up here:

http://www.cpearson.com/excel/OnTime.aspx

If you cannot guarantee that the same instance of Excel will be running at midnight, you can use Windows scheduler to start it and open the desired workbook. The Workbook_Open sub would then call the same macro that Application.OnTime was going to call.

http://krgreenlee.blogspot.com/2006/04/excel-running-excel-on-windows-task.html

Ron de Bruin shows how to set up the emailing part here:

http://www.rondebruin.nl/win/s1/outlook/mail.htm