VBA Send email at specific time - how to fix it?-CodePudding

I am trying to set up an VBA to send email at specific date and time. But I cannot properly set this line:

.DeferredDeliveryTime

It returns Run-time error 440, The object does not support this method. Any ideas how to fix it?

Cell with date is custom formatted as DD-MMM-YYYY, and cell with hour is formatted as Time

Sub RectangleRoundedCorners4_Click()

Dim OutlookApplication As Object
Dim OutlookMail As Object
Dim ws As Worksheet
Dim Ads As String
Dim Subj As String
Dim Body As String
Dim DelDate As Date
Dim DelHour As Integer
Dim DelMin As Integer


    Set OutlookApplication = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApplication.CreateItem(0)
    Set ws = ActiveSheet
     Ads = ws.Cells(4, 2).Value
     Subj = ws.Cells(7, 2).Value
     Body = ws.Cells(4, 9).Value
     DelDate = ws.Cells(10, 6).Value
     DelHour = Hour(ws.Cells(12, 6).Value)
     DelMin = Minute(ws.Cells(12, 6).Value)
   

    With OutlookMail
        .To = Ads
        .CC = ""
        .BCC = ""
        .Subject = "REMINDER: " & Subj
        .Body = Body
        .DeferredDeliveryTime = DelDate & DelHour
    End With
   
    Set OutlookMail = Nothing
    Set OutlookApplication = Nothing
       
End Sub

CodePudding user response：

DeferredDeliveryTime needs a specific date/time. Only concatenating the two values the result will become a string.

Firstly, declare Dim DelHour As Date. It should be something like 02:00:00. Of course, it may contain minutes, seconds and they should be taken in consideration if needed (02:20:30).

Then, try .DeferredDeliveryTime = CDate(DelDate & " " & DelHour)

It may work, I think...