Excel VBA Send Email at Specific Time – Outlook Delayed Delivery

Excel VBA – Delay Delivery – Outlook Emails

By default, Outlook will send emails immediately as soon as it is connected to internet or at specific interval as per its setting. You can use Excel VBA Send Email at Specific Time or day in the future. It is also possible to do this manually using Outlook’s delayed delivery option.

If you are sending emails manually or through Excel VBA macro, it possible to set this parameter. The option is built in within Outlook for all new emails.

To stop it sending emails immediately from the outbox & send in future time or at an defined interval, you can use the option explained below.

Outlook Built-in Delayed Delivery Option

Before checking on the VBA code, lets see how to enable this option manually. This will give us a better understanding on how this option works.

Open Outlook & start with composing a new email by pressing ctrl + n from Inbox.

This will create a new email window. Enter recipient email address, subject and content. Then follow these steps to enable delayed delivery for any email.

1. Click on Option in Menu.
2. Choose ‘Delay Delivery’
3. Enter date & time for ‘Do no delivery before’.
4. Click close

Now, if you click on send, the email will be added to the outbox. It will remain there until the specific date & time limit is reached.

This is a good option for sending out planned emails for future events like New Year, Christmas, Diwali etc.,

Now, lets see how to do this using a macro code.

Excel VBA Send Email at Specific Time or Day

The below code is going to set delay delivery – “Do not deliver before” field as discussed in previous section, but using a Excel VBA macro code.

The parameter used for this option is – DeferredDeliveryTime. Lets see how to use this in the code.

Sub Send_Deferred_Mail_From_Excel()
    Dim OutlookApp As Object
    Dim OutlookMail As Object

    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)

    'Send Email Using Excel VBA Macro Code
    With OutlookMail
        .To = "Officetricks123@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "Happy New Year"
        .Body = "Wish You a Very Happy New Year"
        
        'Few Examples of Delay delivery
        
        'Example1: Send email after some minutes of delay
        '.DeferredDeliveryTime = DateAdd("n", iDelayMinutes, VBA.Now)
        
        'Example2: Send email on specific day & time
        '.DeferredDeliveryTime = "01-Jan-2050" & " 08:00:00 AM"
        
        'Example3: Send email after a week
        .DeferredDeliveryTime = Format(DateAdd("d", 7, VBA.Now), "dd-mmm-yyyy") & " 08:00:00 AM"
                
        .Display 'or just put .Send to directly send the mail instead of display
    End With

    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

The above example code illustrates sending delayed email in 3 ways.

1. Sending after few minutes of delay
2. Sending on specific day & time
3. Send after specific number of days.

With the help of few date formatting options, we can achieve & manipulate the date and time we need to send.

Why we need Delayed delivery option in Outlook Email?

Why would someone want to compose a email, but then let Excel VBA Send Email at Specific Time or day in future? Here are few examples that we came up with.

1. Someone’s birthday that is coming up in near future
2. In case you are sending bulk marketing mails & want to give time interval for each email
3. Sending your team mates a Happy New year wishes or offers in advance, but want Outlook to deliver it just before the actual date.
4. Leaving office early, but setting a delayed delivery of email to 1 or 2 hours later. This way your Manager might think that you worked late. Not ethically right, but some people do this.

In many of these cases, we thought that this option is useful, when we want to send it later, but we might forget when the moment comes. So, we can plan it in advance and send it now when you have enough time.

Additional Reading:

1. How to send bulk emails from Outlook using Excel VBA Macro?
2. Also read this article that has similar code and also explains other scenarios in details.