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.

@dutchdog,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.