Dim targetDate As Date
Sub ScheduleEmail()
' Set the target date
targetDate = DateValue("2024-01-14")
' Schedule the SendCountdownEmail macro to run at 9 AM every day
Application.OnTime TimeValue("05:53:00"), "SendCountdownEmail"
End Sub
Sub SendCountdownEmail()
Dim olApp As Object
Dim olMail As Object
Dim daysLeft As Integer
' Calculate the number of days left until the target date
daysLeft = targetDate - Date
' Create a new Outlook mail item
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
With olMail
.To = "[email protected]" ' Replace with the recipient's email address
.Subject = "Countdown to Special Day"
.Body = "There are " & daysLeft & " days left until January 14, 2024."
.Send
End With
' Clean up
Set olMail = Nothing
Set olApp = Nothing
' Reschedule the SendCountdownEmail macro to run at 9 AM tomorrow
Application.OnTime TimeValue("05:53:00"), "SendCountdownEmail"
End Sub
I get a run time error 6 Overflow or Runtime error 438 - Object doesn't support this property or method
I was expecting to send out an email daily at a specific time that gave the number of days left before a certain date
