MS Outlook: scheduling VBA scripts?

November 10, 2009 3:45 PM

Using Outlook 2007 on windows XP, how do I run a (VBA) script either daily, or on opening the application?

In excel, I'd use something like an Auto_Open() sub, but I can't seem to find an analog for Outlook. Can I just refer to the script to a recurring appointment somehow?

posted by pompomtom to Computers & Internet (4 answers total)

Best answer: There is an Application Startup event. You could put code there to execute your script.

Example:

Private Sub Application_Startup()
	MsgBox "Welcome, " & Application.GetNamespace("MAPI").CurrentUser
	Application.ActiveExplorer.WindowState = olMaximized
End Sub

posted by SuperSquirrel at 4:54 PM on November 10, 2009

1. Open the VBA editor from Outlook (alt-F11)
2. doubleclick on "ThisOutlookSession" on the top left panel
3. on the big panel on the right, select "Application" from the left drop down and select "startup" from the right drop down.
4. put your code in Sub Application_Startup()
5. be sure to save your project

Your macro security settings have to allow running this macro on startup. In Outlook (not the VBA window) go to Tools>Macro...>Security... and choose "no security checks" if you'd like to avoid the warning that will pop up every time you start Outlook (and open yourself up to potential expoits) or choose one of the "warnings" options.
posted by i love cheese at 5:03 PM on November 10, 2009

Response by poster: Perfect, thanks.
posted by pompomtom at 5:58 PM on November 10, 2009

Use this code if you want to make it only run once per day. It stores the date of the time it was last run in the registry and then checks it whenever it is run.

Private Sub Application_Startup()
	Dim sLastRun as String
	sLastRun = Format(now, "ddddd")
	If GetSetting("MyAppName", "Settings", "LastRun", "") = sLastRun Then
		Exit Sub
	End If

	' Your code goes in here

	MsgBox "Welcome, " & Application.GetNamespace("MAPI").CurrentUser
	Application.ActiveExplorer.WindowState = olMaximized

	' Your code finishes here

	SaveSetting "MyAppName", "Settings", "LastRun", sLastRun
End Sub

I haven't properly tested it, but it should work.
posted by mr_silver at 2:39 AM on November 11, 2009