Forum Discussion
Solved
Send out an email automatically at a specific time and date using VBA
I would like to have an email to be triggered automatically on every Monday at a specific timing without executing the macro manually.
How can i amend the code below?
Private Sub sendemail_EBS_outsourcefiles()
Dim outapp As Object
Dim outmail As Object
Set outapp = CreateObject("outlook.application")
Set outmail = outapp.createitem(0)
dtToday = Date
prevdate = DateAdd("n", 0, Now)
daysName = Format(Date, "dddd")
If daysName = "Monday" Then
'On Error Resume Next
With outmail
.To = "email address removed for privacy reasons"
.CC = ""
.BCC = ""
.Subject = "Test File " & Format(Date, "dd-Mmm-yy")
.Display
.HTMLBody = "Test test"
.DeferredDeliveryTime = prevdate
End With
End If
Debug.Print daysName
Set outapp = Nothing
Set outmail = Nothing
End Subappreciate the help in advance..
- You might use WIndows task scheduler for this. But you'll have to make sure the macro is called from Workbook_Open in the ThisWorkbook module and you'll have to run the task using your account credentials. Alternatively, if you're on Microsoft 365, you can use Power Automate to create a flow that does the same and runs on a schedule.
- You might use WIndows task scheduler for this. But you'll have to make sure the macro is called from Workbook_Open in the ThisWorkbook module and you'll have to run the task using your account credentials. Alternatively, if you're on Microsoft 365, you can use Power Automate to create a flow that does the same and runs on a schedule.
JKPieterse , thanks and appreciate the help!
