Selected Answer
Hi Kimurios and welcome to the Forum
In the attached file, I've made the changes in bold so it uses your variables lr and xRrg better. I've corrected the code I believe caused the Automation Error:
Sub Send_Deferred_Mail_From_Excel()
Dim i As Integer, OutlookMail As Object, lr As Long
Dim xRg As Range, Cl As Range
' get last row in A
lr = Cells(Rows.Count, "A").End(xlUp).Row
' create range from row 2 to last row
Set xRg = Range("A2:A" & lr)
Set OutlookMail = CreateObject("Outlook.Application")
'For i = 2 To lr
For Each Cl In xRg
With OutlookMail.CreateItem(0)
.To = "email@gmail.com"
.CC = ""
.Subject = ""
.Body = ""
'Send email on specific day & time
'.DeferredDeliveryTime = Range("A2") + Range("A3") + Range("A4") + Range("A5") & "8:00:00 AM"
.DeferredDeliveryTime = Cl.Value & " 8:00:00 AM"
'.Send
.Display 'disable display and enable send to send automatically (and set OutlookMail to Nothing)
End With
Next Cl
'Application.DisplayAlerts = False
' Temporarily disabled for testing
'Set OutlookMail = Nothing
End Sub
Run the macro and it will produce (and only display) several emails on however many dates are in column A (make sure they're in the future and don't leave a gap!), mine are 7 consecutive dates. I disabled your line after the new comment:
' Temporarily disabled for testing
'Set OutlookMail = Nothing
so that Outlook doesn't disappear before you examine the emails which it creates. In each displayed email, look under Options/ Delay Deleivery to confirm the dates are set correctly.
The problem lay in your line:
.DeferredDeliveryTime = Range("A2") + Range("A3") + Range("A4") + Range("A5") & "8:00:00 aM"
since your trying to add 5 ranges (inside a For i= loop without using i, which doesn't work that way) whereas Outlook needs a date before the time.
I commented that line out and my replacement line (in bold above) is:
.DeferredDeliveryTime = Cl.Value & " 8:00:00 AM"
inside a For Each... loop which looks at each cell (the variable Cl) and concatenates that with your 8 AM time string.
Tip- If you want to add the code into your own file, don't make the chnages manually- use Select All in the header at the start of my code and copy the selected text etc.
Hope this works well for you (with 4 or more dates)- if so, please mark this Answer as Selected (that's how the Forum works).
