[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

ms access macro/vba to update contacts table from ms outlook

Posted on 2014-04-07
8
Medium Priority
?
1,941 Views
Last Modified: 2014-04-22
Is it possible to update an existing contacts table in ms access with a macro/vba from ms outlook public folder?
0
Comment
Question by:rjthomes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 12

Expert Comment

by:pdebaets
ID: 39984542
First, you might want to think about just linking to the Outlook Contacts table: http://www.fmsinc.com/MicrosoftAccess/Email/linked/
1
 

Author Comment

by:rjthomes
ID: 39987744
Thanks for the help but I think it will not include categories.  Right?
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39992191
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):

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

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rjthomes
ID: 39992637
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"?
0
 
LVL 12

Accepted Solution

by:
pdebaets earned 2000 total points
ID: 39992888
This sounds like a different issue than what you first posted. I don't know what categories you are referring to, so perhaps this is in another of your tables? I would suggest creating another Expert Exchange question on this one.

With regard to your original question, try doing this:
1. link the Outlook Contacts table to your front-end database file.
2. Put the code I provided above in the OnClick event procedure of a command button on a form.
3. Modify the code for your needs. You will have to change "MyCustomContactsTable" to be the name of your custom contacts table. "MyTableFirstName" should be the name of your field that holds first names. In the example above, only first and last name are updated. You want to update more fields so make your modifications accordingly.
4. Open the form and click the button to run the update.

Hope this helps.
0
 

Author Comment

by:rjthomes
ID: 39993186
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.
0
 

Author Comment

by:rjthomes
ID: 40014863
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.

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

Open in new window

Screen shot of error
0
 

Author Comment

by:rjthomes
ID: 40015696
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,
______________________________________________
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question