Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2004
    Thanked 0 Times in 0 Posts

    Deferred Delivery VBA code (2000)

    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.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Salt Lake City, Utah, USA
    Thanked 6 Times in 6 Posts

    Re: Deferred Delivery VBA code (2000)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    Re: Deferred Delivery VBA code (2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts