Consulting

Results 1 to 5 of 5

Thread: Use calendar to call macro

  1. #1
    VBAX Mentor OTWarrior's Avatar
    Joined
    Aug 2007
    Location
    England
    Posts
    389
    Location

    Use calendar to call macro

    Not having used Outlook for macros and the like, I am still getting around how to use them, so please bare with me.

    Is it possible to set up a calendar event (a reminder, as you can do for meetings and the like) to run a macro?

    The reason why is to call a macro that checks the filesize of an Access database at the same time every morning, and if it is over the required size, to display a message. I don't have admin privileges to the system.

    Is this possible? (I have the code to check the filesize up and running, I just need to automate it for 9:30 every day)
    -Once my PC stopped working, so I kicked it......Then it started working again

  2. #2
    VBAX Tutor Mavyak's Avatar
    Joined
    Jul 2008
    Posts
    204
    Location
    Quote Originally Posted by OTWarrior
    ...so please bare with me.
    Perhaps after a port of wine with some smooth jazz.



    Can you set your code up in an autoexec macro that self-closes from a different database, then create a task in windows to open that database everyday at 9:30am?

  3. #3
    VBAX Mentor OTWarrior's Avatar
    Joined
    Aug 2007
    Location
    England
    Posts
    389
    Location
    ...and I spent a few moments thinking of the correct spelling of that word...whoops :P

    Unfortunately not, the task scheduler for windows is disabled on these computers, which is why I was thinking of using Outlook to call the macro. Shame really, as your idea sounds really good.
    -Once my PC stopped working, so I kicked it......Then it started working again

  4. #4
    VBAX Tutor Mavyak's Avatar
    Joined
    Jul 2008
    Posts
    204
    Location
    This goes in a class module named "RemindersEvents":
    [vba]Dim WithEvents m_colReminders As Outlook.Reminders

    Sub Class_Terminate()
    Call DeRefReminders
    End Sub

    Public Sub InitReminders(objApp As Outlook.Application)
    Set m_colReminders = objApp.Reminders
    End Sub

    Public Sub DeRefReminders()
    Set m_colReminders = Nothing
    End Sub

    Private Sub m_colReminders_ReminderFire(ByVal ReminderObject As Outlook.Reminder)
    Dim strMsg As String

    strMsg = "This is from a reminder event!"

    If ReminderObject.Caption = "Test Calendar Item" Then
    MsgBox strMsg
    End If
    End Sub[/vba]
    This goes in the ThisOutlookSession module:
    [vba]Dim m_remindevents As New RemindersEvents

    Private Sub Application_Startup()
    m_remindevents.InitReminders Application
    End Sub[/vba]
    The reminder caption is the subject of the calendar item you create. Replace the MsgBox line with the code that checks the size of the Access database.

    Note: This code fires when the reminder fires so set the appointment for about 10:00am and the reminder for 30 minutes prior to get the code to fire at 9:30am.

    Enjoy!

    Mav

  5. #5
    VBAX Tutor Mavyak's Avatar
    Joined
    Jul 2008
    Posts
    204
    Location
    P.S. I totally stole the code above from Microsoft:

    http://msdn.microsoft.com/en-us/library/aa155701(office.10).aspx

    Listing 11.12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •