open outlook and run a function from Access

Fordraiders
Fordraiders used Ask the Experts™
on
Outlook 2010
Access 2010

I need to open Outlook from MsAccess. Then run a function and then close outlook  ?

Sub Run Function()
    On Error Resume Next
    Dim OutApp As Object
    Dim OutMail As Object
    Dim objNsp As Object
    Dim colSyc As Object
    Dim objSyc As Object
    Dim i As Integer

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set objNsp = OutApp.Application.GetNamespace("MAPI")  'CORRECTION to Refer to the OutLook Application correctly
    Set colSyc = objNsp.SyncObjects

    On Error Resume Next
    With OutMail
        .To = "whatever@whatever.com"
        .Subject = "Vetting Report - " & TBFileName.Text
        .Body = "For Your Information .."
        .Attachments.Add BFld1 & TBFileName.Text
        .Send ' to send in background
        ' .Display ' to open a mail window with a normal 'SEND' icon available
    End With

    For i = 1 To colSyc.Count
        Set objSyc = colSyc.Item(i)
        objSyc.Start
    Next

    On Error GoTo 0

    OutApp.Quit

    Set OutMail = Nothing
    Set objNsp = Nothing
    Set colSyc = Nothing
    Set objSyc = Nothing
    Set OutApp = Nothing

aa:
End Sub

Open in new window



The code above is just to open Outlook and send an email.
I need it to run a function in Outlook ?


Thanks
fordraiders
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2014

Commented:
IF

Your function is Public and in ThisOutlookSession, just call it or use it with syntax like
OutApp.MyFunction
and it should go.

Note the IF!

If your function is not Public or in a code module, it won't play.
You could chain-call it (ie have a Public stub in ThisOutlookSession that calls the real one.)

Author

Commented:
Nick, Sorry wont kick off.
Object does not support this property or method..
on
OutApp.CallEmail

CallEmail   is the name of the function i need to execute
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I see no CallEmail in your code ...

/gustav
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Gustav, I did it on my machine and get...method or property not found
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, that explains. Don't call a method that isn't there.

/gustav

Author

Commented:
Just following the suggested solution.

I have yet to find any solutions on calling a "function" From Outlook via Access 2010.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Nick gave you that I believe.

/gustav

Author

Commented:
Ok, let me start over.

Yes, My Function is  Public and in ThisOutlookSession,

Suggested Solution:
just call it or use it with syntax like
OutApp.MyFunction

My Function Name In Outlook is  =  "CallEmail"

So i used
OutApp.CallEmail
and get...method or property not found

fordraiders
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
OK, looks right, but I haven't tried exactly that, so let's here what Nick can add.

/gustav

Author

Commented:
ok Thanks;
Most Valuable Expert 2014

Commented:
This works in Outlook/Access 2003

The Access code in a code module for getting Outlook open properly
Public wasOpen As Boolean
Function StartApp(ByVal appName) As Object
On Error GoTo ErrorHandler
Dim oApp As Object

wasOpen = True
Set oApp = GetObject(, appName)    'Error here - Run-time error '429':
Set StartApp = oApp

Exit Function

ErrorHandler:
If Err.Number = 429 Then
    'App is not running; open app with CreateObject
    Set oApp = CreateObject(appName)
    wasOpen = False
    Resume Next
Else
    MsgBox Err.Number & " " & Err.Description
End If
End Function

Open in new window

http://www.experts-exchange.com/articles/17466/Properly-open-Outlook-as-an-Application-object-in-VBA.html

Code on a Access form for a command button to open Outlook and run the function
Private Sub Command34_Click()

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim objOutlookExplorers As Outlook.Explorers

Set objOutlook = StartApp("Outlook.Application")

Dim ns As Outlook.Namespace
Dim Folder As Outlook.MAPIFolder
Set ns = objOutlook.GetNamespace("MAPI")
Set Folder = ns.GetDefaultFolder(olFolderInbox)
Set objOutlookExplorers = objOutlook.Explorers

If wasOpen = False Then
    objOutlookExplorers.Add Folder
    Folder.Display
    'done opening
End If

'now call the function
Call objOutlook.CanICallThis
End Sub

Open in new window


The function called in ThisOutlookSession
Public Function CanICallThis()
MsgBox "Yes I can"
End Function

Open in new window


Now ***NOTE THAT THIS WAS PECULIAR***
When I typed in Call objOutlook.CanICallThis, it failed '438'
When I copy-and-pasted from the ThisOutlookSession  Call objOutlook.CanICallThis, it worked.
If I hand-edited CanICallThis in the Access code, it failed again.

Copy-and-paste the name of your Outlook function into your Access code and see if that peculiarity holds for you, too.

Author

Commented:
ok Thanks

What is  StartApp ?

Set objOutlook = StartApp("Outlook.Application")
error on that line ?

Author

Commented:
I got Outlook t open ..still got same error message.
Most Valuable Expert 2014

Commented:
If you didn't copy the first code block into your code and read the article about the 429 error then
Set objOutlook = StartApp("Outlook.Application")
isn't going to play.

It calls the first block of code posted

still got same error message.
Which?
'438' on the function call?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
> "OutApp.CallEmail"

Brett, try using CALL before OutApp.CallEmail
Most Valuable Expert 2014

Commented:
I didn't spell that out, but you asked about a function.
You either Call a function
Call OutApp.CallEmail
or you set some value/variable/property equal to the function's return value
Dim success as Boolean
success = OutApp.CallEmail


You did not ask about calling a Sub.
I didn't know if you can call a Sub

That can be one of the wrinkles.
A sub and a function that returns nothing SEEM to be identical -- but sometimes a function can be called by outside procedures where a sub cannot.

I checked.
I can.
This
Public Sub CanICallThis()
MsgBox "Yes I can"
End Sub

Open in new window

can be called by
objOutlook.CanICallThis
or
Call objOutlook.CanICallThis

This tosses a syntax error though
objOutlook.CanICallThis()

It works
screenshots
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
the rule about parentheses or not after a procedure call is whether or not a return value will be handled.  

For instance, this is ok:
MsgBox "My Message", vbYesNo, "My Title"

Open in new window


whereas this needs parentheses because a return value is being used:
if MsgBox ("My Message", vbYesNo, "My Title") = vbNo then

Open in new window

Call with a function having no parameters can have () or not since Call disposes the return value.  If there are parameters using Call, () are required.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015
Commented:
perhaps you need to tell outlook where the procedure is (could setting some of the defaults be affecting this?).  In Outlook, you can do this:
Sub callanother()
   Call ThisOutlookSession.mySub
End Sub

Sub mySub()
   MsgBox "You called me"
End Sub

Open in new window

so maybe also reference ThisOutlookSession ?
Most Valuable Expert 2014
Commented:
One other thing.
I do some extensive stuff with Outlook already.
So, my macro security settings are set to Low, I trust all installed add-ins and templates, and I Trust access to Visual Basic Project

But as you've seen from the screenshot, it definitely works.

Author

Commented:
folks, thanks...both solution were helpful.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
you're welcome, Brett ~ happy to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial