How to get Outlook to auto-run Macro (which is in Outlook)?

Question

I am trying to create a Task in Outlook, using VBA, to auto-open an Excel file at certain times during the day: 9AM, 12-noon, and 3PM.  I have code inside the Excel file that will run when the file is opened.  I have Outlook VBA some code that does pretty much what I want:

Sub CreateTask()

Dim olnameSpace As Outlook.NameSpace
Dim taskFolder As Outlook.MAPIFolder
Dim xlApp As Object
Dim sourceWB As Workbook
Dim sourceSH As Worksheet
    
'Const olTaskItem = 3

Set objOutlook = CreateObject("Outlook.Application")
Set objTask = objOutlook.CreateItem(olTaskItem)

objTask.Subject = "Script Center Master Plan"
objTask.Body = "Final report for Script Center master plan."
objTask.ReminderSet = True
objTask.ReminderTime = #3/6/2012 11:59:00 PM#
'objTask.DueDate = #3/6/2012 11:59:00 PM#
'objTask.ReminderPlaySound = True
'objTask.ReminderSoundFile = "C:\Windows\Media\Ding.wav"


    Set xlApp = CreateObject("Excel.Application")
    
    With xlApp
        .Visible = True
    End With
    
    
    strFile = "C:\Users\rshuell\Desktop\historical prices.xls"
    
    Set sourceWB = Workbooks.Open(strFile, , False)
    Set sourceSH = sourceWB.Worksheets("MergeSheet")
    sourceWB.Activate
    
    
xlApp.Quit
Set sourceSH = Nothing
Set sourceWB = Nothing
Set xlApp = Nothing
    
End Sub

However, I'm not able to figure out how to get Outlook to run the (Outlook) Macro 3x per day; every week day.  I created a Task in Outlook, but it doesn't seem like the Task is really doing anything.

Please help. 

Thanks!!

Answer 1

I just tried this:

Sub RecurringAppointmentAutomation()
    Dim oAppt As AppointmentItem
    Dim oPattern As RecurrencePattern
    Set oAppt = Application.CreateItem(olAppointmentItem)
    Set oPattern = oAppt.GetRecurrencePattern
    With oPattern
        .RecurrenceType = olRecursWeekly
        .DayOfWeekMask = olTuesday
        .PatternStartDate = #3/6/2012#
        .Duration = 60
        .StartTime = #12:15:00 PM#
        .EndTime = #12:16:00 PM#
    End With
    oAppt.Subject = "My Automation"
    oAppt.Save
    oAppt.Display
   
Call OpenExcel

End Sub

That created an Appointment in Outlook, but it didn't actually run 'Call OpenExcel'.  'Call OpenExcel' is supposed to open the Excel file, similar to what I described above.  That line of code doesn't seem to get handled by the Appointment.

Answer 2

Have you tried setting a breakpoint on the "Call OpenExcel" line to ensure it gets hit?  It's possible that code in the OpenExcel procedure is throwing an error, but you didn't include that code.  You also may have error handling turned off so exceptions aren't getting thrown anywhere.

As for running macros on a schedule, one way may be to create a recurring "system" task with a reminder.  Then trap the Application.Reminder event and run your macro if this is a reminder for your system task.

---

Eric Legault
MVP (Outlook)
About me...

Answer 3

How is error handling turned off?

Here is the Appointment:

I think the problem is that there is noting in there to tell the code to run at 3:05 (which just passed, and no code fired).

Again, this is the code:

Sub RecurringAppointmentAutomation()
    Dim oAppt As AppointmentItem
    Dim oPattern As RecurrencePattern
    Set oAppt = Application.CreateItem(olAppointmentItem)
    Set oPattern = oAppt.GetRecurrencePattern
    With oPattern
        .RecurrenceType = olRecursWeekly
        .DayOfWeekMask = olTuesday
        .PatternStartDate = #3/6/2012#
        .Duration = 60
        .StartTime = #12:15:00 PM#
        .EndTime = #12:16:00 PM#
    End With
    oAppt.Subject = "My Automation"
    oAppt.Save
    oAppt.Display
   
Call OpenExcel

End Sub

Sub OpenExcel()

etc.

strFile = "C:\Users\rshuell\Desktop\historical prices.xls"   
    Set sourceWB = Workbooks.Open(strFile, , False)
    Set sourceSH = sourceWB.Worksheets("MergeSheet")
    sourceWB.Activate

etc.

End Sub

I've done this before; about 6 mos ago.  Last time I did this, it worked perfectly fine.  I was working at another firm back then.  I can't remember how I set the code up last time...

 

Answer 4

The simplest solution to determine the source of your problem is to set breakpoints, step through your code and examine variable values and code execution steps.  Another option is to add "On Error GoTo" statements throughout your code to trap unexpected errors, and/or use Debug.Print statements to output debugging data.

Also make sure your project can compile.  I'm guessing that you don't have a reference set to the Microsoft Excel Object Model Library and that's what is causing OpenExcel to fail...

---

Eric Legault
MVP (Outlook)
About me...

Answer 5

No, no, no, sorry for not being more clear here, I thought I explained.  Excel opens just fine.  I have to be in the code and hit F5 to run the code though.  I can't seem to get this setup so it will run automatically, at say 12-noon, and again at 3PM, for instance.  I want to control THIS PART of the process with Outlook.  Here is my code so far.

Sub RecurringAppointmentAutomation()
    Dim oAppt As AppointmentItem
    Dim oPattern As RecurrencePattern
    Set oAppt = Application.CreateItem(olAppointmentItem)
    Set oPattern = oAppt.GetRecurrencePattern
    With oPattern
        .RecurrenceType = olRecursWeekly
        .DayOfWeekMask = olTuesday
        .PatternStartDate = #3/6/2012#
        .Duration = 1
        .StartTime = #4:14:00 PM#
        .EndTime = #4:15:00 PM#
    End With
    oAppt.Subject = "My Automation"
    oAppt.Save
    oAppt.Display
   
Call OpenExcel

End Sub

Sub OpenExcel()

Dim xlApp As Object
Dim sourceWB As Workbook
Dim sourceSH As Worksheet
    
    Set xlApp = CreateObject("Excel.Application")
    
    With xlApp
        .Visible = True
    End With
    
    strFile = "C:\Users\rshuell\Desktop\historical prices.xls"
    
    Set sourceWB = Workbooks.Open(strFile, , False)
    Set sourceSH = sourceWB.Worksheets("MergeSheet")
    sourceWB.Activate
      
'xlApp.Quit
'Set sourceSH = Nothing
'Set sourceWB = Nothing
'Set xlApp = Nothing
    
End Sub

Again, I'm trying to create an Appointment to automatically run this code for me, each weekday at say 12-noon, and again at 3PM.  Maybe I need to set up a Task to do this.  Not exactly sure.  I'll try that now...

Answer 6

Dang!!  I guess I can't assign a time to a Task.  I guess it has to be done in an appointment.

Answer 7

I answered something similar today, and so did DamianD: just set a reminder on a recurring "system" Task and trap the Application.Reminder event; fire your code when the reminder is firing for your system task.

---

Eric Legault
MVP (Outlook)
About me...

Answer 8

I dimmed a objReminders as Outlook.Reminders

Then, Set objReminders = Application.Reminders

I reran the code for execution at 5:30 -- nothing happened.

I don't recall using a Reminder last time.  As I remember, it was quite easy, and certainly easier than it's turning out to be now.  :(

Answer 9

Wrong event. Add this to ThisOutlookSession:

Private Sub Application_Reminder(ByVal Item As Object)

End Sub

---

Eric Legault
MVP (Outlook)
About me...

Answer 10

Sorry, I don't get it.  I tried this:

Private Sub Application_Reminder(ByVal Item As Object)
    Call OpenExcel
End Sub

That is after I ran my 'Sub RecurringAppointmentAutomation()'

So, the Appointment was in Outlook, but I don't think the Reminder event fired.  Not sure why.  I need to research this a bit more.

Thanks for the help so far!!

Answer 11

Isn't the goal to fire OpenExcel before you create the recurring appointment??  You can't just fire Application_Reminder manually - it fires at the reminder time for a specific item that has a reminder.  Remember when I said create a "system" task?  Check the passed Item argument to see if the reminder is firing for your system task (e.g. if Item.Subject = "System Task"), THEN fire RecurringAppointmentAutomation.

---

Eric Legault
MVP (Outlook)
About me...

Answer 12

I'm pretty much lost now.  All I want to do is run a Macro on a certain date at a certain time, using Outlook.  I can use the Windows Task Scheduler, and do this in 5 minutes.  I just wanted to Use Outlook, both to check for emails and perform a certain task when an email comes into my Inbox.  I've done this before, about 6 months ago.  Last time it was a simple thing.  I was up and running in no time.  Now, I've spent 2 days on this, and I'm still without a solution. 

Thanks,

Ryan--

Answer 13

I've stated the answer many, many times in this thread: use the Application_Reminder event as your timer.  It is up to you to set the recurrence pattern and time for the item that fires the reminder.  The item could be a specific recurring Task (recommended) or an Appointment.  Check for that specific item by comparing the values of the Item object passed to the Application_Reminder event.  If the Item matches the Task or Appointment you created, then the reminder is firing on the timer you specified - then run your macro.  Otherwise, exit the event procedure. 

---

Eric Legault
MVP (Outlook)
About me...

Answer 14

So, I ran 'RecurringAppointmentAutomation' and set the Appointment.  After 12:21, I was expecting to get a MsgBox popping up; it never popped.

Sub RecurringAppointmentAutomation()
    Dim oAppt As AppointmentItem
    Dim objReminders As Outlook.Reminders


    Dim oPattern As RecurrencePattern
    Set oAppt = Application.CreateItem(olAppointmentItem)
    Set oPattern = oAppt.GetRecurrencePattern
    Set objReminders = Application.Reminders
    With oPattern
        .RecurrenceType = olRecursDaily
        .PatternStartDate = #3/7/2012#
        .Duration = 1
        .StartTime = #12:20:00 PM#
        .EndTime = #12:21:00 PM#
    End With
    oAppt.Subject = "My Automation"
    oAppt.Save
    oAppt.Display

'Call OpenExcel

End Sub
Private Sub Application_Reminder(ByVal Item As Object)

    'Run my procedure here
    MsgBox "I'm calling a macro!"

End Sub

I don't know how to fire the 'Application_Reminder' event.  That's where I am now.  I would surmise, if I get that working, that's it!!  IS there another setting that controls the 'Application_Reminder' event?  

If I'm doing it wrong, please give me an actual example, so I can see how to do it right!!

Thanks!!

Answer 15

First off, you don't need to create an Appointment with code (unless you have a specific reason to that I don't know about).  Just create a test Appointment called "Macro Timer", set a reminder, and wait for the reminder to fire.  When it does, the Application_Reminder event will fire automatically (you don't call this event manually).  In that event, check if Item.Subject = "Macro Timer".  If true, run your OpenExcel macro.

---

Eric Legault
MVP (Outlook)
About me...

Answer 16

Thx Eric,

You solution works a treat for an issue I was having with 'Rules and Alerts'. Much better to address using your suggested method.

cheers

Richard