Excel VBA Send Email at Specific Date & Time-CodePudding

I made a 2 cell contain Date (B2:B10) and Time (C2:C10).

I declare the cell so that it will send email based on date & time in cell but it show automation error

Sub Send_Deferred_Mail_From_Excel()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim cell As Range
    Set cell = Range("B2:C2")

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

    'Send Email Using Excel VBA Macro Code
    With OutlookMail
        .To = "[email protected]"
        .CC = "[email protected]"
        .BCC = ""
        .Subject = "Happy New Year"
        .Body = "Greeting Gael, Wish You a Very Happy New Year"
        
        'Send email on specific date & time
        .DeferredDeliveryTime = Range("B2:C2")
        
                
        .Display 'or just put .Send to directly send the mail instead of display
    End With

    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

CodePudding user response：

DeferredDeliveryTime accepts a date as a value, so you need to combine the date and time that you have into a single value.

Due to the way dates work in VBA, you can simply add them together and it should work:

.DeferredDeliveryTime = Range("B2")   Range("C2")

I would also suggest to add some checks that those ranges contain valid time and dates if you have the chance.