Hello,
I am writing a macro to send an email daily at 9:30am from an excel by attaching the same excel. While i am able to get the mail when i test run the macro in VBA module window, but i am unable to get the mail daily automatically on the specified time. Below is my code.
*******ThisWorkbook Code********
Private Sub SetOnTime()
Dim Start As Date
Start = DateSerial(2021, 6, 14)
If Date <= Start + 5 Then
Call SendMail
Application.OnTime TimeValue("9:30:00"), "SendMail"
End If
End Sub
***********Module Code************
Public Const MailFunct = "SendMail"
Sub SendMail()
Dim outlookApp As Object
Dim myMail As Object
Set outlookApp = CreateObject("Outlook.Application")
Set myMail = outlookApp.CreateItem(olMailItem)
ThisWorkbook.Save
source_file = ThisWorkbook.FullName
With myMail
.To = "smadhu71@rediffmail.com"
.CC = "smadhu71@rediffmail.com"
.Subject = "Update"
.Body = " "
.Attachments.Add source_file
.Send
End With
End Sub
I am writing a macro to send an email daily at 9:30am from an excel by attaching the same excel. While i am able to get the mail when i test run the macro in VBA module window, but i am unable to get the mail daily automatically on the specified time. Below is my code.
*******ThisWorkbook Code********
Private Sub SetOnTime()
Dim Start As Date
Start = DateSerial(2021, 6, 14)
If Date <= Start + 5 Then
Call SendMail
Application.OnTime TimeValue("9:30:00"), "SendMail"
End If
End Sub
***********Module Code************
Public Const MailFunct = "SendMail"
Sub SendMail()
Dim outlookApp As Object
Dim myMail As Object
Set outlookApp = CreateObject("Outlook.Application")
Set myMail = outlookApp.CreateItem(olMailItem)
ThisWorkbook.Save
source_file = ThisWorkbook.FullName
With myMail
.To = "smadhu71@rediffmail.com"
.CC = "smadhu71@rediffmail.com"
.Subject = "Update"
.Body = " "
.Attachments.Add source_file
.Send
End With
End Sub