Working with Outlook Contacts from Access

[Dale Fye]

After several searches of the newsgroups, and with some cobbling together, I
now have a subroutine that will read the pertinent fields from my Outlook
contacts list, and populate a table in my database. However, I would like
to use late binding as I have users running both Office 2003 and 2007 and
want to avoid any reference problems.

I've played around with this code until I am blue in the face, but am still
unable to get it to run properly without the reference to the Outlook object
library. The line where it generally gives me an error the one that reads:
If TypeOf Contact Is Outlook.ContactItem Then

Also, if Outlook was not open when this code runs, I set set a flag (bOpen)
to False, and would like to close the instance of Outlook just before I go
into the Exit portion of the subroutine. However, the myOLApp object does
not appear to have a close or quit method. What method should I use to
close Outlook.

Dale

Public Sub OutlookContacts(Optional Reset As Boolean = False)

Dim myOlApp As Object 'Outlook.Application
Dim olns As Object
Dim objFolder As Object
Dim objAllContacts As Object
Dim Contact As Object
Dim myItem As Object 'Outlook.ContactItem

Dim bOpen As Boolean
Dim strSQL As String
Dim rs As DAO.Recordset

DoCmd.Hourglass True
Static ContactsAreLoaded As Boolean

'If the contact list has already been loaded, then skip this step
If ContactsAreLoaded And Not Reset Then GoTo ContactsExit

On Error Resume Next
Set myOlApp = GetObject(, "Outlook.Application")
If Err.Number = 0 Then
bOpen = True
Else
Debug.Print Err.Number, Err.Description
bOpen = False
Set myOlApp = CreateObject("Outlook.Application")
End If
On Error GoTo ContactsError

'Empty tbl_OutlookContacts if necessary
strSQL = "DELETE * FROM tbl_OutlookContacts"
CurrentDb.Execute strSQL, dbFailOnError
'Open the local contacts table
strSQL = "SELECT * FROM tbl_OutlookContacts"
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

Set myItem = myOlApp.CreateItem(2) 'olContactItem

' Set the Namespace object.
Set olns = myOlApp.GetNamespace("MAPI")

' Set the default Contacts folder.
Set objFolder = olns.GetDefaultFolder(10) 'olFolderContacts

' Set objAllContacts equal to the collection of all contacts.
Set objAllContacts = objFolder.Items

' Loop through each contact.
For Each Contact In objAllContacts

DoEvents
If TypeOf Contact Is Outlook.ContactItem Then
Set myItem = Contact

rs.AddNew
rs("lastname") = myItem.lastname
rs("firstname") = myItem.firstname
rs("phone_Business") = myItem.BusinessTelephoneNumber
rs("phone_Home") = myItem.HomeTelephoneNumber
rs("phone_Mobile") = myItem.MobileTelephoneNumber
rs("email_1") = myItem.email1address
rs("email_2") = myItem.Email2Address
rs("email_3") = myItem.Email3Address
rs("Company_Name") = myItem.CompanyName
rs("Department") = myItem.Department
rs("Job_Title") = myItem.JobTitle
rs.Update

End If
Next

ContactsAreLoaded = True

If bOpen = False And Not myOlApp Is Nothing Then
myOlApp.Close
End If

ContactsExit:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
DoCmd.Hourglass False
Exit Sub
ContactsError:
MsgBox Err.Number & vbCrLf & Err.Description
Debug.Print Err.Number & vbCrLf & Err.Description
Resume ContactsExit
End Sub

 

[Douglas J. Steele]

Are you sure you need to check the type of contact?

You've set objFolder to the Contacts folder, and then you're looking at all
Items in that folder. Doesn't that imply that they're all ContactItem
objects?

From what I just saw using the Object Browser, the Outlook Application
object does have a Quit method.

 

[Dale Fye]

Doug,

I am totally unfamiliar with the Outlook Object model, but when I tried:

myOlApp.Close
and
myOlApp.Quit

they both generated errors.

As I mentioned, I cludged this together from a number of posts I found in
the Outlook newsgroups. Didn't think about the fact that I might not need
the If statement at all. Will try that.

Thanks for the feedback.

Dale

 

[Douglas J. Steele]

To be honest, I don't ever send email from my Access applications. You might
want to check the Access Email FAQ that Tony Toews has at
http://www.granite.ab.ca/access/email.htm to see how to terminate your
instance of Outlook.

 

[Dale Fye]

Doug,

Tried my code without the IF statement; it bombed with an error message of
438 (Object doesn't support this property or method). My guess, although
I'm still unsure, is that the objAllContacts object in the Contacts folder
also includes distribution lists, and that is why the If statement was
required.

Found a couple of references on Tony's site, none yet that address the above
issue, but still looking. Interestingly, he doesn't "Close" or Quit the
Outlook object, he just sets it to nothing.

Thanks for the pointer. I had never really taked a close look at Tony's
site, other than the front end updater. He has a lot of good information
there.

Dale

 

[Karl Timmermans]

Some very quick cursory comments (by no means intended to be complete) just
looking at your code sample

#1 - no reason why you can't use early binding - as long as you don't use
anything specific to O'2007 which you're not from your sample - it's all
generic stuff that should work on any system with O'2000 on up from what I
can
see

#2 - would suggest setting your NameSpace immediately after creating your
Outlook object and logging on if Outlook wasn't running especially if
contact folder is Exchange based - If Outlook <wasn't> running - remember to
namespace.LogOff at end prior to closing Outlook

#3 - why do you <create> a contact item (myItem) at the start - can't see
the purpose

#4 - an alternative to (TypeOf)
if left(myitem.messageclass, 11) <> "IPM.Contact" then
goto nextitem
endif
*** skips everything not related to Contacts directly without
skipping contacts created using a custom form (if in use)

#5 - don't forget to release all your objects including NameSpace prior to
closing (to be neat and tidy) and avoid Outlook not closing properly

#6 - Closing Outlook = myOLApp.Quit

#7 - myOLApp will never be "nothing" (or your app won't work) whether or not
Outlook was running so (If not myOLApp is nothing) at end - no purpose

Finally, you're going to get a security warning since you're accessing the
contact's Email address. To that end, would suggest seriously looking at
<Redemption> -
http://www.dimastr.com/redemption

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com

 

[Dale Fye]

Karl,

Thanks for the comments.

#1. It was my understanding that I could avoid referencing Outlook
altogether by using the Object declarations, thereby avoiding version
conflicts in the References. Are you telling me that I could use the
Reference to Outlook 2003, and I would not get any conflicts if the code was
run (as is or with your recommended changes) in an O2007 environment?

#3. Because that is what the code I cobbled together had. Guess that
doesn't make much sense with the "Set myItem = Contact" inside the loop,
does it?

#4. Don't know whether you saw my last post to Doug or not. I tried the
code without the If TypeOf statement, and got an error. I assumed that was
because of distribution lists contained within the objAllContacts object.
Is that a valid assumption?

#7. You mention that myOLApp will never be "nothing". Does that include if
an error is encountered? I've had instances where when an error was
encountered, the object (say a recordset) was "dropped" and that when I
tried to close it and set it to nothing, it generated another error. So,
to alleviate that error, I started testing to see whether objects were still
instantiated (not nothing), and if so, closed them or set them to nothing.
Don't know if this makes sense, but it seems to work with recordsets and
some other object types.

Here at the house, I'm not getting any errors or security warnings when just
accessing the email address, but will test this tomorrow at the office,
where security settings are significantly greater.

Dale

 

[Karl Timmermans]

In answer to the points:

#1 - Operative element is not using Outlook properties/methods etc on a
machine where those items are not supported. In your case, everything in
your example is generic across all versions of Outlook so can't see where
any referencing issues would be encountered - early binding provides better
performance and reduces errors/problems. In this particular scenario - don't
see a reason for late-binding.

#3 - Creating the ContactItem doesn't hurt anything but it is redundant
given your loop

#4 - Assumption re: DLs contained in Items group - certainly would cause an
error if DLs exist. Every item in Outlook has a pre-defined default
MessageClass (Contacts not using a custom form = IPM.Contact , with a custom
form = IPM.Contact.SomeName , Dist List= IPM.DistList etc). If you attempt
to access a DL Item using ContactItem properties, you will get an error
that's a given.

#7 - Re: olAPP = nothing - in your specific example, the scope of your
object is specific to the <Sub> - exit the <Sub> for any reason and the
olApp no longer exists. My comments were specific to the scope of the code
you outlined. In your example - let's put it this way, if your <myOLApp>
variable suddenly became nothing after being created - there are other
issues that need to be dealt with. Objects should never disappear
<unexpectedly> - means that something needs to be fixed somewhere along the
way or some unexpected strange behaviour is going to occur.

As for security warnings - may or may not apply to you. To be honest, have
lost track of specifics of when it does or doesn't happen anymore based on
Outlook version, mode of access and a number of other conditions etc. One of
many, many benefits of using Redemption - don't really have to think about
it anymore.

BTW - your underlying premise to this routine is that all contacts are
always in the Default Contacts folder. Since you mentioned "users" - you are
positive that a user or 2 or 3 haven't created their own way of managing
users (i.e. sub-folders etc) - just something to think about.

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com

 

[Dale Fye]

Karl said: BTW - your underlying premise to this routine is that all
contacts are always in the Default Contacts folder. Since you mentioned
"users" - you are positive that a user or 2 or 3 haven't created their own
way of managing users (i.e. sub-folders etc) - just something to think
about.

Dale: Great point. Most of my users are not sophisticated enough to create
contact lists as subfolders. However, there are a couple who might. How
would you check for that? My first thougth would be to loop through all the
folders and identify those that are contact folders, storing them in an
array, then loop through the array of "contact" folders.

Dale

 

[Ken Slovak - [MVP - Outlook]]

Any contacts folder can hold distribution lists, so you have to test the
object before assigning it to a ContactItem. Usually the Class or
MessageClass property is used for that.

Outlook.Application.Quit() is definitely available for use and will
terminate an Outlook session. It should be used only in code not running
in-process with Outlook (code in another application such as Access or
standalone code, not an Outlook COM addin or Outlook VBA code).

Just setting a reference to the Outlook.Application object to Nothing will
release your local reference to the object but will not terminate the
Outlook session.

 

[Karl Timmermans]

Not knowing anything about your application in terms of purpose, importance
to your company and users etc etc - my first inclination would be to simply
establish the rule from where contacts get included and all pertinent data
should always be stored and retrieved from there (of course, make sure your
management first agrees with any operating rules or you may find yourself in
a position of being deemed to have an app that <doesn't work according to
expectations>). You simply cannot program for every possibility. Users
generally tend to be a creative bunch (either intentionally or
unintentionally) and can make your life very interesting.

#1 - users can have a "mix" of contacts in the default folder (some totally
personal which they won't want included)
#2 - your users can have contacts in sub-folders but not want them included
because those are personal etc (or vice versa)
#3 - they can create new root folders (outside of the default) for various
kinds of contacts that may be relevant to your application
......... so on and so forth

In short, establish (with your management) what rules that this app will
operate under and then ask your intended users if they see any problems with
those rules (or at least give them a chance to deal with any "personal only"
stuff in some way)- before you spend all kinds of time creating the <final>
solution.

Karl
___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com

 

[Dale Fye]

Ken,

What code would I need to add to loop through the Folders collection and
check to see whether the folder type is a contact folder. Would like to
setup an outer loop to check and make sure all of the contact folders have
been checked for contacts, rather than just check the default contact folder.

Since this part of the application will only load these contacts into a temp
table in the local (read users frontend) copy of the applcation, I am not
concerned about reading their "personal" email addresses, so I want to make
sure that I'm displaying all of their contacts (short of those in the global
address lists).
--

Dale

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Thanks, Karl

Appreciate the input.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Ken Slovak - [MVP - Outlook]]

MAPIFolder.DefaultItemType == OlItemType.olContactItem is how you'd test
each folder to see if it's a contacts folder. If the result is true it's a
contacts folder.

Since any type of folder can have a subfolder that's a contacts folder you
should use a recursive algorithm to examine each member of NameSpace.Folders
and all folder and Folders collection objects under that.