How do I? : Run a user defined procedure in Outlook from Access 1

[thefarg]

Hi. In a Module (or class) in outlook I have written a sub.
In Access I am instantiating an outlook object.

        Dim objOutlook As Object
        Set objOutlook = CreateObject("Outlook.Application")
        objOutlook.Module1.Test2

If the user defined sub in outlook is called Test2 ( in Module1) how do I call it?

 

[MajP]

From what I have read this is not supported. Supposedly the only way is possibly by putting the Outlook code in a public procedure in the ThisOutlookSession module in Outlook VBA. Most recommendations are to build the code in Access. This is probably due to the fact the unlike other office applications there is only one vba project for outlook.

http://support.microsoft.com/kb/229911

http://msdn.microsoft.com/en-us/library/bb206734(v=office.12).aspx

If this was another office application you would do this

http://support.microsoft.com/kb/177760

Or you can add them as a reference. Once you add it as a reference the project shows up in the vbe and you can control it as if it is part of the application. I do not have office on this computer, but I would be interested if you can add the vbaproject.otm as a reference. It seems like you should be able.

 

[thefarg]

I have this code (from Outlook 2007 Help) that does a send receive for all accounts and runs code when the SyncEnd event is triggered.

Dim WithEvents mySync As Outlook.SyncObject

Public Sub Initialize_handler()
    MsgBox "init reached"
    Set mySync = Application.Session.SyncObjects.Item(1)
    mySync.Start
End Sub
   
Private Sub mySync_SyncEnd()
    MsgBox "Synchronization is complete."
End Sub

This must be placed in a class module and Initialize_handler run.
If I am running access with an outlook object open, could I place this code in access?

 

[MajP]

Never tried it but a session is an object of an outlook application, so I would definitely think you could.

 

[thefarg]

Cant add the outlook project as a reference. I tried opening an outlook.application, but If I try running cOutlook.Initialise_handler it errors 424 and says object required.

Dim WithEvents mySync As Outlook.SyncObject
Dim objOutlook As Outlook.Application

Public Sub Initialize_handler()
    Set objOutlook = Outlook.Application
    Set mySync = objOutlook.Session.SyncObjects.item(1)
    mySync.start
End Sub
   
Private Sub mySync_SyncEnd()
    MsgBox "Synchronization is complete."
End Sub

Code is in a class module (in access) called cOutlook .
How do I do this correctly?

 

[MajP]

You declare a variable, but never instantiate anything.

Your code would be equivalent to

Dim ctrl as Access.control
Set ctrl = Access.control

or even with a custom class
Dim myClass as someClass
Set myClass = someClass

Do you see the problem

To instantiate an object you either need a constructor
set myClass = New someClass
Set olApp = New Outlook.Application

A property that returns an object
Set ctrl = forms("someform").txtbxone

or a function that creates/returns one
Set db = currentdb
Set objOutlook = CreateObject("Outlook.Application")

See this on early and late binding

http://www.dicks-clicks.com/excel/olBinding.htm

 

[thefarg]

Cannot use "new" with "withevents" variable. The code you see is taken from MS Outlook 2007 help file. It works if placed in a form module, but no luck running it in a normal class module.
So... do I need to instantiate both a outlook.application and a outlook.application.Session.SyncObjects.item ?

 

[thefarg]

Sorry, just reading my latest shot at it, I do instantiate the outlook object.

Dim WithEvents mySync As Outlook.SyncObject
Private Sub btnTest1_Click()
        Dim objOutlook As Object
        Set objOutlook = CreateObject("Outlook.Application")
        Set mySync = objOutlook.Session.SyncObjects.item(1)
        mySync.start

End Sub


Private Sub mySync_SyncEnd()
    MsgBox "Synchronization is complete."
End Sub

Should I also be instantiating the

objOutlook.Session.SyncObjects.item(1)

 

[MajP]

I am not very familiar with the outlook object model, but all examples look like this.
You need to get the MAPI namespace first.

public Sub Sync()
Dim nsp As Outlook.NameSpace
Dim sycs As Outlook.SyncObjects
Dim syc As Outlook.SyncObject
Dim i As Integer
Dim strPrompt As Integer
Set nsp = Application.GetNamespace("MAPI")
Set sycs = nsp.SyncObjects
For i = 1 To sycs.Count
Set syc = sycs.Item(i)
syc.Start
next i
End Sub

 

[thefarg]

OK. But do I only need to intantiate the outlook object and can use the child objects from that?

FYI : Rather than loop through every account, I prefer to use SyncObjects.item(1) which is All Accounts Groups anyway. Looping through the accounts means that all accounts get synced, then each account individually. These number correspond to the the account group number displayed when you click send/receive.

 

[MajP]

FYI : Rather than loop through every account, I prefer to use SyncObjects.item(1) which is All Accounts Groups anyway. Looping through the accounts means that all accounts get synced, then each account individually. These number correspond to the the account group number displayed when you click send/receive.

That was only an example to show getting the MAPI namespace, not meant to demonstrate synchobjects.

OK. But do I only need to intantiate the outlook object and can use the child objects from that?

If the objects are exposed. Then you can reference them directly.

I used the wrong terminology about instantiation. Using the New keyword or calling a function that returns a new/created object is instantiation. Setting a variable equal to a contained object is not instantiation because nothing is created. That is simply referencing an object. So if they are exposed you can reference them and in fact you probably cannot instantiate them since they exist already.

This is fine if you want with no references:
CreateObject("Outlook.Application").Session.SyncObjects.item(1).start



So, I did not know that the Session was the same as the MAPI namespace. My confusion.

Syntax
expression.Session
Returns the NameSpace object for the current session. Read-only.

expression A variable that represents a NameSpace object.

Remarks
The Session property and the GetNamespace method can be used interchangeably to obtain the NameSpace object for the current session. Both members serve the same purpose. For example, the following statements do the same function:

VBA
Set objNamespace = Application.GetNamespace("MAPI")

VBA
Set objSession = Application.Session

 

[thefarg]

Yes, the only namespace is MAPI so I prefer to use the session syntax myself (code just looks cleaner) but most examples I see use the GetNamespace syntax.
Cheers for the help, consider this one resolved

 

[MajP]

To clarify something you said

Cannot use "new" with "withevents" variable.

That is not a true statement. The problem is that little in vba can be created with the New keyword. Only thing I think you can instantiate is user defined classes. This would fail anywhere
Set mySync = New Outlook.SyncObject
regardless of withevents.

I guess most vba objects do not expose a constructor.
Almost everything in vba is created by adding some kind of create function.
createObject, createControl, createtablef, createfield, currentdb...

Or an add function of a collection that returns a new object.
set myNode = tvw.nodes.add(....)

But if you define your own class and use that in another class trapping its events, you should be able to use the new keyword.

If I have a custom class the traps form events
private withevents mForm as access.form
and then I try this
set mForm = new Access.form
it will fail because that line of code fails everywhere, because to create a new form you have to use the createform function.

But if I do this
set mForm = New Forms_FormOne
that code will work because you can instantiate a specific form's form instance using the new keyword.

 

[thefarg]

Oh. Seems I have to work VBA's shortcomings to do interesting things.
Thanks. Just a quick question.
In the original example from Outlook help, they call the initialisation routine "Initialize_handler" as if its an event.
Is it? If not, why the syntax?

 

[MajP]

You can use one or underscores in a procedure name. In keeping with vba syntax that would not be an event, because if it was they would write it as ObjectName_EventName (i.e Handler_Initialize) not EventName_HandlerName. Camelback notation is the norm, but I have seen people use underscores instead.

Public Sub SynchronizeOutlookFromAccess()
some people do
Public Sub Synchronize_Outlook_From_Access()

Oh. Seems I have to work VBA's shortcomings to do interesting things

Based on how robust you want this application and your experience with different languages, you may be spending too much time learning how to "dumb-down" to vba. You really might want to build a front end in vb.net (or other .net language). Some of the simple things will take more time but the complicated things will be much easier. Something to consider.