two things: auto email and archiving

[LoboNetwork]

Hello all..

I have an excel workbook with one sheet currently. It has many columns
and rows. Each row contains tasks and things to do.

Some of the important columns are:
1) Task Number - literally.
2) Target Closing Date - date the task should be completed.
3) Status - whether the task is open or closed (completed or not).
4) Actual closing date - actual date the task is completed.
5) Actioned by - main person involved in task.

Now what I did a few days ago was create some new columns which make
use of IF statements in excel:
1) Warning - issues warning (closed, overdue, action due, in progress)
2) Days left (number of days left till target closing date)
3) Days late (number of days passed target closing date)

We have about 200 tasks and keep adding more. Having it all on one
sheet gets hard to organize. I have some new things I want to do and
try implement.. but I have no idea how or where to start..

AUTO EMAILER

My basic idea and thoughts for auto email:

I'd like it so that 5 days before the target due date for a task, the
person in the "actioned by" column would get emailed a custom reminder
saying their task is due. Then they can go into the file, look at
their task and then do their work. We use outlook for mail. I assume
it would be possible to for excel to detect that there are 5 days left
for a task and send a reminder email to the person involve and that its
VBA related?

MUTLIPLE SHEET \"ROW\" ARCHIVING

This idea recently came into my mind. There are so many tasks that it
gets somewhat hard to manage. Is there a way to make it so that when
the "Status" is "closed", it automatically takes the row of the task
involved to another sheet. So that all the closed items get moved to
another sheet automatically? Also in this second sheet, once an item
is closed for a month or so, maybe those items can go into another
"third" sheet called "Archives"? Once again I think the idea is great
but i have no idea where to start it.

Are these two doable? Any help and tips would be greatly appreciated.
Even a point in the right direction....

thanks

 

[Ron de Bruin]

Hi LoboNetwork

Point 1)

You can adapt this one for mailing
http://www.rondebruin.nl/sendmail.htm#message

In this example it test the Column "C" for yes or no
You can use the Column with
<Days left (number of days left till target closing date)>
To check if you will send the mail


Point 2)

You can filter and copy
See Debra's site for examples
http://www.contextures.com/xlautofilter03.html

Play with this information and post back if you need more help

 

[LoboNetwork]

ahh i tried to do some of this stuff but i still dont understan
anything.... dont know what to change to make it work with what
need..