Check dates and then send email to user.

I need to check row records. if an update is older as 1 day, it should send an email to an user with the ticketnumber to action it.

So button should do this:

1. Check the rows for dates of old ticketnumbers
2. If the last update time is older as 1 day
3. Check which user it is from and find his corresponding email adres in the user list
4. Send that user an email with the ticketnumber to please action that ticket


Sheet 'data' Contains rows that look like this:

A B C D E F
Opened By | Status | Open Time | Last Update Time | Opened by Group | Agent

CALL4081833 | AA071 | Open - Callback | 28-7-2012 14:17 | 28-7-2012 14:17 | HELPDESK | Hans
CALL4103426 | MC0165 | Open - Callback | 14-7-2012 14:17 | 5-10-2012 14:17 | HELPDESK | Althea
CALL3349244 | SA2973 | Open - Callback | 3-7-2012 14:17 | 10-10-2012 14:17 | HELPDESK | Alex
CALL3104510 | KO6761 | Open - Callback | 29-7-2012 14:17 | 10-10-2012 14:17 | HELPDESK | Hans
CALL2944069 | AA071 | Open - Callback | 23-8-2012 14:17 | 9-10-2012 14:17 | HELPDESK | Hans









Sheet 'Results' Contains rows that look like this:

A B C
SA2973 Alex Alex@test.nl
MC0165 Althea Althea@test.nl
AA071 Hans Hans@test.nl
KO6761 Andy Andy@test.nl

Etc etc

The agent column on sheet data gets the name from the Results sheet by comparing the user ID.



So in above example, hans should get two emails, althea one.

The email should read like this:


Subject: Update needed for ticket XXXXXXXX (callno there)

Body: Dear (name),

Your callback is due for an update. The ticketno is XXXXXXXX (callno there). Please action this ticket as our customer is waiting for an answer to solve his problem.