Open Calendar and go to date with VBA?

[deko]

Is it possible to pass a date to Outlook in VBA and move the Calendar to
that date on open?

I know I can open the Calendar like this:

Dim ol As Outlook.Application
Set ol = New Outlook.Application
Set olns = ol.GetNamespace("MAPI")
olns.GetDefaultFolder(olFolderCalendar).Display

But how do I move to a specified date? In pseudo code, it might look
something like this:

Dim ola As Outlook.AppointmentItem
ola.Display(#1/5/2005#)

Suggestions?

Thanks in advance.

 

[Sue Mosher [MVP-Outlook]]

There is a hidden View.GoToDate method but it's apparently broken in all but
the most recent version. The Outlook View Control also has such a method.

 

[deko]

There is a hidden View.GoToDate method but it's apparently broken in all
but

the most recent version. The Outlook View Control also has such a method.

I'm using Access 2003 - I will give it a shot and post back.

 

[deko]

There is a hidden View.GoToDate method but it's apparently broken in all
but

the most recent version. The Outlook View Control also has such a method.

I think this is close, but still missing something. Further suggestions
welcome

Option Compare Database
Option Explicit

Private Sub cmdGoToDate_Click()

Dim dtmMyDate As Date
Dim ol As Outlook.Application
Dim olns As NameSpace
Dim viws As Views
Dim viw As View

dtmMyDate = Me!txtGoToDate

Set ol = Outlook.Application
Set olns = ol.GetNamespace("MAPI")

'olns.GetDefaultFolder(olFolderCalendar).Display 'this works to open
calendar

Set viws = olns.GetDefaultFolder(olFolderCalendar).Views
Set viw = viws.Item("dateView") 'as per MSDN View object info
Debug.Print dtmMyDate 'date value seems fine
viw.goToDate (dtmMyDate) 'Intellisense seem to like this line, but...
'getting error: "Object Variable or With block variable not set"

End Sub

'I have Access 2003 with SP1 and references to "Microsoft Oulook 11.0 Object
Library" and
'"Microsoft Office Outlook View Control" (not sure if View Control reference
is necessary)

'expected pre-SP1 error is: "Run-time error: Method 'GotoDate' of object
'View' failed."
'I am not getting this error

'View object info from MSDN:
'http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaol10/ht
ml/olobjview.asp

'Help file reference from:
'http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaol10/ht
ml/olmthGoToDate.asp

 

[Sue Mosher [MVP-Outlook]]

First of all, you're not properly instantiating an Outlook.Application
object. Second, unless you actually apply the named view you invoke, I
wouldn't expect it to work. So, instead, try using the view already in use.
Something like this test code:

Sub GoToDate()
Dim objCalendar As Outlook.MAPIFolder
Dim olExp As Outlook.Explorer

dtmMyDate = Date + 30

Set OL = CreateObject("Outlook.Application")
Set olns = OL.GetNamespace("MAPI")
Set objCalendar = olns.GetDefaultFolder(olFolderCalendar)
objCalendar.Display
Set olExp = objCalendar.GetExplorer
Set viw = olExp.CurrentView
viw.GoToDate dtmMyDate

End Sub

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[deko]

First of all, you're not properly instantiating an Outlook.Application

object. Second, unless you actually apply the named view you invoke, I
wouldn't expect it to work. So, instead, try using the view already in use.
Something like this test code:

Sub GoToDate()
Dim objCalendar As Outlook.MAPIFolder
Dim olExp As Outlook.Explorer

dtmMyDate = Date + 30

Set OL = CreateObject("Outlook.Application")
Set olns = OL.GetNamespace("MAPI")
Set objCalendar = olns.GetDefaultFolder(olFolderCalendar)
objCalendar.Display
Set olExp = objCalendar.GetExplorer
Set viw = olExp.CurrentView
viw.GoToDate dtmMyDate

End Sub

Thanks!! This works great!!

I hope you don't mind a few follow-up questions:

First, I'd like to know the correct way to instantiate an
Outlook.Application.
Here's what I've been using:

If Not blnTest Then
Set ol = GetObject(, "Outlook.Application")
Else
Set ol = New Outlook.Application
End If
'the idea here is that, based on my blnTest,
'I use the current instance of Outlook, otherwise create a new instance.

'then I show the Calendar like this:
olns.GetDefaultFolder(olFolderCalendar).Display

You are using CreateObject("Outlook.Application")
are we both doing the same thing in a different way?
Are there advantages to CreateObject?

Secondly, you appear to be creating two calendar objects - is this correct?
You display one instance of the calendar object like so:

objCalendar.Display

And then I'm lost... what is going on in the next two lines?

Set olExp = objCalendar.GetExplorer
Set viw = olExp.CurrentView

Again, thanks for the help.

 

[deko]

First of all, you're not properly instantiating an Outlook.Application

object. Second, unless you actually apply the named view you invoke, I
wouldn't expect it to work. So, instead, try using the view already in

use.

Here's the complete code I'm using - it works great... my only complaint is
that a new Calendar is opened each time. If you can help me figure out how
to use a Calendar that is already open that would be great. Thanks in
advance.

Private Sub Date_DblClick(Cancel As Integer)
On Error GoTo HandleErr
Dim ol As Outlook.Application
Dim olns As NameSpace
Dim viw As View
Dim dtmDate As Date
Dim olCal As Outlook.MAPIFolder
Dim olExp As Outlook.Explorer
dtmDate = Me![ApptDate]
Set ol = GetObject(, "Outlook.Application")
Set olns = ol.GetNamespace("MAPI")
Set olCal = olns.GetDefaultFolder(olFolderCalendar)
olCal.Display
Set olExp = olCal.GetExplorer
Set viw = olExp.CurrentView
viw.GoToDate dtmDate
Exit_Here:
On Error Resume Next
Set ol = Nothing
Set olns = Nothing
Set olCal = Nothing
Set olExp = Nothing
Set viw = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case 429 'if Outlook is not already open
Set ol = CreateObject("Outlook.application")
Resume Next
Case Else
modHandler.LogErr ("frmAllApptsDatasheet"), ("Date_DblClick")
End Select
Resume Exit_Here
End Sub

 

[Sue Mosher [MVP-Outlook]]

The currently open window is Application.ActiveExplorer. You can set its
CurrentFolder property to show a particular folder.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

First of all, you're not properly instantiating an Outlook.Application
object. Second, unless you actually apply the named view you invoke, I
wouldn't expect it to work. So, instead, try using the view already in

use.

Here's the complete code I'm using - it works great... my only complaint
is
that a new Calendar is opened each time. If you can help me figure out
how
to use a Calendar that is already open that would be great. Thanks in
advance.

Private Sub Date_DblClick(Cancel As Integer)
On Error GoTo HandleErr
Dim ol As Outlook.Application
Dim olns As NameSpace
Dim viw As View
Dim dtmDate As Date
Dim olCal As Outlook.MAPIFolder
Dim olExp As Outlook.Explorer
dtmDate = Me![ApptDate]
Set ol = GetObject(, "Outlook.Application")
Set olns = ol.GetNamespace("MAPI")
Set olCal = olns.GetDefaultFolder(olFolderCalendar)
olCal.Display
Set olExp = olCal.GetExplorer
Set viw = olExp.CurrentView
viw.GoToDate dtmDate
Exit_Here:
On Error Resume Next
Set ol = Nothing
Set olns = Nothing
Set olCal = Nothing
Set olExp = Nothing
Set viw = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case 429 'if Outlook is not already open
Set ol = CreateObject("Outlook.application")
Resume Next
Case Else
modHandler.LogErr ("frmAllApptsDatasheet"), ("Date_DblClick")
End Select
Resume Exit_Here
End Sub

 

[deko]

The currently open window is Application.ActiveExplorer. You can set its

CurrentFolder property to show a particular folder.

Here's what I added:

If ol.ActiveExplorer Is Nothing Then
olns.GetDefaultFolder(olFolderCalendar).Display
Else
Set ol.ActiveExplorer.CurrentFolder =
olns.GetDefaultFolder(olFolderCalendar)
ol.ActiveExplorer.Display
End If

Seems to be working okay - comments welcome...

Thanks again for the help! The below routine has added some nice
functionality to my application.

Private Sub Date_DblClick(Cancel As Integer)
On Error GoTo HandleErr
Dim ol As Outlook.Application
Dim olns As NameSpace
Dim viw As View
Dim dtmDate As Date
Dim olCal As Outlook.MAPIFolder
Dim olExp As Outlook.Explorer
dtmDate = Me![ApptDate]
Set ol = GetObject(, "Outlook.Application")
Set olns = ol.GetNamespace("MAPI")
If ol.ActiveExplorer Is Nothing Then
olns.GetDefaultFolder(olFolderCalendar).Display
Else
Set ol.ActiveExplorer.CurrentFolder =
olns.GetDefaultFolder(olFolderCalendar)
ol.ActiveExplorer.Display
End If
Set olExp = ol.ActiveExplorer.CurrentFolder.GetExplorer
Set viw = olExp.CurrentView
viw.GoToDate dtmDate
Exit_Here:
On Error Resume Next
Set ol = Nothing
Set olns = Nothing
Set olExp = Nothing
Set viw = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case 429 'if Outlook is not already open
Set ol = CreateObject("Outlook.application")
Resume Next
Case Else
modHandler.LogErr ("frmAllApptsDatasheet"), ("Date_DblClick")
End Select
Resume Exit_Here
End Sub