Share via

How to AutoRun a macro every day at a scheduled time

Anonymous
2019-07-03T08:00:16+00:00

I want to know how I can make my reminder macro auto-run at a specific time every day.

Code in Workbook: 

Private Sub Workbook_Open()

Application.OnTime TimeValue("10.54.00"), "Mail_reminders"

End Sub

Code in Module:

Sub Mail_reminders()

Application.OnTime TimeValue("10.54.00"), "Mail_reminders"

Dim myApp As Outlook.Application

Dim mymail As Outlook.MailItem

Dim mydate1 As Date

Dim mydate2 As Long

Dim datetoday1 As Date

Dim datetoday2 As Long

Dim x As Long

lastrow = Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row

For x = 4 To lastrow

mydate1 = Cells(x, 12).Value

mydate2 = mydate1

Cells(x, 15).Value = mydate2

datetoday1 = Date

datetoday2 = datetoday1

Cells(x, 16).Value = datetoday

If mydate2 - datetoday2 = 0 Then

Set myApp = New Outlook.Application

Set mymail = myApp.CreateItem(olMailItem)

mymail.To = Cells(x, 11).Value

With mymail

.Subject = "Reminder"

.Body = Cells(x, 20).Text

.Display

'.send

End With

Cells(x, 13) = "Reminder sent"

Cells(x, 13).Interior.ColorIndex = 46

Cells(x, 13).Font.ColorIndex = 2

Cells(x, 13).Font.Bold = True

Cells(x, 14).Value = mydate2 - datetoday2

End If

Next

Set myApp = Nothing

Set mymail = Nothing

End Sub 

Moved from: Office Excel  / Windows 10  / Office 2019

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

8 answers

Sort by: Most helpful
  1. HansV 462.6K Reputation points
    2019-07-03T11:20:46+00:00

    I created a test workbook; the code works for me.

    If you like, you can download it from https://www.dropbox.com/s/k16mzi6099gaw02/MailReminders.xlsm?dl=1

    Right-click the downloaded file and select Properties from the context menu.

    Click Unblock, then click OK.

    Make sure that you allow macros when you open the workbook.

    For testing purposes, I have set the macro to run 1 minute after opening the workbook.

    An email message  was displayed:

    And the worksheet was updated:

    See https://www.dropbox.com/s/k16mzi6099gaw02/MailReminders.xlsm?dl=1

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments
  2. Anonymous
    2019-07-03T09:51:24+00:00

    Hey,

    It didn't work. 

    By that logic, without the + 1 + it should remind me today if I change the time up to a few min from now. 

    Workbook code:

    Private Sub Workbook_Open()

    Application.OnTime Date + 1 + TimeValue("12.00.00"), "Mail_reminders"

    End Sub

    Module code:

    Sub Mail_reminders()

    Application.OnTime Date + 1 + TimeValue("12.00.00"), "Mail_reminders"

    Dim myApp As Outlook.Application

    ....

    Was this answer helpful?

    0 comments No comments
  3. HansV 462.6K Reputation points
    2019-07-03T09:41:23+00:00

    Date is the current date.

    Date + 1 is tomorrow

    Date + 1 + TimeValue("10.54.00") is tomorrow morning 10:54.

    Change the line in the standard module.

    Was this answer helpful?

    0 comments No comments
  4. Anonymous
    2019-07-03T09:00:09+00:00

    Why + 1 + ? What does it do?

    In the module or in workbook?

    Was this answer helpful?

    0 comments No comments
  5. HansV 462.6K Reputation points
    2019-07-03T08:40:58+00:00

    Change the second line

    Application.OnTime TimeValue("10.54.00"), "Mail_reminders"

    to

    Application.OnTime Date + 1 + TimeValue("10.54.00"), "Mail_reminders"

    Was this answer helpful?

    0 comments No comments