CODEWITHSUNDEEP
excelvba
Pom
Pom

Reputation: 43

Send email at specific time

I am trying to send email at specific date and time. I cannot set this line:

.DeferredDeliveryTime

It returns

Run-time error 440, The object does not support this method.

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

This how sheet looks like

Upvotes: 0

Views: 339

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

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...

Upvotes: 3

Related Questions