open outlook and run a function from Access

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
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
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.)
FordraidersAuthor 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
Gustav BrockCIOCommented:
I see no CallEmail in your code ...

/gustav
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

FordraidersAuthor Commented:
Gustav, I did it on my machine and get...method or property not found
Gustav BrockCIOCommented:
Well, that explains. Don't call a method that isn't there.

/gustav
FordraidersAuthor Commented:
Just following the suggested solution.

I have yet to find any solutions on calling a "function" From Outlook via Access 2010.
Gustav BrockCIOCommented:
Nick gave you that I believe.

/gustav
FordraidersAuthor 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
Gustav BrockCIOCommented:
OK, looks right, but I haven't tried exactly that, so let's here what Nick can add.

/gustav
FordraidersAuthor Commented:
ok Thanks;
Nick67Commented:
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.
FordraidersAuthor Commented:
ok Thanks

What is  StartApp ?

Set objOutlook = StartApp("Outlook.Application")
error on that line ?
FordraidersAuthor Commented:
I got Outlook t open ..still got same error message.
Nick67Commented:
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 ProgrammingCommented:
> "OutApp.CallEmail"

Brett, try using CALL before OutApp.CallEmail
Nick67Commented:
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 ProgrammingCommented:
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 ProgrammingCommented:
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 ?
Nick67Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
folks, thanks...both solution were helpful.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Brett ~ happy to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.