CODEWITHSUNDEEP
vbams-accessoutlook
WNG
WNG

Reputation: 11

Outlook Task Scheduler to Run Access Subs

I have Access Macros that run a series of queries, form a tables, and send those tables to multiple recipients. This works fine, but I'm being increasingly asked to supply an increasing number of reports on a set day of the week.

Rather than use my calendar to remind me to open Access and run these Macros, can I run VBA in Outlook to run an Access macro on a certain day?

Upvotes: 1

Views: 2872

Answers (1)

enderland
enderland

Reputation: 14165

Rather than use my calendar to remind me to open Access and run these Macros, can I run VBA in Outlook to run an Access macro on a certain day?

There are ways you can do this with .BAT files and windows task scheduler, but you can also do it with Outlook VBA. I have a few calendar events which have this exact purpose, to execute code and clear the reminder.

First you need to add an event handler to fire when your reminders get set.

Private WithEvents olRemind As Outlook.Reminders

You then need to initialize the WithEvents. I do this everytime a reminder happens because I sometimes break code or hard stop, which loses the event handler.

Private Sub Application_Reminder(ByVal Item As Object)
    Set olRemind = Outlook.Reminders
End Sub

Then, you want to process the event on your calendar. The way I have mine setup is I add a specific category for the event I want to fire. This helps make my calendar clearer. You can do this in a variety of ways.

I also then dismiss the reminder so it doesn't show up.

'fire off automatic macros based on recurring reminders
Private Sub olRemind_BeforeReminderShow(Cancel As Boolean)
    Dim objRem As Reminder

    For Each objRem In olRemind
        'get categories
        Dim rmdrCategories As String

        rmdrCategories = objRem.Item.categories

        'call the macro based on category
        If InStr(rmdrCategories, "whateverYouWantTheReminder") > 0 Then

            'only run if this reminder is visible
            If objRem.IsVisible Then

                'This code is specific to whatever macro you want to run
                Dim mydb As Object                
                Set mydb = GetObject("...pathToDatabase.mdb")
                mydb.Application.Run "YourMacroName"
                mydb.Application.Quit
                Set mydb = Nothing


                 objRem.Dismiss
                Cancel = True
            End If

        End If

    Next objRem

End Sub

Upvotes: 1

Related Questions