Creating Outlook XP Contacts from Access XP (XP)

Topic

New Reply

I would like to know how to export Access record items to an Outlook contact folder in such a way that the parent-child relationship in Access between a Company record and one or more POC records can be preserved/restablished. Does anyone know how this can be done? I think it has something to do with the Links property of a contactitem, but I am unsure. TIA.

Reply | Quote

Replies

As best I can tell, setting the links property for a contact doesn’t do much of anything in Outlook. BTW, what exactly is a POC record? The basic challenge with Outlook is that it isn’t inheritly a relational database, so linking things together is a cumbersome project at best. Using Automation from Access, you should be able to set the links property, assuming it is exposed by the object model, but it’s not something I have played with.

Reply | Quote

Thanks for your response. POC stands for Point of Contact. In Access I have two tables. One for Companies and one for POCs. There are cases where someone can be the POC for more than one company. In Outlook for a Contact, I can select one or more contact names to be associated with particular contact. I want to create an Outlook Contact folder, add the company records to it, and then add the POC records. When I add the POC record, I would like to populate the ‘Contacts…’ field I see on the Outlook Contact form, with the Company the POC is associated with. I am unsure of which field in the Outlook object model I should be using and how.

Reply | Quote

OK, I think I understand what you are asking – and it’s not a trivial task, but does appear to be possible, though I’ve not done it. I presume what you want to automate from Access is the manual process of opening a company (or right-clicking on it) and selecting the Actions/Link command, selecting the Contacts folder in the Look in: TreeView, and then selecting one of the entries in the Items ListBox. That puts the Linked Contact in the Contacts box at the bottom of the standard Contact form.

This is really more of an Outlook exercise than it is an Access problem, as you need to use the Outlook object model, and it does expose the Link object, and provides methods for editing, those being Add, Item and Remove. The best place to find out about this stuff is the VBA Help for Outlook. To quickly get to that, have Outlook as the active application, and press the Alt-F11 key, which brings up the VBA editor. Then open the help files and search for “outlook links” and you will get a number of topics. If you’ve not played with Automation, you might find our brief Automation Tutorial useful, and there are a couple of Help topics that should show up in your search as described above.

Now for the harder part. Outlook uses the item name as its primary identifier, which doesn’t of course guarantee uniqueness – a considerable worry if you are working with a significant number of contacts. One thing we’ve done in the past is to store the unique ID number in one of the less frequently used fields in Outlook, and then used that to ensure that we got the correct item – we typically assign an autonumber to each person record in a database. The other issue you may bump into is that Outlook isn’t terribly efficient at working with these kind of relationships as it doesn’t have the robust indexing that you might find in databases. If you are planning to do real-time updates of Outlook as you make changes to the Access database, you may find the response time gets unacceptably slow. But it does sound like an interesting project – let us know how you fare, and post back if you have further questions.

Reply | Quote

Wendell, thanks for your tips. I have done some automation before, but find myself having to get more into it to accomplish this project. I have successfully loaded both the company and POC records from Access into a contact type folder in OUtlook. My thought was to retrieve from Outlook the unique id for each company as it is saved in Outlook, and store it in a company record field in the Access table. Then as I add POC records to the contact folder, use the unique Outlook company id to populate some Outlook POC contact field, to initiate the relationship between company and POC. I will post my question in the Outlook forum and see if anyone has some insight on the Outlook side.

Reply | Quote

Unfortunately, getting the unique Outlook ID is a significant challenge – in general it is difficult to Access with Automation, and you have all sorts of challenges if people are updating things in Outlook and you try to take that back to Access. That’s why we forced all updating to be done in Access, and didn’t allow users to update Outlook – though we were working with Exchange Server folders. If you post in the Outlook forum (and also perhaps the VBA forum), please put a link to this thread so that other loungers can see the discussion we had. (If you not done that before, see Help 19)

Reply | Quote

Wendell, thanks. I have made a post in the Outlook forum. post 373326

Reply | Quote

Wendell, thanks. I have made a post in the Outlook forum. post 373326

Reply | Quote

Unfortunately, getting the unique Outlook ID is a significant challenge – in general it is difficult to Access with Automation, and you have all sorts of challenges if people are updating things in Outlook and you try to take that back to Access. That’s why we forced all updating to be done in Access, and didn’t allow users to update Outlook – though we were working with Exchange Server folders. If you post in the Outlook forum (and also perhaps the VBA forum), please put a link to this thread so that other loungers can see the discussion we had. (If you not done that before, see Help 19)

Reply | Quote

OK, I think I understand what you are asking – and it’s not a trivial task, but does appear to be possible, though I’ve not done it. I presume what you want to automate from Access is the manual process of opening a company (or right-clicking on it) and selecting the Actions/Link command, selecting the Contacts folder in the Look in: TreeView, and then selecting one of the entries in the Items ListBox. That puts the Linked Contact in the Contacts box at the bottom of the standard Contact form.

This is really more of an Outlook exercise than it is an Access problem, as you need to use the Outlook object model, and it does expose the Link object, and provides methods for editing, those being Add, Item and Remove. The best place to find out about this stuff is the VBA Help for Outlook. To quickly get to that, have Outlook as the active application, and press the Alt-F11 key, which brings up the VBA editor. Then open the help files and search for “outlook links” and you will get a number of topics. If you’ve not played with Automation, you might find our brief Automation Tutorial useful, and there are a couple of Help topics that should show up in your search as described above.

Now for the harder part. Outlook uses the item name as its primary identifier, which doesn’t of course guarantee uniqueness – a considerable worry if you are working with a significant number of contacts. One thing we’ve done in the past is to store the unique ID number in one of the less frequently used fields in Outlook, and then used that to ensure that we got the correct item – we typically assign an autonumber to each person record in a database. The other issue you may bump into is that Outlook isn’t terribly efficient at working with these kind of relationships as it doesn’t have the robust indexing that you might find in databases. If you are planning to do real-time updates of Outlook as you make changes to the Access database, you may find the response time gets unacceptably slow. But it does sound like an interesting project – let us know how you fare, and post back if you have further questions.

Reply | Quote

Thanks for your response. POC stands for Point of Contact. In Access I have two tables. One for Companies and one for POCs. There are cases where someone can be the POC for more than one company. In Outlook for a Contact, I can select one or more contact names to be associated with particular contact. I want to create an Outlook Contact folder, add the company records to it, and then add the POC records. When I add the POC record, I would like to populate the ‘Contacts…’ field I see on the Outlook Contact form, with the Company the POC is associated with. I am unsure of which field in the Outlook object model I should be using and how.

Reply | Quote

As best I can tell, setting the links property for a contact doesn’t do much of anything in Outlook. BTW, what exactly is a POC record? The basic challenge with Outlook is that it isn’t inheritly a relational database, so linking things together is a cumbersome project at best. Using Automation from Access, you should be able to set the links property, assuming it is exposed by the object model, but it’s not something I have played with.

Reply | Quote