Outlook: Improve Delay Delivery (Part 2)

A couple months ago, I wrote up an initial implementation of adding a button to Outlook that would allow me to ‘delay delivery’. I took the basics of the code from a SuperUser post and turned it into a macro. Basically, the VB code checked to see if I was sending the email at weird hours (before/after work on a weekday, or on a weekend) and then shifting the time to be more reasonable (by which, I mean, 6am the next weekday morning). If I didn’t click the macro button, however, the email would be sent as usual.

Having used and rather enjoyed this feature during this summer, I decided that it was time to revisit my implementation. Namely, I was repeatedly annoyed when I would click the macro button hoping for my mail to be delivered a couple hours in the future, but, since I was within my regularly scheduled hours, nothing would change. If I don’t want a delay, I can use the normal Outlook send operation, and so clicking this macro button should always result in a delay. For the sake of argument, let’s delay it by at least two hours.

Quick Bug Fix: Always ‘Immediately Delivered’

While implementing the above changes, I found a fix for a bug that was always causing the message ‘Mail will be delivered immediately when sent’, even though it would eventually report that delivery had been successfully delayed.

' If mail is currently delayed, remove the delay and quit
    If objMailItem.DeferredDeliveryTime = NoDeferredDelivery Then
        objMailItem.DeferredDeliveryTime = NoDeferredDelivery
        MsgBox "Mail will be delivered immediately when sent", _
                vbOKOnly, "Deferred delivery removed"
    End If

There are a couple things wrong here:

objMailItem.DeferredDeliveryTime = NoDeferredDelivery: this should be an inequality. We should be checking ‘if there IS deferred delivery’, then remove it. (Or, more in line with the code ‘if there IS NOT NO deferred delivery, remove that deferred delivery.)
The function does not exit, so the rest of the code always runs and delayed delivery cannot be removed/unset. We can add exit Sub to the end of the function.

Always Delay Delivery

Here’s the code which set the delivery delay, and we want to change line 59 to always delay mail:

    ' Set the date appropriately for the next weekday
    If Weekday(Date, vbMonday) = 6 Then
        ' Today is Saturday
        ' Delay mail two days
        SendDate = Date + (2)
        MailIsDelayed = True
    ElseIf Weekday(Date, vbMonday) = 7 Then
        ' Today is Sunday
        ' Delay mail one day
        SendDate = Date + (1)
        MailIsDelayed = True
    Else
        ' Currently a weekday
        ' See if it's inappropriate to send mail right now
        If DatePart("h", Now) < DelayMailBefore Then
            ' It's early morning - delay it
            SendDate = Date
            MailIsDelayed = True
        ElseIf DatePart("h", Now) > DelayMailAfter Then
            ' It's late night - delay it until tomorrow morning
            SendDate = Date + (1)
            MailIsDelayed = True
        Else
            ' It's okay to send mail during this time
            ' Don't enable delayed send
            MailIsDelayed = False
        End If
    End If

The first if/elseif are doing their job properly. In the else on line 45, we want to always add our 2 hours to the current time so that Now + 2 hours is the soonest mail will ever be sent.

NowPlus2 = DateAdd("h", 2, Now)

Or we can parameterize the 2:

Dim NowPlus As Integer              ' Number of hours to send in the future (at a minimum)
NowPlus = 2                         ' Default to 2 hours in the future

...

NowPlus2 = DateAdd("h", NowPlus, Now)

Now, we just need to use our NowPlus2 variable instead of Now.

    ' If I clicked this button, I always want a mail delay
    NowPlus2 = DateAdd("h", NowPlus, Now)
    ' Currently a weekday
    ' See if it's inappropriate to send mail right now
    If DatePart("h", NowPlus2) < DelayMailBefore Then
        ' It's early morning - delay it
        SendDate = Date
        MailIsDelayed = True
    ElseIf DatePart("h", NowPlus2) > DelayMailAfter Then
        ' It's late night - delay it until tomorrow morning
        SendDate = Date + (1)
        MailIsDelayed = True
    Else
        ' It's okay to send mail during this time
        ' Don't enable delayed send
        SendDate = NowPlus2
        SendTime = ""
        MailIsDelayed = True
    End If

The else section probably requires some explanation. Since we’re setting SendDate to a DateTime rather than a Date, we will null out SendTime which is ” 06:00:00″ otherwise. That way the DeferredDeliveryTime is set to SendDate & SentTime where we might have:

SendDate is a DateTime and SendTime is an empty string, which results in a DateTime string
SendDate is a Date and SendTime is a string specifying the Time component, which results in a DateTime string

Final Macro Code

See the final code for the macro. See my original post for how to add the macro back into Outlook as a button in the ribbon.

Please note, that as of August 2020, the syntax highlighter plugin I use is converting & to & so please replace these if you copy/paste the code.

Sub DelaySendMail()

    On Error GoTo ErrHand               ' Error Handling
    
    Dim objMailItem As MailItem         ' Object to hold mail item
    Dim SendDate As String              ' The date to send delayed mail
    Dim SendTime As String              ' The time to send delayed mail
    Dim DelayMailAfter As Integer       ' Latest hour mail is sent live
    Dim DelayMailBefore As Integer      ' Earliest hour to send mail live
    Dim MailIsDelayed As Boolean        ' Set if the mail will be delayed
    Dim NoDeferredDelivery As String    ' Value if deferred delivery is disabled
    Dim NowPlus As Integer              ' Number of hours to send in the future (at a minimum)
    
    SendTime = " 06:00:00"              ' Time to deliver delayed mail (6AM)
    DelayMailBefore = 5                 ' Delay mail sent before 5:00AM
    DelayMailAfter = 17                 ' Delay mail sent after 8:59PM
    MailIsDelayed = False               ' We assume it's being delivered now
    NoDeferredDelivery = "1/1/4501"     ' Magic number Outlook uses for "delay mail box isn't checked"
    NowPlus = 2                         ' Default to 2 hours in the future
    
    'Set object to mail item you have open
    Set objMailItem = Outlook.ActiveInspector.CurrentItem
    
    ' Check and make sure current item is an unsent message
    If objMailItem.Sent = True Then
        Err.Raise 9000
    End If
    
    ' If mail is currently delayed, remove the delay and quit
    If objMailItem.DeferredDeliveryTime <> NoDeferredDelivery Then
        objMailItem.DeferredDeliveryTime = NoDeferredDelivery
        
        MsgBox "Mail will be delivered immediately when sent", _
                vbOKOnly, "Deferred delivery removed"
        Exit Sub
    End If
        
    ' Set the date appropriately for the next weekday
    If Weekday(Date, vbMonday) = 6 Then
        ' Today is Saturday
        ' Delay mail two days
        SendDate = Date + (2)
        MailIsDelayed = True
    ElseIf Weekday(Date, vbMonday) = 7 Then
        ' Today is Sunday
        ' Delay mail one day
        SendDate = Date + (1)
        MailIsDelayed = True
    Else
        ' If I clicked this button, I always want a mail delay
        NowPlus2 = DateAdd("h", NowPlus, Now)
        ' Currently a weekday
        ' See if it's inappropriate to send mail right now
        If DatePart("h", NowPlus2) < DelayMailBefore Then
            ' It's early morning - delay it
            SendDate = Date
            MailIsDelayed = True
        ElseIf DatePart("h", NowPlus2) > DelayMailAfter Then
            ' It's late night - delay it until tomorrow morning
            SendDate = Date + (1)
            MailIsDelayed = True
        Else
            ' It's okay to send mail during this time
            ' Don't enable delayed send
            SendDate = NowPlus2
            SendTime = ""
            MailIsDelayed = True
        End If
    End If
    
    If MailIsDelayed Then
        ' Mail should be delayed - set the delivery date/time
        objMailItem.DeferredDeliveryTime = SendDate & SendTime
        MsgBox "Mail will be delivered at " & _
                SendDate & SendTime, _
                vbOKOnly, "Mail delayed"
    End If

    Exit Sub

ErrHand:
    ' Handle well-known errors with message
    ' Other errors, just tell the user
    MsgBox "An error has occured on line " & Erl & _
                ", with a description: " & Err.Description & _
                ", and an error number " & Err.Number & _
                ", error " & Err
    If Err.Number = 13 Then
        ' No current item or current item isn't a mail message
        MsgBox "Future delivery can only be set on mail items", vbOKOnly, "Not a mail item"
    ElseIf Err.Number = 9000 Then
        ' The active message has already been sent
        MsgBox "Please run this macro from an unsent mail item", vbOKOnly, "Not an unsent mail item"
    Else
        MsgBox "An error has occured on line " & Erl & _
                ", with a description: " & Err.Description & _
                ", and an error number " & Err.Number
    End If
        
End Sub