Get Outlook Contact Data from Outlook 1

[alexisb]

I created an Access db (2003) that contains contact info with a lot of custom logic. My client wants to be able to use Outlook to "see" these contacts and create distribution lists from the data in the Access tables and queries. I have done automation the other way; that is, having Access "see" Outlook, but I can't seem to figure out how to work it the other way. No contact data will be updated from Outlook. All updates will be done in Access. We just want to use Access as the data source. I have looked through the forums and my books and everything I found talks about how to do the links the other way. I must be missing something...Can anyone set me straight?
Thanks,
Alexis

 

[JerryKlmns]

This means that you need to take a look at a table, find some data and use it?
And what do you have till now?

 

[alexisb]

Yes, I want to look at a table/query in Access and use that data to create a distribution list in Outlook. The data will come from a query because we will be grouping the contacts based on different criteria, such as committee name, or other values. I have used Access as a data source for Word and Excel and figured I could do the same thing for Outlook but I can't find any tools in Outlook to do the link.

Each person using the database will have their own local copy of Outlook in which we will be setting up the distribution lists. That's why it would work great if I have a query from Access.

Right now there's no logic in the Access database to deal with Outlook distribution lists. I had written logic to send email directly from the database and it worked, but that doesn't resolve the problem of how the database can be used as a datasource for Outlook contacts.

I see that I mistyped the title of this thread; I meant to type "Get Outlook Contact Data from Access".

Thanks for your help.
Alexis

 

[JerryKlmns]

Alexis,

You need a connection object to open a "bridge" to your data source. You need a recordset object to retrieve fields from tables/queries. But you need first to structure your database logic.

i.e
TableA
ID
MailAddress
Name
Group_ID
etc

TableB
Group_ID
Area_ID
Description
etc

TableC
Area_ID
Sector_ID
Description
etc

TableD
Sector_ID
Description
etc

 

[alexisb]

My db structure is somewhat like what you described, as follows:

tblContacts
ContactID
FirstName
LastName
EmailName

tblContactCommittees
ContactID
CommitteeID

tblCommittees
CommitteeID
CommitteeName

I will have queries that tie the 3 tables together and then select based on CommitteeName. I would have a query for each CommitteeName and use those queries as the record sources for a distribution list for each committee name.

I use VBA all the time but I guess I can't "see" how to do the bridge so it is online real-time withtout the users having to continually export the data to Outlook. I think I could write code to do an export but I want a direct link, not an export. It's like doing a linked table from a frontend db to a backend db, but instead I want to do it between Access and Outlook.

Thanks,
Alexis

 

[JerryKlmns]

Good.

Option Explicit
Public Adm_Cnn As ADODB.Connection 'Declare the "bridge"
Public strEMailRecipients AS String

Sub OpenConnection()
    Set Adm_Cnn = New ADODB.Connection
    With Adm_Cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source") = Srv_Location & App_Database
'        .Properties("Jet OLEDB:System database") = Srv_Location & Mdw_Database
'        .Properties("User ID") = PowerUser
'        .Properties("Password") = PowerPswrd
        .Properties("Mode") = adModeShareDenyNone
        .Properties("Jet OLEDB:Engine Type") = 5
        .Properties("Locale Identifier") = 1033
'        .Properties("Persist Security Info") = False
        .Open
End Sub

Sub CloseConnection()

If Not Adm_Cnn Is Nothing Then
   If Adm_Cnn.State = adStateOpen Then Adm_Cnn.Close
   Set Adm_Cnn = Nothing 
End IF

End Sub

Sub TheRecordset()
Dim rstOloi As ADODB.Recordset  ' Select date etc
Dim strSQL As String 

strSQL = "SELECT EmailName " & _
         "FROM (tblContacts As A Inner Join tblContactCommittees As B On A.ContactID = B.ContactID) Inner Join tblCommittees As C On B.CommitteeID = C.CommitteeID"

Set rstOloi = New ADODB.Recordset
With rstOloi
    .ActiveConnection = Adm_Cnn
    .CursorLocation = adUseServer
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .Source = strSQL
    .Open
    While Not .EOF
        strEMailRecipients = strEMailRecipients & .Fields(0) & "; "
        .MoveNext
    Wend
    .Close
End With
Set rstOloi = Nothing

End Sub

Function GetRecips() As String

Call OpenConnection
Call TheRecordset
Call CloseConnection
GetRecips = strEMailRecipients 
End Function

Just to open your appetiate. Would you build more from here?

 

[alexisb]

Thanks. I don't mean to be obtuse but does this code go in Access? I pasted the code into an Access module. When I run GetRecips(), I get the error "User-Defined Type Not Defined" on "Public Adm_Cnn As ADODB.Connection 'Declare the "bridge". Do I need to use an additional library in Tools/References?

Does this bridge data between Access and Outlook. Sorry - I don't know how to view it in Outlook.

Thanks again.
Alexis

 

[JerryKlmns]

Alexis,

Add a reference to Microsoft AxtiveX Data Objects 2.x Library.

You could use this code in Access or Outlook.
Working in your objective, you 'll have to create a form in Outlook from where you want to place a combobox to select the appropiate CommitteeName, feed that to the sub Recordset (add a WHERE clause in strSQL) and get the recipients. Also you 'll have to create a new mail item in Outlook just to pass the recipients.

Function GetRecips() As String

Call OpenConnection
Call TheRecordset
Call CloseConnection(Me.cmbCommitteeName)
GetRecips = strEMailRecipients
End Function


Sub TheRecordset(strCommitteeName As String)
....
strSQL = "SELECT EmailName, CommitteeName " & _
"FROM (tblContacts As A Inner Join tblContactCommittees As B On A.ContactID = B.ContactID) Inner Join tblCommittees As C On B.CommitteeID = C.CommitteeID" & _
"WHERE CommitteeName='" & strCommitteeName & "'"
....

 

[alexisb]

Thanks for sticking with me (and have a star). I think I see where you are going with this. I will work with what you've given me today and will let you know how it worked out.

Thanks again. I really appreciate it.

Alexis