Sending email via Excel VBA code based on date in cell.

Hi Dan,

Yes, this is easy to do. For example, to email the active worksheet to dan@aol.com based on the date in cell B4:

If Now() > [B4] + 14 Then

ActiveSheet.SendMail recipients:="dan@aol.com", _
subject:="Your inputs are due", _
returnreceipt:=true
End If

This uses SendMail to send the email, but you could also use Outlook automation if you want to create an email and don't want to include a worksheet.

This code could be put in the ThisWorkbook_Open event if you just want to check the date each time the workbook is opened, or you could use other events to check the date more often (such as the Calculate event, which could be used to check the date each time a calculation occurs).

Finally, you didn't mention whether you want hours, minutes, and seconds taken into account. My code assumed you do, but you could use Date() in place of Now() to ignore portions of a day.

That might work. I was actually thinking of putting on a message delivery delay. That way the excel file will not have to be edited.

Here is the situation. A customer service rep finishes up a customer call, and enters the end date (today) in the excel file in a cell. Two weeks later, we want to followup with the customer with an email to see if their concerns were met. But we don't want to have to reopen each excel workbook (which corresponds to the customer's file). What we want is for our operator to enter today's date and then click a "send email folowup" button at the close of the call. Two weeks after the call, an email will be sent.

There is an option in Outlook to do this, but I can't find how to code it in VBA.

Hi Dan,

Yes, I now understand what you want to do. It can be done using Outlook automation. Unfortunately, Outlook has no macro recording capability, so it will require coding from scratch. It would probably take me an hour to figure it out, wading through the Outlook object model. To get a quick answer I would suggest you post a request for the code on an Outlook programming bulletin board, such as

http://www.wopr.com/cgi-bin/w3t/postlist.pl?Cat=&Board=out

and

http://www.microsoft.com/office/com...soft.public.outlook.program_vba&lang=en&cr=US

Good luck, and keep Excelling.

Damon

Did you ever figure out how to do this? I have been batting my head and still can't get it done.

Thank you.

I know this post is now over 7 years old, but I see that it is still getting a lot of views, so for the benefit of those that still wonder how to do this, first create your email in Outlook (lots of postings on this site explaining how to do that), then before doing the Send, do

MailItem.DeferredDeliveryTime = Range("B4") + 14

to have it automatically send the email in two weeks after the date in cell B4. In this case your MailItem would be the name of the object variable you assigned the new email MailItem object to.

Damon

Quite right Damon, I'm another one that needs help with sending an Email from Excel.
Mine 'should' be simple (I think).
It involves sending an Email to Members when their subscriptions are due.
Using Excel 2007, Windows Live Mail.
I wish to extract 10 names and Email addresses from a Worksheet and send a simple Email to each saying "Your membership renewal was due on (date)"
I would prefer individual emails, otherwise one email would require using BCC address.
Any help greatly appreciated.
Thanks
Morris

Damon Ostrander said:

I know this post is now over 7 years old, but I see that it is still getting a lot of views, so for the benefit of those that still wonder how to do this, first create your email in Outlook (lots of postings on this site explaining how to do that), then before doing the Send, do

MailItem.DeferredDeliveryTime = Range("B4") + 14

to have it automatically send the email in two weeks after the date in cell B4. In this case your MailItem would be the name of the object variable you assigned the new email MailItem object to.

Damon

Click to expand...

I am attempting to do something very similar but when I tried the code


it errors out saying object doesn't support this type or method and highlights the line .MailItem.DeferredDeliveryTime = Range("A1") Am I missing something? Possibly a reference I need to add. I already have outlook view control and outlook 12 object library selected.

Hi furstukin,

I'm on a public computer where I can't really test this, but I believe the problem is simply that otlNewMail is itself a MailItem. Therefore you only need

.DeferredDeliveryTime = Range("A1")

inside your otlNewMail With statement.

Damon

Damon Ostrander said:

Hi furstukin,

I'm on a public computer where I can't really test this, but I believe the problem is simply that otlNewMail is itself a MailItem. Therefore you only need

.DeferredDeliveryTime = Range("A1")

inside your otlNewMail With statement.

Damon

Click to expand...

You are absolutely corrrect. It works perfectly now. Thank you so much for your help.