Send email based on deadline date

**SAsplin** · 10-09-2013, 05:32 AM

Hi folks,

There seems to be a lot of similar questions in the archives here but none have been adequately solved. So, it's obviously a bit of a puzzler. I'm unable to upload any documents so I'll try to clearly explain what I'm after:

I have an excel spreadsheet that lists tasks, who has been assigned to them, their email address and the task deadline. Column P (from row 6 down) contains the email addresses. Column Q contains the deadline dates.

What I need is some VBA code that will email an individual to remind them that the deadline for their task is due say, in 15 days. This could just be a line in the subject saying "Your Task Deadline is Approaching. Please Review".

So, this code should email just the person who's task is due (on that row) NOT to all addresses in the email column (column P).

Can anyone help with this? Or will it be condemned to the archive unanswered like all the others?

Now there's a challenge.....

SA

**OllieB** · 10-09-2013, 05:41 AM

SA, what is your e-mail client, Outlook, Lotus Notes, G-mail etc?

**SAsplin** · 10-09-2013, 05:47 AM

OllieB - we are using Outlook 2007.

**OllieB** · 10-09-2013, 06:09 AM

I do not have Outlook myself but the following code should work

Please Login or Register  to view this content.

**OllieB** · 10-09-2013, 08:59 AM

@SA,

did you have a chance to test?

**SAsplin** · 10-09-2013, 10:01 AM

OllieB - Apologies, I was called away from my desk. Where should I post this code? When I run it I get a run-time error 13: Type Mismatch which debugs on the following line:

Please Login or Register  to view this content.

Some other points to consider?
1. The spreadsheet is actually called 'Tracker' although I amended that in the code...
2. The email addresses are vlookup formulas pulling data from another part of the sheet
3. The deadline dates are entered as DD/MM/YYYY and the cells are set to that format.

Many thanks for your help,

SA

**SAsplin** · 10-09-2013, 10:59 AM

Can anyone help with this?

**OllieB** · 10-09-2013, 11:23 AM

No patience? :-)

change the line to

Please Login or Register  to view this content.

If the value in column Q is a proper date the above should work, the displayed format is not relevant. That the e-mail address in column P is a formula is not relevant. The value will be picked up correctly.

**SAsplin** · 10-09-2013, 11:30 AM

Ha Ha! Woken up early this morning by my children - I had little patience then - now I'm just knackered!

Thanks for the updated code.

Checking now..........................................................................................

**SAsplin** · 10-09-2013, 11:36 AM

Ok. The code ran fine up until:

Please Login or Register  to view this content.

Then it debugged with run-time error 438: object doesn't support this property or method

**OllieB** · 10-10-2013, 01:54 AM

Found the problem, mistake on my side. The corrected code is shown below

Please Login or Register  to view this content.

**SAsplin** · 10-10-2013, 04:36 AM

Morning OllieB - thanks for the post - I really do appreciate your help. It's almost right, but there seems to be one issue - it seems to send out an email for each item where the date is greater than today but less than the date in column Q. i.e. it's ignoring the 15 day prompt.

**OllieB** · 10-10-2013, 04:44 AM

the current version selects all tasks with a due date after today

Please Login or Register  to view this content.

and a due date before (today + 15 days).

Please Login or Register  to view this content.

I fail to see anything wrong with this logic

**SAsplin** · 10-10-2013, 04:52 AM

I can't explain it either - but that's what it seems to be doing. Could I ask what the "d" does in the DateAdd command?

Also, why does this line reference column B?

Please Login or Register  to view this content.

I'm just trying to get my head round how this code works!

**OllieB** · 10-10-2013, 04:59 AM

1. The "d" in the DateAdd instruction is used to specify to add 15 days (=d) to the current date (=Now). Y9ou can use this instruction to add days, months or years to a given date

2. The B is the column that is used to determine the number of rows on the worksheet. I honestly can't remember why I chose column B. You can change it to P or Q whichever you prefer.

with regards to the selecting the tasks for the e-mails to send, I read your original post again and I now think you only want to send a reminder when the due date is exactly 15 days away from today. IF... that is the case

REPLACE

Please Login or Register  to view this content.

BY

Please Login or Register  to view this content.

**SAsplin** · 10-10-2013, 05:10 AM

Yup. The idea is to send out an email exactly 15 days before the deadline date. I have changed the code as suggested above but now it doesn't send any emails. The code runs without debugging - just no emails are sent.

**SAsplin** · 10-10-2013, 05:16 AM

Hold on! I've just re-pasted your original line back in and it seems to be working! Finger's crossed.

Just one more question - and then I'll fill your reputation box up with praise - is there a way to NOT send an email if there is a value in column R. Just to throw a spanner in the works!

**OllieB** · 10-10-2013, 05:22 AM

Of course there is.

Replace

Please Login or Register  to view this content.

By

Please Login or Register  to view this content.

Now it will only select tasks if the due date is exactly 15 days frrom now and if there is NO value in column R on the same row

**SAsplin** · 10-10-2013, 05:32 AM

Oh my God! It's a kind of magic! Column R is for entering the date when the task is completed, and I didn't want an email sending out if the task was already done.

Works like a charm.

OllieB, I prostrate myself before your genius. Many, many thanks for your time, patience and effort. I shall of course add significant reputation for you.

**OllieB** · 10-10-2013, 05:43 AM

You are too funny

**ankushnayyar** · 06-05-2014, 05:48 PM

This is a great example of automation. I was looking for exactly the same thing. I see this post is almost two years old and if you are still an active member, would you be able to answer one question.

Is there any possibility that this logic can work without the workbook being opened. I belive that if no one opens the workbook then the macro will not run? RIGHT

Thank you for your help in advance.

**OllieB** · 06-06-2014, 03:02 AM

The code is contained in the workbook. If the workbook is not opened the code would not be executed.

But you can create a different solution using a separate workbook to send the e-mails and schedule the daily opening of that workbook using Windows scheduler.

**ankushnayyar** · 06-09-2014, 05:53 PM

Thanks Ollie. I'll use the Scheduler.
Regards

Send email based on deadline date

LinkBack

Thread Tools

Rate This Thread

Display

Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Re: Send email based on deadline date

Thread Information

Users Browsing this Thread

Similar Threads

Send email based on date value

Automatically send an email based on the value of a cell and/or trigger date

VBA code to send automatic emails via outlook based on deadline

excel send an email based on cell value (Date)

Send email when deadline date is approaching

Bookmarks

Bookmarks

Posting Permissions