Excel Due date tracker-- Auto email when due?

Microsoft Microsoft excel 2007 full vers...
September 13, 2010 at 23:12:44
Specs: Windows Vista

I sort of got stuck with a very tedious Excel tracker that tracks when people are due for their vaccinations.

I know that there are ways to set up formulas in Excel 2007 but I am pretty ignorant in the whole process. I humbly request your help.

Here is a gist of my Excel tracker:

Column A=Name | Column B=Job Title | Column C=Due Date

And then the rows fill in accordingly.

So, let's say I had this:

John Smith | Janitor | 09/15/2010

Could I somehow synch those three rows up with my Outlook contacts list and send a formatted email to them? Could I also set it up to CC me?

I would want something like, " Hello, if you are seeing this then you are due for your annual vaccination. Please complete your vaccination as soon as possible. Thank you."

Thanks for your help.

See More: Excel Due date tracker-- Auto email when due?

Report •

September 14, 2010 at 05:57:41

I can offer some help with formulas.
If you have the following arrangement

	A	B		C		D	E		F
1	Name	Position	Vacc. Due	Status	Rem. sent	Vacc. Date
2	Smith J	Janitor		15-Sep-10	Due	15-Sep-10	
3	Smith K	Clerk		10-Sep-10	Done	10-Sep-10	12-Sep-10

You can start by getting a warning when a vaccination is due.
In Cell D2 enter this formula:
This tests to see if the due date is 1 week or less away from today's date, using "IF date difference is less than 8 (days)" : IF(C2-TODAY()<8
For a longer warning increase the 8 in the formula
The formula then uses a second IF to see if the vaccination date cell has been completed : IF(F2="","Due","Done"). This returns "Due" if there is nothing in the vaccination date cell, else it returns done.

To avoid "Due" showing up when there is no date in cell C2 you can add an extra test for an empty cell:

Now to improve the visibility of vaccinations due, you can use 'Conditional formtting' which changes the appearance of a cell based on values in that (or other) cells.
Select cell D2.
From the Ribbon select Home - Styles - Conditional Formatting,
From the drop down select 'Manage Rules' and select 'New Rule'
In the dialog box select the last item in the list 'Use a formula to determine which cells to format'
Enter this formula in the box


Note that TRUE and FALSE do not have double quotes - they are Excel's logical values true and false.
Click the format button and from the 'Fill' Tab select a red color, click OK until out of the conditional formatting dialog.

D2 will now be Red if the cell contains "Due"

You could extend the warning colors to show Red when Due, but no reminder sent and Yellow when a reminder sent, but not yet vaccinated.
Select Cell D2, and follow the same steps and Add a new rule:

=IF(AND(D2="Due", E2<>""),TRUE,FALSE)
Check the 'Stop if true' box alongside the rule.
Select a yellow fill color and OK out of conditional formatting.

The formula in cell D2 can be dragged down as many rows as you have employees on your list.

To send an e-mail is somewhat more of a challenge.
You need to decide how this will be triggered.
The above scheme provides information that can be used to to test if an email is due, and an email sent date can be input in column E.
Thus if a cell in column D contains "Due" but the corresponding cell in column E is empty, an email should be sent.

The trigger for testing these conditions could be a button on the worksheet that is clicked, or it could be opening the Workbook that triggers the test.

In either case Excel can run a macro which carries out the tests and sends the e-mails.
I don't know how to link to Outlooks address book, but if you have the staff e-mail addresses in column G, then the macro can use them.

For emailing from Excel you can look at response #1 on this thread, or look at these articles on MSDN.

When you have had a look at these, you can then decide what approach you want to take and come back for further advice.


Report •
Related Solutions

Ask Question