This week's entry in my Lazy Programmer series is a variation of last week's macro that sends an email when Appointment reminders fire.
This VBA sample opens a web page in Internet Explorer after a reminder fires, using the URL entered into a Task's Subject field. To avoid firing with every reminder, it looks at the message class and exits if not a Task reminder. It then checks the Category field. If the Category is "Open Page", the macro opens Internet Explorer and navigates to the URL in the Subject field.
Outlook 2003 users can also use the method described in Scheduling a Recurring Message.
This code could easily be changed to run when an appointment reminder fires, such as to open an online meeting url.
Open a webpage using a reminder
Set macro security to off or to 'always ask'. You may need to restart Outlook for the lower macro security settings to kick in.
- Open the VBA editor using Alt+F11
- Expand the VBA project
- Double click on ThisOutlookSession to open it
- Paste the code into ThisOutlookSession
Return to Outlook and create your task:
- Create a new task, entering a web address in the Subject field.
- Create a category called "Open Page" and assign it to the task.
- Set the reminder for the desired date and time then save and close.
Private Sub Application_Reminder(ByVal Item As Object) Dim strURL As String Dim oApp As Object Set oApp = CreateObject("InternetExplorer.Application") If Item.MessageClass <> "IPM.Task" Then Exit Sub End If If Item.Categories <> "Open Page" Then Exit Sub End If strURL = Item.Subject oApp.navigate (strURL) oApp.Visible = True 'wait for page to load before passing the web URL Do While oApp.Busy DoEvents Loop End Sub
Open other files types
To open other file types, you need to reference the correct application and change the code that opens the file to the method used by the other application.
For example, to open an Excel workbook, you'll need to create the Excel.Application instead of InternetExplorer.Application, then open the workbook. Remove the DoEvents block, since we don't need to wait for Excel to open.
Set oApp = CreateObject("Excel.Application") oApp.Workbooks.Open FileName:=strURL oApp.Visible = True
Opening some files, such as text files in Notepad, will use a Shell command. Remove the oApp lines and use just the Shell command.
Shell ("Notepad.exe " & strURL), vbNormalFocus
Batch (*.bat) can be opened using just the file path:
Shell (strURL), vbNormalFocus
If you create a recurring task, remember to mark the task complete, instead of dismissing it.
To use this with an appointment, change If Item.MessageClass <> "IPM.Task" Then to If Item.MessageClass <> "IPM.Appointment" Then. If you want a neater entry on your calendar, put the url in the Location field and replace strURL = Item.Subject with strURL = Item.Location
If you want to use tasks to open different items, such as a web page using one task and an Excel workbook using another, you need to wrap the code in If statements:
If Item.Categories = "Open Web" Then 'do whatever End If If Item.Categories = "Open Excel" Then 'do whatever End If