February 24, 2018

How to send Email through Outlook at Specific Tme

How to send email through Outlook at a specific Time using ontime method in VBA. Using the ontime method we have two options to perform activities based on time:
1. We can specify a time for the task to be performed using this line of code:
Application.OnTime TimeValue(“19:04:00”), “sendReminder”
The first part of the sets the time to a specific value – Application.OnTime TimeValue(“19:04:00”)
The second part of the code – “sendReminder” call the code for the task to be performed. You can not only send emails automatically at the set time but you cal also open another file or get data from an updated text file or data from a micro-controller.
2. We can specify a time for the task to be performed based on on our system’s clock. For example if the time on our ssytem clock is 4 p. m. or 16 hours, we can ask the task to be run after 15 seconds, 10 minutes or 2 hours later. We can also perform the task 1 hour, 10 minutes and 20 seconds later. Watch the video below to learn how we can perform a task at a specific time using the ontime method in Excel VBA.

You can watch this video on YouTube also.

Here’s the complete VBA or macro code:

Module1:

Sub myTimer()
Application.OnTime TimeValue(“16:16:30”), “sendReminder”
‘Application.OnTime Now + TimeValue(“00:00:00”)

End Sub

Module2:

Sub sendReminder()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim MailDest1 As String, MailDest2 As String

Set OutLookApp = CreateObject(“Outlook.application”)
Set OutLookMailItem = OutLookApp.CreateItem(olMailItem)

MailDest1 = “[email protected]
MailDest2 = “[email protected]

With OutLookMailItem
.To = MailDest1
.BCC = MailDest2
.Subject = Range(“a1”).Value
.Body = Range(“B1”).Value
.Attachments.Add “C:\Users\takyar\Pictures\bengal-tiger.jpg”
.Send
‘.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing

End Sub

Further reading:

ONTIME Method in Excel

Download File:

6 thoughts on “How to send Email through Outlook at Specific Tme

Comments are closed.