Access to Outlook (Access )

Topic

New Reply

I need to be able to maintain Outlook Contacts from Access. Does anyone know of any code out there that will do this?

Reply | Quote

Replies

Have you looked at
Helen Feddema site?

Reply | Quote

Yeah I did, her code49. The sample Outlook template it included could not be read by Outlook, I got “Not a valid property of this object” errors in her code when she tried to use the Outlook Object she defined, and even if it could work, it still only dumps the Access table into Outlook. There is no means to detect if a record already exists to be able to update it rather than create a duplicate.

Reply | Quote

The version of Office you are useing makes a considerable difference in whether Outlook OLE Automation works well or not. Outlook 2000 is much more friendly than 98 – but neither is a piece of cake when it comes to working with Access. The problem is that Outlook basically uses a flat file with delimiters to store all of the data about a contact. That said, the task is not insurmountable.

You have to do it all in code, and it is fairly complex. The process essentially involves creating Add, Edit and Delete processes, and the code to get and display the various contact information in forms. We are in the process of doing something similar using Exchange Server. I believe there is some information about how to tackle this in the Getz et al Developers Handbook, and there are several white papers available in the MS knowledge base. Bottom line – it isn’t trivial.

Reply | Quote

Unfortunately there is nothing in Dev. Handbook (97 version anyway). I’ve seen other code for adding a contact that worked, but I haven’t seen any code that worked (the one I saw didn’t work) for checking to see if an entry in Contact file already exists. Also, in your experience, can an Outlook Contact be updated, or is it better to delete/add?

Reply | Quote

Mark, which version of Outlook are you using? For everything prior to Outlook 2000, the Outlook scripting language was VBS, not VBA, so that might be where you’re running into problems. There were articles in Access-VB-SQL Advisor (http://www.advisor.com) and Smart Access (http://www.smartaccessnewsletter.com) on Access and Outlook in the earlier versions, so you might try looking through their indexes, although you’ll have to buy the articles unless you have the mags at home.

Reply | Quote

Just checked (I don’t routinely use Outlook), and I’m using Outlook97. In looking at Helen’s documentation, she said her template file was for Outlook98 (is there such a thing) and Outlook2000. Anyway, there are still other problems with the code.

Reply | Quote

Yes, Outlook98 was an in-between version that cured some of the Outlook97 problems, but of course had a different object library name. I would expect any code for Outlook 2000 to use VBA rather than VBS, but if she’s using the object libraries, there’s a lot you can do with Outlook without having to resort to VBS. Unfortunately, it isn’t as much as you might wish, and any given version exposes different methods and properties, so the code probably isn’t at all backward compatible with 97. She used to have 97 stuff on her site as well, so you might be able to find something more suitable if you look for it.

Reply | Quote

>>Yes, Outlook98 was an in-between version that cured some of the Outlook97 problems, but of course had a different object library name. <<

Of course! why make this simple! It looks like I'll have to follow through on my threat to load-in Office2000, now that I've gotten a friend to use RoadRunner to download all new latest SR and SP. I try to stay no more than 1 release behind.

Reply | Quote

You are correct about not being able to check to see if the person already exists, but there is a trick. To make sure you have consistency, you only allow updating from Access – i.e. no updates from Outlook. Second, you use one of the spare Outlook “fields” to store a unique identifier, and you use that as your look-up source. In other words, you use Access to check for duplicates in its tables before you ever start an OLE session with Outlook. I seem to remember that you use Office 97 primarily, and that limits what you can do from Outlook anyhow.

As a starter, you might look at ">Q168095 on the Knowledge base. There are some other white papers that go into more detail that I haven’t been able to locate, but I’ll dig later today and see if I can’t find them.

Reply | Quote

>>You are correct about not being able to check to see if the person already exists, but there is a trick<<

I'm wasn't sure about checking for duplicates in Outlook. I did have some code I got from somewhere else that purported to check for duplicates, but it is apparent that the check didn't work because I was able to consistently add duplicates from Access to Outlook. I am using Office97, but my client is using OfficeXP, if that would make a difference in what I can do. I'l check-out those MSKB references to see what I can understand. Thanks.

Reply | Quote

We haven’t tried to do any OLE Automation with Outlook 2002, so there may be some object model changes between Outlook 97/98 and it. We did find some nasty wrinkles with Word 2002 Mail Merges being automated from Access when security is active. I also found a knowledge base article that gives some example code for working with Outlook 2002 – Q291120.

There are a couple of other useful examples in Scott Barker’s Access 2000 Power Programming on pages 460-470 and in the 2000 version of the Developer’s Handbook on pages 953-961 – the latter is working with the Journal rather than contacts, but the principles are the same. Hope all this helps some – it is not a trivial task.

Reply | Quote

Thanks, I’ll dig into it some more. I’ve got to come up with way to handle this, since the client needs the information in both my system and in Outlook, and to be able to do it with minimal effort. (for example, if a new customer walks in the door, the should be able to quickly enter the customer name into Access and have it automatically added to Outlook Contact list when they create an invoice).

One more thing though. Do you know if it is possible to update the Contact information from Access? Everything I’ve seen so far deals merely with adding an entry. I suppose I could Delete/Add, but I don’t know what effect that will have on other related entries in Outlook.

Reply | Quote

Yes, you can update the Contact information using any of the appls that support OLE Automation. It’s basically the same process as adding a new item, except you have to find the record you want and then change the appropriate fields. But in your case you may just want to do a delete and an add rather than coding logic to see what info has changed. That could get pretty hairy. There is a reasonably detailed article in the Knowledge Base, Q302460, that points to an Automation Help File that has some detailed info about taking an Access Database and creating contacts from it.

I got to thinking about your problem, and I assume it is a multiple user environment, in which case you will need to be using Exchange Server in any event. Outlook PST files don’t like to share at all. Hope this helps.

Reply | Quote

Thanks, I’ll check out that MSKB article. I just loaded Office2000 Developer on my laptop, and as soon as I’m sure it doesn’t interfere with O97 I’ll do it on my desktop. The only potential problem is that there can only be 1 copy of Outlook on the computer (which can be sticky on if I need to do this interface in 2 versions of Office).

I think I downloaded what is necessary to link to Outlook Contacts through Exchange; at least when I create a new linked table, “Exchange” shows up on my file-type list. However, as I understand it, you can’t update information in a table linked this way?

Reply | Quote

I fear Office2K may have replaces your Outlook 97 – it seems to me when I tried to run a dual office machine it told me there wasn’t any choice, but that’s been 2 years ago, and at my age memory was the second thing to go – can’t remember what the first was. I believe you are correct regarding the updating of Exchange “tables” linked via ODBC.

Developing this to run on two different versions of Office in the same application could be a major league challenge. If possible, convince them to go to a single version – either 97 or XP. For one thing, XP is going to want to convert your database every time you try to run the application. And linking to a 97 backend from an XP front-end is generally trouble with a CAPITAL T. Performance suffers quite a bit, and we had corruption problems with the back-end.

Reply | Quote

>>I fear Office2K may have replaces your Outlook 97 <<

Yes it did, but it did warn me that it was going to do that (apparently Outlook is only Office component for which only 1 version is allowed). It's not really a problem, since I don't use Outlook anyway, and I've only got it loaded for this one situation I'm working on. And since client uses XP, I'll be converting some on-site anyway.

Reply | Quote

I found this today whilst looking for something on Microsoft

Reply | Quote

Thanks. I’ll check it out, but I think I may have already gotten that from someplace. The problem is that you can’t modify the data in the Outlook Contact file via the linked table using Exchange, and it also doesn’t expose all the Contact fields.

Reply | Quote

Thanks, but I’ve already gotten these. They don’t seem to work very well, unless I’ve done something wrong during installation. For one thing, it doesn’t map many of the Contact fields. It works better under A2000, but there are still some shortcomings.

Reply | Quote

This site Might be worth a look.

Reply | Quote

Thanks. I think I may have gotten the info there from another source, but I’ll look at it more closely.

Reply | Quote

BTW, Looking at your problem simply. Could you export the Outlook contacts, work on them in Access and then put them back?

Reply | Quote

>>BTW, Looking at your problem simply. Could you export the Outlook contacts, work on them in Access and then put them back?<<

The information in Access are the Customers in an Invoicing application, so the info has to reside there, otherwise I lose referential integrity. I can add a Customer to Contacts, but once it is there I either have to be able to update it or just delete it and re-add (though I don't know what consequences of this are).

Reply | Quote