Get Data in Outlook using Access 1

[cthaxter]

I'm trying to write code in an Access database to get data from my Outlook contacts folder and populate certain tables in Access a certain way, using the data from Outlook as well as other data taken from a form filled out by the user.

I need help with the first part: referencing the Outlook data. I've looked into it a bit and it seems rather complicated. Just linking tables doesn't work, because that brings in only certain fields, not all contact fields. I also want to access user-defined fields that are in Outlook.

Any help? All I need to know to get started is how to reference the FullName field. If you're in Access, and you go to File/Get External Data/Link Tables and change the file type to Exchange or Outlook, and choose the Contacts folder that you want, you aren't given a choice as to which fields you want to link to. It just links a certain predetermined set of fields. And FullName is not included in those fields. So, I presume you I'll have to write code to do it.

Further Keywords: Connections Collection, Workspace Object, Connect Property, MAPI folder, ODBC, Microsoft Jet Engine, OpenDatabase Method

Thanks!

Christopher

 

[JoaoTL]

Getting a Contact Item by a Full Name.

Function GetContactItem(strFullName As String) As Outlook.ContactItem
' This procedure returns an Outlook ContactItem with a FullName
' property that matches the full name passed in the strFullName
' argument to the procedure.
Dim fldContacts As Outlook.MAPIFolder
Dim objItem As Object
Dim strCriteria As String

' Use the InitializeOutlook procedure to initialize global
' Application and NameSpace object variables, if necessary.
If golApp Is Nothing Then
If InitializeOutlook = False Then
MsgBox "Unable to initialize Outlook Application " _
& "or NameSpace object variables!"
Exit Function
End If
End If

Set fldContacts = gnspNameSpace.GetDefaultFolder(olFolderContacts)
For Each objItem In fldContacts.Items
If UCase$(objItem.FullName) = UCase$(strFullName) Then
Set GetContactItem = objItem
Exit Function
End If
Next objItem

Set GetContactItem = Nothing

End Function Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access

 

[cthaxter]

Thanks for responding, but the code doesn't work. Remember, I'm in Access, just trying to access data from Outlook, so keywords such as ContactItem and olFolderContacts aren't recognized.

Also, if I want to use Outlook.ContactItem or Outlook.MAPIFolder as data types, I would have to set them with a Type statement. I'm not entirely familiar with that process.

Sure, if I use those keywords and data types within Outlook, they are recognized. But I'm not in Outlook--I'm in Access, trying to access data that's in Outlook.

Sorry for the confusion... or should those keywords be available to me if I initialize outlook within the code somehow? I don't know how to do that. In any case, the code didn't work. In further examples, please declare all variables as if there is an Option Explicit statement, to avoid confusion.

Thank you for the initial suggestion, and I would greatly appreciate any further help.

Christopher

 

[JoaoTL]

Hi, there is no confusion, this work under Access, at least work for me, so you have to chek your references, select:

Microsoft Outlook 9.0 Object Library

If you have Access 2000 Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access

 

[cthaxter]

Thanks, I didn't know about that. I went to Tools/References and solved the problem.

However, the InitializeOutlook portion is giving me errors. The code stops at the golApp line and tells me an object is needed. If I dim golApp as Object, the line executes, but then I get the Msgbox just before the Exit Function.

Where did golApp and InitializeOutlook come from, anyway? Where is InitializeOutlook defined or set? I'm a bit confused about this. In what case is InitializeOutlook true? If I comment out this section, i.e., from the Set golApp... to the second End If, then I get an error saying an object is needed at the Set fldContacts line. If I then precede that line by:

Set gnspNameSpace = Outlook.GetNamespace("MAPI&quot

the function runs but returns Nothing.

Could you give me an example of code that will call this function properly? Maybe that's part of my problem. Here's what I have:

Sub DealContactRetrieval()
Dim objContactItem As Outlook.ContactItem

Set objContactItem = GetContactItem("Mary Baker&quot
MsgBox objContactItem.FullNameAndCompany

End Sub

The function otherwise currently returns Nothing.

I would appreciate any further assistance. Thanks again.

-Christopher

 

[JoaoTL]

Sorry about that. You will need to initialize Outlook with this function and to initialize global Application and NameSpace object variables, if necessary. I dont have a example to post were i am now and i did this a long time ago. Try to make this work, soon i will post a example, but until there work on this.

Function InitializeOutlook() As Boolean
' This function is used to initialize the global Application and
' NameSpace variables.

On Error GoTo Init_Err

Set golApp = New Outlook.Application ' Application object.
Set gnspNameSpace = golApp.GetNamespace("MAPI&quot ' Namespace object.

InitializeOutlook = True
Beep

Init_End:
Exit Function
Init_Err:
InitializeOutlook = False
MsgBox ""
Resume Init_End
End Function
Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access

 

[cthaxter]

Thank you! That worked, but I had to declare at least module-level variables for golApp and gnspNameSpace. I went ahead and declared them as Public. Oh, and I'm using a public contacts folder called "Blue Book." Here's the complete code, for posterity's sake. Thank you very much for your help! Obrigado!

Option Explicit
Public gnspNameSpace As Outlook.NameSpace
Public golApp As Outlook.Application

Sub DealContactRetrieval()
Dim objContactItem As Outlook.ContactItem

Set objContactItem = GetContactItem("Mary Baker&quot
If Not objContactItem Is Nothing Then
MsgBox objContactItem.FullNameAndCompany
Else
MsgBox "No item was returned."
End If

End Sub

Function GetContactItem(strFullName As String) As Outlook.ContactItem
' This procedure returns an Outlook ContactItem with a FullName
' property that matches the full name passed in the strFullName
' argument to the procedure.
Dim fldContacts As Outlook.MAPIFolder
Dim objItem As Object
Dim strCriteria As String

' Use the InitializeOutlook procedure to initialize global
' Application and NameSpace object variables, if necessary.
If golApp Is Nothing Then
If InitializeOutlook = False Then
MsgBox "Unable to initialize Outlook Application " _
& "or NameSpace object variables!"
Exit Function
End If
End If

Set fldContacts = gnspNameSpace.Folders("Public Folders&quot.Folders _
("All Public Folders&quot.Folders("Blue Book&quot

For Each objItem In fldContacts.Items
If objItem.MessageClass = "IPM.Contact.ITU" Then
If UCase$(objItem.FullName) = UCase$(strFullName) Then
Set GetContactItem = objItem
Exit Function
End If
End If
Next objItem

Set GetContactItem = Nothing

End Function
Function InitializeOutlook() As Boolean
' This function is used to initialize the global Application and
' NameSpace variables.
'Dim gnspNameSpace As Object

On Error GoTo Init_Err

Set golApp = New Outlook.Application ' Application object.
Set gnspNameSpace = Outlook.GetNamespace("MAPI&quot ' Namespace object.

InitializeOutlook = True
Beep

Init_End:
Exit Function
Init_Err:
InitializeOutlook = False
MsgBox "An error was encountered in the process of initializing Outlook."
Resume Init_End
End Function

 

[DCCoolBreeze]

An interesting thing to remember about Microsoft is that everything is a object and the object is located in a .DLL so as long as you have access to the objects within the .DLL you should be able to execute any of the methods in the .DLL. Hence, MS ACCESS can execute word objects, excel objects, powerpoint objects, outlook objects, project objects, winfax objects,... as long as you have permission too...

good luck!