Prev: Help in a query
Next: Migrate from Access 97?
From: Lauren Quantrell on 18 Feb 2010 21:20
The following late binding code used to work with all previous
versions of MS Access prior to Access 2007 to export a contact to
Outlook. It worked exporting from Access 2003/2002/2000 to Outlook
including Outlook 2007. It worked whether or not Outlook was open.
However, now with Access 2007 it only works if Outlook is closed.

Dim objOutlook As Object
Dim objOutlookApp As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookApp = objOutlook.CreateItem(2)

With objOutlookApp

.FirstName = "John"
.LastName = "Smith"
' ...
.Save

End With

So I modified the code to determine if Outlook is running, however I
get an error message if Outlook is running:
"ActiveX component can't create object"

If fIsAppRunning("Outlook") = True Then
' (Outlook is running)
Set objOutlook = GetObject(, "Outlook.Application")
Else
' (Outlook is not running)
Set objOutlook = CreateObject("Outlook.Application")
End If

So I can't figure out why Set objOutlook = GetObject(,
"Outlook.Application") doesn't work.
Any help with this is appreciated.
lq
From: Stuart McCall on 18 Feb 2010 22:55
"Lauren Quantrell" <laurenquantrell(a)hotmail.com> wrote in message
news:c60819c1-c1fd-4c92-9734-b2f402ea8e3a(a)l19g2000yqb.googlegroups.com...
> The following late binding code used to work with all previous
> versions of MS Access prior to Access 2007 to export a contact to
> Outlook. It worked exporting from Access 2003/2002/2000 to Outlook
> including Outlook 2007. It worked whether or not Outlook was open.
> However, now with Access 2007 it only works if Outlook is closed.
>
> Dim objOutlook As Object
> Dim objOutlookApp As Object
>
> Set objOutlook = CreateObject("Outlook.Application")
> Set objOutlookApp = objOutlook.CreateItem(2)
>
> With objOutlookApp
>
> .FirstName = "John"
> .LastName = "Smith"
> ' ...
> .Save
>
> End With
>
> So I modified the code to determine if Outlook is running, however I
> get an error message if Outlook is running:
> "ActiveX component can't create object"
>
> If fIsAppRunning("Outlook") = True Then
> ' (Outlook is running)
> Set objOutlook = GetObject(, "Outlook.Application")
> Else
> ' (Outlook is not running)
> Set objOutlook = CreateObject("Outlook.Application")
> End If
>
> So I can't figure out why Set objOutlook = GetObject(,
> "Outlook.Application") doesn't work.
> Any help with this is appreciated.
> lq

Although I have no idea what fIsAppRunning contains, but it looks to me like
this is where the problem lies. Try using this function instead of all the
above:

Public Function AppObject(ClassName As String, _
Optional AppStarted As Boolean) As Object

Dim obj As Object
'
On Error Resume Next
Set obj = GetObject(, ClassName)
If Err.Number Then
Err.Clear
Set obj = CreateObject(ClassName)
AppStarted = True
End If
Set AppObject = obj
Set obj = Nothing
End Function

Call it like this:

Set objOutlook = AppObject("Outlook.Application")

If you want to know if Outlook had to be loaded, call it like this:

Dim WasLoaded As Boolean

Set objOutlook = AppObject("Outlook.Application", WasLoaded)
Debug.Print WasLoaded

That will display "True" or "False" in the debug window.


From: Lauren Quantrell on 19 Feb 2010 08:06
Stuart,
The problem has absolutely nothing to do with the subroutine I'm using
to determine if Oulook is running.
The problem occurs when Outlook is running, not when it is not, and
only when using GetObject(, "Outlook.Application").
So it fails using your code as well.
lq



On Feb 18, 10:55 pm, "Stuart McCall" <smcc...(a)myunrealbox.com> wrote:
> "Lauren Quantrell" <laurenquantr...(a)hotmail.com> wrote in message
>
> news:c60819c1-c1fd-4c92-9734-b2f402ea8e3a(a)l19g2000yqb.googlegroups.com...
>
>
>
>
>
> > The following late binding code used to work with all previous
> > versions of MS Access prior to Access 2007 to export a contact to
> > Outlook. It worked exporting from Access 2003/2002/2000 to Outlook
> > including Outlook 2007.  It worked whether or not Outlook was open.
> > However, now with Access 2007 it only works if Outlook is closed.
>
> >    Dim objOutlook As Object
> >    Dim objOutlookApp As Object
>
> >    Set objOutlook = CreateObject("Outlook.Application")
> >    Set objOutlookApp = objOutlook.CreateItem(2)
>
> >     With objOutlookApp
>
> >            .FirstName = "John"
> >            .LastName = "Smith"
> >            ' ...
> >            .Save
>
> >        End With
>
> > So I modified the code to determine if Outlook is running, however I
> > get an error message if Outlook is running:
> > "ActiveX component can't create object"
>
> >        If fIsAppRunning("Outlook") = True Then
> >             ' (Outlook is running)
> >            Set objOutlook = GetObject(, "Outlook.Application")
> >        Else
> >            ' (Outlook is not running)
> >            Set objOutlook = CreateObject("Outlook.Application")
> >        End If
>
> > So I can't figure out why  Set objOutlook = GetObject(,
> > "Outlook.Application") doesn't work.
> > Any help with this is appreciated.
> > lq
>
> Although I have no idea what fIsAppRunning contains, but it looks to me like
> this is where the problem lies. Try using this function instead of all the
> above:
>
> Public Function AppObject(ClassName As String, _
>     Optional AppStarted As Boolean) As Object
>
>     Dim obj As Object
>     '
>     On Error Resume Next
>     Set obj = GetObject(, ClassName)
>     If Err.Number Then
>         Err.Clear
>         Set obj = CreateObject(ClassName)
>         AppStarted = True
>     End If
>     Set AppObject = obj
>     Set obj = Nothing
> End Function
>
> Call it like this:
>
> Set objOutlook = AppObject("Outlook.Application")
>
> If you want to know if Outlook had to be loaded, call it like this:
>
> Dim WasLoaded As Boolean
>
> Set objOutlook = AppObject("Outlook.Application", WasLoaded)
> Debug.Print WasLoaded
>
> That will display "True" or "False" in the debug window.- Hide quoted text -
>
> - Show quoted text -

From: Stuart McCall on 19 Feb 2010 16:57
"Lauren Quantrell" <laurenquantrell(a)hotmail.com> wrote in message
news:eefd1d05-6753-457d-929c-76009ee46b1f(a)b7g2000yqd.googlegroups.com...
Stuart,
The problem has absolutely nothing to do with the subroutine I'm using
to determine if Oulook is running.
The problem occurs when Outlook is running, not when it is not, and
only when using GetObject(, "Outlook.Application").
So it fails using your code as well.
lq

Very odd. I've not seen that symptom before. Have you tried it with a
different app (eg Word) ? If that were to run ok, then the problem lies
between Access and Outlook. I don't have any other suggestions, sorry.
Perhaps someone with more experience of automating outlook will have an
idea...


From: Lauren Quantrell on 20 Feb 2010 10:08
Thanks. Same thing happens when I revert to early binding by the way.
I'll check Excel/Word automation.
 |  Next  |  Last
Pages: 1 2
Prev: Help in a query
Next: Migrate from Access 97?