Trying to run an Outllook Sub from Access VBA

[Amiga1200]

From Access 2013, I am trying to run the following Sub in Outlook but I keep getting 'Object doesn't support this property or method'

Outlook code

ThisOutlookSession

Public Sub Fun()
MsgBox ("I am in Outlook")


End Sub



MS Access code

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Process_Error

blnNewInstance = IsOutlookOpen

MsgBox ("I should now go to outlook")

Outlook.Application.fun

MsgBox ("I should have returned from outlook")

Exit_Form_Open:
Exit Sub

Form_Open_Process_Error:
MsgBox Err.Description
Resume Exit_Form_Open

End Sub

[June7]

IsOutlookOpen is a custom function?

You probably need to declare and set an Outlook object.

[Amiga1200]

IsOutlookOpen is a custom function that checks if outlook is open

I will try declaring and advise

[Amiga1200]

I have altered it to the following and am still getting the error

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Process_Error

Dim objOutlook As Object

Set objOutlook = GetObject(, "Outlook.Application")
If objOutlook Is Nothing Then
Set objOutlook = CreateObject("Outlook.Application")
End If
objOutlook.fun

Set objOutlook = Nothing


Exit_Form_Open:
Exit Sub
Form_Open_Process_Error:
MsgBox Err.Description
Resume Exit_Form_Open
End Sub

[ItsMe]

Suggest you remove the If odjoutlook is nothing block.

Actually, you should probably start over and explain what it is you are trying to do when the form opens.

[Amiga1200]

I am updating our Access 2003 front end to 2013 and my automation does not work anymore I keep getting 'Object doesn't support this property or method'

I have several Access Functions/Subs that run Outlook Subs/Functions that I have added to Outlook.

I believe that the way I was doing this may have been incorrect but worked E.G. Outlook.Application.SaveEmail (ID) (SaveEmail is a Sub in Outlook)

I have tried researching this but have not found anything that works, I have lowered the Outlook Macro security to none.

Regards
Kevin

[June7]

Most everything I find indicates this should never have worked. See http://social.msdn.microsoft.com/For...rum=outlookdev

However, this seems to say it is possible http://www.code-tips.com/2009/03/mic...vba-class.html

Sorry, I cannot get anything to work.

[R_Badger]

You might want to add this to thisoutlooksession

Private Sub Application_Startup

End Sub

I can't remember where I read this, however this forces the outlook vba project to open and be accessible using automation any point after startup.

I know the interaction between the two sets of vba is possible because I use it at the moment.

[ItsMe]

That is interesting. I have not seen or tried that approach before. I have only used the GetNamespace and similar methods using VBA. I took a quick look at my files and could not find any examples but here is the reference to use VBA to interact with Outlook. Most everything centers or starts with the GetNamespace.

http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

[ipisors]

Outlook is very different from most Office apps' VBA, in that it is per-application, not per file ...

You may want to consider this type of alternative. Write Outlook VBA code that's automatically triggered (Rules > Run a script) when an item is received or sent.

Then, code your VBA to open outlook and send an item ...

[June7]

I have no problem with code in Access that sends an email through Outlook. I have even used code to manipulate elements of Outlook such as calendar. I cannot find any way to call an Outlook VBA procedure from Access VBA. R_Badger, if you have accomplished that, would you like to share the code or post db?

[ipisors]

I agree with June on both points; the only thing I found was an article by Ken Outlook MVP that stated it was undocumented and claimed you could use:
Call Outlook.Application.SubName()
if the sub was Public was in ThisSession class module.
When trying to mimic that inside an Access module by using Call OutlookApplicationVariable.SubName(), I did NOT find that to work in tests this morning. (To be fair, Ken emphasized its lack of documentation and stated it may not work at any time).

This is why I mention, you could write a mix of vba (some in access, some in outlook) that accomplished the same thing, if necessary, but very indirectly. Outlook vba as in vba actually run from within Outlook is just one of those.....diminished value type of subjects, because the whole deployment/distribution/shared usage side of it is pretty much not there ...

[R_Badger]

June7, Had some time to throw something a bit more concise today (Was at work and a bit tight for time.

A small note, to call the sub/function in outlook, it must exist in thisoutlooksession, I have tried calling it from another module within outlook and that did not work.

Outlook sub - purely to test

Private Sub Application_Startup
'Allows you to access the items in thisoutlloksession, it does not have to contain any commands
End Sub

Sub test()
Msgbox "This is an outlook sub called by access"
End Sub

Access

Private Sub Olinteract
dim objOl as Object 'Outlook Object
Dim NewOl as Boolean ' Flag to tell whether a new outlook instance was created

Set objOl = GetObject(, "Outlook.Application") 'Check if an existing Outlook object is open

If objOl Is Nothing Then
     'Open Outlook if it isn't Open
     Set objOl=createObject("Outlook.Application")
     NewOl=True
End If

objOl.test

If NewOl=true then objOl.Quit
set objOl=Nothing


End Sub

I can't upload a db at the moment but will try to remember when I get to a machine that will let me.