rjthomes
asked on
ms access macro/vba to update contacts table from ms outlook
Is it possible to update an existing contacts table in ms access with a macro/vba from ms outlook public folder?
First, you might want to think about just linking to the Outlook Contacts table: http://www.fmsinc.com/MicrosoftAccess/Email/linked/
ASKER
Thanks for the help but I think it will not include categories. Right?
You are correct, the linked Outlook Contacts table will not include any categories.
What is it you are trying to do? It might be convenient to just use the Outlook Contacts table for your application. If you need to just use the Outlook Contacts table to update your custom Access contacts table, here is some sample/template code (once you have Outlook Contacts linked):
What is it you are trying to do? It might be convenient to just use the Outlook Contacts table for your application. If you need to just use the Outlook Contacts table to update your custom Access contacts table, here is some sample/template code (once you have Outlook Contacts linked):
dim dbs as DAO.Database
dim rstI as DAO.Recordset
dim rstO as DAO.Recordset
set dbs = CurrentDB()
set rstI = dbs.openrecordset("Contacts", dbopenforwardonly)
set rstO = dbs.openrecordset("MyCustomContactsTable",dbopendynaset)
do while not rstI.EOF
rsto.findfirst "MyTableFirstName = " & chr(34) & rstI!First & chr(34) & " AND MyTableLastName = " & chr(34) & rsti!last & chr(34)
if rsto.nomatch then
rsto.addnew
else
rsto.edit
endif
rsto!MyTableFirstName = rsti!first
rsto!MyTableLastName = rsti!last
rsto.update
rstI.movenext
loop
set rstI = nothing
set rstO = nothing
set dbs = nothing
ASKER
I am simply trying to create a report in ms access that uses categories as one of it's filters. What you have offered above is probably what I need but I do not know how to implement. Can you help with that? Linking a table to outlook (as you shared in your first post) is pretty easy. Is that what you meant by"once you have outlook contacts linked"?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I might be trying to over complicate things...
I have a list on contacts (around 1000) that I have categorized in outlook. Some are sub-contractors and others are vendors for instance.
I would like to create a custom report in MS Access but I need to filter by the categories that are set up in outlook.
I know that you can sort of do this in outlook views but that is not exactly what I want. The linked table gives me everything that I need accept for what category they are.
I am sure I am not asking the right questions and probably not in the right spot... Just a bit of an amateur.
I really appreciate you patience and help.
I have a list on contacts (around 1000) that I have categorized in outlook. Some are sub-contractors and others are vendors for instance.
I would like to create a custom report in MS Access but I need to filter by the categories that are set up in outlook.
I know that you can sort of do this in outlook views but that is not exactly what I want. The linked table gives me everything that I need accept for what category they are.
I am sure I am not asking the right questions and probably not in the right spot... Just a bit of an amateur.
I really appreciate you patience and help.
ASKER
pdebaets,
I hope you haven't given up on me but I certainly understand.
I think this is closer to what I would like to do (activated by an event) but I cannot get it to work. See my screen shot below the code.
I hope you haven't given up on me but I certainly understand.
I think this is closer to what I would like to do (activated by an event) but I cannot get it to work. See my screen shot below the code.
Sub ImportContactsFromOutlook()
' This code is based in Microsoft Access.
' Set up DAO objects (uses existing "tblContacts" table)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblContacts")
' Set up Outlook objects.
Dim ol As New Outlook.Application
Dim olns As Outlook.Namespace
Dim cf As Outlook.MAPIFolder
Dim c As Outlook.ContactItem
Dim objItems As Outlook.Items
Dim Prop As Outlook.UserProperty
Set olns = ol.GetNamespace("MAPI")
Set cf = olns.GetDefaultFolder(olFolderContacts)
Set objItems = cf.Items
iNumContacts = objItems.Count
If iNumContacts <> 0 Then
For i = 1 To iNumContacts
If TypeName(objItems(i)) = "ContactItem" Then
Set c = objItems(i)
rst.AddNew
rst!FirstName = c.FirstName
rst!LastName = c.LastName
rst!Address = c.BusinessAddressStreet
rst!City = c.BusinessAddressCity
rst!State = c.BusinessAddressState
rst!Zip_Code = c.BusinessAddressPostalCode
' Custom Outlook properties would look like this:
' rst!AccessFieldName = c.UserProperties("OutlookPropertyName")
rst.Update
End If
Next i
rst.Close
MsgBox "Finished."
Else
MsgBox "No contacts to export."
End If
End Sub
ASKER
pdebaets,
Your response below was exactly what I needed...
__________________________ __________ __________ ______
In your code, you need to add a reference to Outlook. Click Tools > References then put a check mark next to Microsoft Outlook x.0 Object Library. The version number may be different on your computer. I have version 14.0 (2010).
The code you have will update an Access table with contact information from Outlook. You can include a Categories field in your Access table and include this line of code in the update to have it filled with Categories information from Outlook:
rst!Categories = c.Categories
Let me know if this helps,
__________________________ __________ __________
Your response below was exactly what I needed...
__________________________
In your code, you need to add a reference to Outlook. Click Tools > References then put a check mark next to Microsoft Outlook x.0 Object Library. The version number may be different on your computer. I have version 14.0 (2010).
The code you have will update an Access table with contact information from Outlook. You can include a Categories field in your Access table and include this line of code in the update to have it filled with Categories information from Outlook:
rst!Categories = c.Categories
Let me know if this helps,
__________________________