SOLVED

Send out an email automatically at a specific time and date using VBA

Iron Contributor

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 Sub

appreciate the help in advance..

2 Replies
best response confirmed by hrh_dash (Iron Contributor)
Solution
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.
1 best response

Accepted Solutions
best response confirmed by hrh_dash (Iron Contributor)
Solution
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.

View solution in original post