Add a DeferredDeliveryTime of two days to arrive at 9am

Question

I am trying to get the time to change to 9am. It hasn't worked or has removed the preceding DateAdd("d", 2, now) which assigns it for 2 days time.

replyEmail.DeferredDeliveryTime = DateAdd("d", 2, Now)

I tried different DateTime functions, defining certain values, e.g. Date + Time("09:00:00")

If you search this site for `[vba] DeferredDeliveryTime` you will get 31 results. Look if any of these answers your question. — Tom Brunberg, Sep 09 '22 at 10:54
[for example](https://stackoverflow.com/questions/38914222/how-to-schedule-a-delivery-of-mail-to-a-specific-time-in-vba) — Tom Brunberg, Sep 09 '22 at 10:59
@TomBrunberg I've trawled through each of these, but to no avail. Any differing attempt confirms either the date correct "11/09/2022 00:00" or the time correct "30/12/1899 09:00" but not together "11/09/2022 09:00" — WyattR, Sep 09 '22 at 11:32

score 2 · Accepted Answer · answered Sep 09 '22 at 11:57

2

After playing around for two days, it appears I've stumbled upon my answer.

    replyEmail.DeferredDeliveryTime = DateAdd("d", 2, Date) + DateAdd("n", 0, #9:00:00 AM#)

My issue was finding the right object and then how to join the two so the function didn't override itself causing only the date or time to be correct, but not both.

I'm sure there is a way to make this look cleaner and if I discover it, I'll post but this does the job for the time being.

answered Sep 09 '22 at 11:57

WyattR

21
4

Great! Have you tried it yet? (By setting your pc clock forward) – Tom Brunberg Sep 09 '22 at 12:40
@TomBrunberg I have indeed, though not setting the clock forward as that's locked behind the IT administrator team. Sent a test email to my home email address with the values changed to reflect todays date and whilst I was on lunch, the email came through. Great success! – WyattR Sep 09 '22 at 13:53
Super! and thanks for reporting back (+1) – Tom Brunberg Sep 09 '22 at 14:05
You don't need to formatt with a code fence **and** 4 spaces indent. Remove the 4 spaces from the front of the line of code for readability. – JAlex Sep 15 '22 at 16:56

score 0 · Answer 2 · answered Sep 09 '22 at 17:31

Option Explicit

Sub deferredDelivery_2D9H()

    Dim mItem As MailItem
    Dim d2_9AM As Date
    
    Debug.Print "Date + 2 time format......: " & Format(Date + 2, "ddddd hh:nn")
    
    d2_9AM = DateAdd("h", 9, Date + 2)
    Debug.Print "d2_9AM....................: " & d2_9AM
    
    Set mItem = CreateItem(olMailItem)
    mItem.DeferredDeliveryTime = d2_9AM
    Debug.Print "mItem.DeferredDeliveryTime: " & mItem.DeferredDeliveryTime
    
    mItem.Display
    
    ' "Do not deliver before" entry in Options Tracking Properties dialog
    'ActiveInspector.CommandBars.ExecuteMso ("DelayDeliveryOutlook")
    
    ' Options Tracking Properties dialog
    ActiveInspector.CommandBars.ExecuteMso ("MessageOptions")
    
End Sub

Add a DeferredDeliveryTime of two days to arrive at 9am

2 Answers2