Working with Outlook Contacts from Access

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" wrote in message
...
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

"Karl Timmermans" wrote in message
...
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 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 - 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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.



"Ken Slovak - [MVP - Outlook]" wrote:

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.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm


"Dale Fye" wrote in message
...
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

Thanks, Karl

Appreciate the input.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"Karl Timmermans" wrote:

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" wrote in message
...
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

"Karl Timmermans" wrote in message
...
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 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 - 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm


"Dale Fye" wrote in message
...
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.