Deferred Delivery VBA code (2000)

[DanR1245]

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 how to code in VBA the "Deferred delivery" option available in Outlook.

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.

[JohnBF]

Dan, this question can be answered in a few sentences or a few pages. I'll start with the sentence approach.

1. Search the Excel Forum for examples of sending a message by creating an instance of Outlook. (Don't look at those posts which use the Sendmail method.) <!post=Here's,82332>Here's<!/post> a good example.

2. When in the instance of Outlook you create the new MailItem with the Excel spreadsheet attached, also set the following Property for the new MailItem:

mynewMailItem.DeferredDeliveryTime = Now + 14 ' (System Date + System Time + 14 days)

3. Cross your fingers, as Deferred Delivery is not 100% reliable.

Many Loungers here and in the Excel Forum will be happy to help you with more detail and assistance if you should need it: post back to this thread.

[jscher2000]

To not answer your question... are you sure this is a good idea? If there were communications in the interim and someone got a message generated two weeks earlier, apparently written with no awareness of the intervening communications, he might think the sender a bit disorganized. Perhaps this is impossible in your scenario, but I thought I'd mention it.