Dynamic Link to Outlook

I need a solution to link Outlook 2002 to Access 2002 on Windows 2000 system.

I can link using the wizard but the fields available are limited.
LVL 2
lake59Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PendragonZeroConnect With a Mentor Commented:
turns out reminders are kept in a seperate data structure.
because just abut anything can have a reminder (contacts, appointments)
worry not, there is a solution, we can get it but i have to look it up
for now, fields that dont show up in the dropdown, dont include in the procedure
0
 
Colonel32Commented:
What fields are you after? Are some simply missing from the table once you create the link? I did this before with no real problems :0
0
 
lake59Author Commented:
Many, but 2 that come to mind immediately are "job title" and "full name"
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
shanesuebsahakarnCommented:
Are you using Exchange?
0
 
lake59Author Commented:
no just a pst file.   Actually we have 5 pst files and share them using public outlook but this should be irrelevant (I think)
0
 
lake59Author Commented:
I have looked extensively on the internet and in EE for an answer to this.  Most offer code to solve this .  Unfortunately I don't know how to use it or where to enter it into Outlook or access.  It is hard to imagine that Microsoft or someone else doesn't provide a packaged solution to this, but i can't find one.
0
 
Colonel32Commented:
I'm still trying to figure out what happens to those fields. So you run the 'link table' wizard, but when you open the table subsequently, a number of fields are simply missing?
0
 
lake59Author Commented:
Thats correct.  Microsoft also confirms this but offers no solutions.

This describes the issue well and even offers solutions but they are over my head:  http://www.outlookcode.com/d/database.htm
0
 
PendragonZeroCommented:
I guess the fields you are missing are custom fields, judging by the article.
Are you pulling addressbook data?
Give me an example and I will try to recreate and fix?
0
 
lake59Author Commented:
Two fields that I am trying to link are "job title" and "full name".  Neither show up in the linked table.  

It is interesting to note that "Full Name" is not simply a combination of "First Name" and "Last Name".  When I make changes to the letter case in "First Name" (for instance) it is not reflected in the "Full Name" field.

The same is true for fields like "business address" which should be a combination of all the business address fields.

The outlook address book is based on my contacts folder in my default pst.  I am linking to that folder.
0
 
PendragonZeroCommented:
How's this for starters:
I wrote some Access code that uses Outlook automation.
You need to add the Outlook Library reference.

Give the function the full path to your pst file.
Then it does this:
1) Creates an Outlook session using early binding
2) Blindly, assumes your pst is not connected, so it adds it
3) Reads the contacts items found in PST (assuming blindly that there is a Contacts folder)
4) Just prints the full name and jobtitle
5) Closes the PST file

Code Start =====================
Sub OpenPSTandReadContacts(pstFilePath As String)
    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
    Dim i As Long, j As Long
   
    Set olns = ol.GetNamespace("MAPI")
    j = olns.Folders.Count + 1
    olns.AddStore pstFilePath
   
    Set cf = olns.Folders(j).Folders("Contacts")
    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)
                Debug.Print c.FullName, c.JobTitle
            End If
        Next i
        MsgBox "Finished."
    Else
        MsgBox "No contacts to export."
    End If
    olns.RemoveStore olns.Folders(j)
End Sub
Function TestOpenPSTandReadContacts()
    OpenPSTandReadContacts "C:\archive.pst" 'change this
End Function
Code End ======================

What needs to be done:
1) add a check to see if PST already open (optional)
2) modify the folder to look for (if its not Contacts)
3) add a recordset object to record the data to a table (required)
4) add more fields to record (easy)

This should be enough to get started:
I can help more if need be.
Pen
0
 
lake59Author Commented:
This is great but I don't know what to do with the code.  Can I simply paste it into a module.  I am a real neophyte when it comes to using VB code.
0
 
PendragonZeroCommented:
Oh no prob,
Just copy and paste into a module.
But you need to add a reference file.
To do this:
1) In the VB editor: go to Tools -> References...
2) Look for Microsoft Outlook 9.0 Object Library (might not be 9.0) check the box
3) Click Ok
4) with the cursor in the function press f5
5) use TestOpenPSTandReadContacts to test it; make sure you change the file name

you will probably need help reading the data to a table
so i will help with that,

Pen
0
 
lake59Author Commented:
On step 4, Pressed F5 and got "Compile Error: User-defined type not defined" and this line is highlighted "ol As New Outlook.Application"
0
 
PendragonZeroCommented:
Make sure you have the Outlook Reference file checked.
Checked references show at the top of the references prompt.
You may have to up its priority.  By highlighting the reference and clicking the up arrow.
Plus what version of Access are you running?
0
 
lake59Author Commented:
okay I didnt have the cursor in the "Function" as you said.  When i do, the compile error is gone, but I get a runtime error "the operation failed.  an object cannot be found".  The debugger points to this line: "Set cf = olns.Folders(j).Folders("Contacts")"

I changed the path to OpenPSTandReadContacts "C:\Documents and Settings\Administrator\Local Settings\Application Data\Microsoft\Outlook\outlook1.pst"
0
 
lake59Author Commented:
BTW MS Access 2002
0
 
PendragonZeroCommented:
In your outlook1.pst is there a folder called Contacts that has your contact items?
If not then replace "Contacts" in
Set cf = olns.Folders(j).Folders("Contacts")
with the name you have for the folder.

Plus remember, the code assumed outlook1.pst was not connected so close it, it is open for now; we can change code to make it work if it is open.
Pen
0
 
lake59Author Commented:
Outloook1.pst does contain "contacts" folder

I was connected, so disconnected and ran again.

The procedure triggered Outlook backup, so I assume it is "talking to"  outlook.
Unfortunately the new Runtime Error is "Automation Error.  Unspecified Error"
The debug points to: Set olns = ol.GetNamespace("MAPI")
0
 
PendragonZeroCommented:
hmmm...

try again, keep outlook open but just close outlook1.pst. (if you can)
just want to see if it works that way
0
 
PendragonZeroCommented:
actually, do you have this newer reference for Outlook
Microsoft Outlook 10.0 Object Library
link to that if you have it.

plus before running the code,
go to Debug->Compile
to see if there are any compile problems
0
 
lake59Author Commented:
can't close outlook1.pst (default pst), so I closed another called bcp.pst and changed folder to "personal" (the name of contacts on bcp.pst)

Debug->Compile is greyed out (not available)

Runtime error is now "Array index out of bounds"
Debug points to: Set cf = olns.Folders(j).Folders("Personal")

Microsoft Outlook 10.0 Object Library  is what is being used as the refrence
0
 
lake59Author Commented:
oops, I didn't actually close bcp.pst.  When I did the module ran to completion ("finished.)!
0
 
lake59Author Commented:
now, do I reconnect bcp.pst and try to link to it with the wizard?
0
 
PendragonZeroCommented:
go back to the code
if you press Ctrl+g it brings up the immediate window
you should see the info you wanted, correct?
0
 
PendragonZeroCommented:
the code i gave you was pretty generic

we can tailor it to your needs:
1) since outlook1.pst is your default, we dont need to add it (what is its folder name)
2) list all of your pst and their folder names and whether they automatically open (optional)
3) so what we can do it just check to see if the folder exists, if not add it

pen
0
 
PendragonZeroCommented:
actually the wizard cannot pull certain fields, usually custom fields (nothing u can do)
by using code, you can pull any fields you want from anywhere you want.

what i intend to do is to extend the above code to pull the data you want into a table,
all right in access (it easy to store the data)

what i need from you is to list what fields you want and what table name you want
 
0
 
lake59Author Commented:
Ctrl +g shows the data!  Way cool!
Outlook1.pst displays as outlook.pst the contacts folder is "contacts"
bcp.pst has two: "personal" and "cic"

You have two fields listed.  Can I simply add more to your list?  If so, for now add "first name" and "full name" just to see data...

Table name can be OLContacts

Now for the million dollar question:  Is this a dynamic link that will allow me to modify data from Access?

0
 
PendragonZeroCommented:
yes you can simply add to this line to see more data
               Debug.Print c.FullName, c.JobTitle
just add ,c.FirstName (it should autofill as you type, so you can see all the fields available)

for now, the data is just displayed in the debug window,
eventually you want to store it to a table

for the million dollar question, yeah you can make changes from access into outlook

now how frequently will you be making changes because if multiple people are making changes at the same time you dont want to butt heads right?

because this approach is transaction based its not real time (mock scenario)
u pull contacts from outlook into access at noon
u make changes to contacts in access this takes 30 minutes  (lets say)
then u send changes from access to outlook at 12:30

now any changes made in outlook between 12 and 12:30 will get overwritten.
but new contacts added would not get deleted

depending what you are doing we can make it more stable:
what do you envision doing with the contacts if you had them access?




0
 
lake59Author Commented:
Yes I do want to get the data into a table.
Sounds like to update Outlook, I will need to run another procedure?
Changes will be infrequent, so banging heads, I can work around.
I plan to write update querrys to change the data.
I did try to add reminder fiels but it was not listed....?
0
 
PendragonZeroCommented:
you will have two routines
one is fetch outlook data into access
one is load access data into outlook

clear the debug window by clicking in the immediate window
press ctrl+a to select all
then press delete key

now re-run the procudure
it should show you the fields

if that dont work:
which fields did you add?
0
 
lake59Author Commented:
"reminder" field doesn't work.  Its not in the pull-down list either.
runtime error: "object doesnt support this property or method"
Debug: Debug.Print c.FullName, c.JobTitle, c.Reminder
0
 
PendragonZeroCommented:
is reminder a custom field?
0
 
PendragonZeroCommented:
if it is try, user1 - user4
0
 
lake59Author Commented:
no its a field that comes from outlook used to set reminders.  there are ather fields associated with reminders too like "flag status", "followup flag" and "reminder time".
0
 
lake59Author Commented:
Pen, if the reminder data looks like a big hurdle, I can probably live without it for now.  Getting data into a table, then moving changes back to Outlook is more urgent
0
 
lake59Author Commented:
Pen,  I guess you gave up on me.  You still deserve the points though.  You taught me alot here.  I would love to complete this.   If you post your email in your profile, I will contact you.

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.