Solved

Dynamic Link to Outlook

Posted on 2004-09-16
37
348 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:lake59
  • 19
  • 15
  • 2
  • +1
37 Comments
 
LVL 4

Expert Comment

by:Colonel32
ID: 12078036
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
 
LVL 2

Author Comment

by:lake59
ID: 12078097
Many, but 2 that come to mind immediately are "job title" and "full name"
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12078106
Are you using Exchange?
0
 
LVL 2

Author Comment

by:lake59
ID: 12078124
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
 
LVL 2

Author Comment

by:lake59
ID: 12078180
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
 
LVL 4

Expert Comment

by:Colonel32
ID: 12078305
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
 
LVL 2

Author Comment

by:lake59
ID: 12078372
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
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12078766
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
 
LVL 2

Author Comment

by:lake59
ID: 12078856
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
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12079709
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
 
LVL 2

Author Comment

by:lake59
ID: 12079742
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
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12080772
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
 
LVL 2

Author Comment

by:lake59
ID: 12084056
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
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12085679
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
 
LVL 2

Author Comment

by:lake59
ID: 12085897
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
 
LVL 2

Author Comment

by:lake59
ID: 12085956
BTW MS Access 2002
0
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12086017
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
 
LVL 2

Author Comment

by:lake59
ID: 12086181
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 2

Expert Comment

by:PendragonZero
ID: 12086411
hmmm...

try again, keep outlook open but just close outlook1.pst. (if you can)
just want to see if it works that way
0
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12086477
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
 
LVL 2

Author Comment

by:lake59
ID: 12086587
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
 
LVL 2

Author Comment

by:lake59
ID: 12086645
oops, I didn't actually close bcp.pst.  When I did the module ran to completion ("finished.)!
0
 
LVL 2

Author Comment

by:lake59
ID: 12086666
now, do I reconnect bcp.pst and try to link to it with the wizard?
0
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12086671
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
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12086701
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
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12086785
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
 
LVL 2

Author Comment

by:lake59
ID: 12086975
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
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12087114
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
 
LVL 2

Author Comment

by:lake59
ID: 12087281
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
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12087399
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
 
LVL 2

Author Comment

by:lake59
ID: 12087676
"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
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12087760
is reminder a custom field?
0
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12087773
if it is try, user1 - user4
0
 
LVL 2

Author Comment

by:lake59
ID: 12087847
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
 
LVL 2

Accepted Solution

by:
PendragonZero earned 500 total points
ID: 12087955
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
 
LVL 2

Author Comment

by:lake59
ID: 12104309
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
 
LVL 2

Author Comment

by:lake59
ID: 12216873
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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now