none
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!!

    Tuesday, March 06, 2012 5:05 PM

Answers

  • 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...

    Wednesday, March 07, 2012 7:08 PM

All replies

  • 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.

    Tuesday, March 06, 2012 5:18 PM
  • 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...

    Tuesday, March 06, 2012 7:10 PM
  • 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...

     

    Tuesday, March 06, 2012 8:09 PM
  • 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...

    Tuesday, March 06, 2012 8:58 PM
  • 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...

    Tuesday, March 06, 2012 9:19 PM
  • Dang!!  I guess I can't assign a time to a Task.  I guess it has to be done in an appointment.
    Tuesday, March 06, 2012 9:24 PM
  • 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...

    Tuesday, March 06, 2012 9:30 PM
  • 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.  :(

    Tuesday, March 06, 2012 10:32 PM
  • Wrong event. Add this to ThisOutlookSession:

    Private Sub Application_Reminder(ByVal Item As Object)
    
    End Sub


    Eric Legault
    MVP (Outlook)
    About me...

    Tuesday, March 06, 2012 10:40 PM
  • 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!!

    Tuesday, March 06, 2012 10:58 PM
  • 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...

    Wednesday, March 07, 2012 3:33 AM
  • 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--

    Wednesday, March 07, 2012 4:44 AM
  • 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...

    Wednesday, March 07, 2012 5:09 AM
  • 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!!


    • Edited by ryguy72 Wednesday, March 07, 2012 6:30 PM
    Wednesday, March 07, 2012 5:26 PM
  • 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...

    Wednesday, March 07, 2012 7:08 PM
  • 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

    Tuesday, August 07, 2012 11:36 PM