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

DanR1245

New Member
Joined
May 12, 2004
Messages
27
Hi everyone-

I have an excel workbook that has dates entered in cells. What I want to do is write some VBA code to automatically send an email 2 weeks after a date is entered in the final cell. I have all the code written to send the email based on the user clicking a button, but I am not sure if it is possible to do the automatic email based on a value being entered in the cell.

Thanks much.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
:devilish:
Did you ever figure out how to do this? I have been batting my head and still can't get it done.

Thank you.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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

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

Code:
Sub email()
Dim myOutlook As Object
Dim myMailItem As Object
Dim FName As String
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
FName = ActiveWorkbook.Path & "\" & ActiveWorkbook.NAME
With otlNewMail
.To = "[EMAIL="d3ftr@com.net"]d3r@com.net[/EMAIL]"
.CC = ""
.Subject = "Test"
.Body = "Test"
.Attachments.add FName
.MailItem.DeferredDeliveryTime = Range("A1")
.Send
End With
 
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
End Sub

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.
 
Upvote 0
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
 
Upvote 0
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


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

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top