export Access to Outlook (Access 2003)

Topic

New Reply

Hello,

I work for a small company with 8 employees. We are all networked together by MS Server 2003. We have a shared Outlook calendar and a shared Outlook contacts as well. I am currently working on creating a Access Database where we can maintain our client contacts and track some marketing and production efforts as well. My goal is to be able to export current, up-to-date client contacts from Access to Outlook. Can anyone point me in the direction of a website where it gives a step by step – in dummy terms- on how to do this?

Thanks
sprater@adaptiveecosystems.com

Reply | Quote

Replies

Welcome to Woody’s Lounge!

You can link an Outlook contacts folder as a linked table in an Access database.

For an approach using VBA code, see Code Samples on Helen Feddema’s site. Sample no. 49 in the Access to Outlook section has a sample database you can download. Unfortunately the downloads don’t always work, so if you have problems, just try again the next day or so.

Reply | Quote

Thanks,

I did find that site earlier today, and was not able to download the sample. I’ll keep trying. When I sent her an email, it bounced back; email box full. In the meantime is there somewhere else I can look as well?

Thanks again,
Shirley

Reply | Quote

See post 36,274 . The database attached to that post is the Access 97 version of Helen Feddema’s demo. You can convert it to Access 2000 (or 2002/2003) format.

Reply | Quote

Hans,

Thank you for the link. I downloaded the file and converted it. When I open the sample database and click on the Select Outlook folder or Export Data to Outlook on the frmExportToOutlook form, I get an error message:

Error No: -2147024770; Description: Automation error
The specified module could not be found.

I’m thinking that I am missing some step or maybe it does not work with Access/Outlook 2003? I appologize for my ignorance, but understanding VB code is a very weak link within me.

Reply | Quote

I don’t have Office 2003. The code works OK in Office XP (2002). Have you checked that macro security (in Tools | Macro | Security) is not set to High?

Could you check the following?
– Activate the Visual Basic Editor (Alt+F11).
– Select Tools | References…
– Is there a reference starting with MISSING?

Reply | Quote

Hi Hans,

I checked both the security settings (set to low) and there is not a reference at all that starts with missing in the list.

S.

Reply | Quote

I have attached a slightly more robust version in Access 2000 format. You should be able to open it without conversion.

Note: it is wise to create a contact items folder in Outlook for testing, otherwise you;ll have to add the contacts from the Access database to your standard Contacts folder. You can do this in advance, or by clicking the New button in the Select Folder dialog.

Reply | Quote

Great Job Hans

I have been waiting for somethine like this.

Questions:

1. Is the Contact record layout format the same accross OL verisions 2000 and up?

2. Where does the Category that is entered in sample DB show up in the Contact record?

Thanks, John

Reply | Quote

Hi Hans,

Wow, this is perfect! Thank you very much, this is exactly what I was trying to achieve. I have one more question, when I tested it, it exported the test contacts like I had hoped. I exported them again, seeing if Outlook would prompt do I want to replace current record, and it didnt ask. So I end up with duplicate contacts. Is there a way around this? Thanks again Hans, your help is very much appreciated!

Reply | Quote

I’m not very familiar with Outlook VBA, so I’ll have to look into this. Hopefully someone else will reply first…

Reply | Quote

Should I repost the question in the Outlook forum?

Edit: And thank you very much Hans!

Reply | Quote

Here is a version that prompts for each individual record if there is a matching contact on the CustomerID field. If there are many matches, this quickly becomes tedious.

I’ll also post a version in my next reply that acts like importing in Outlook, i.e. it asks once beforehand what to do with matches.

Reply | Quote

Thanks for the Cake!

Reply | Quote

Here is the version that prompts only once. You can decide which version you prefer.

Reply | Quote

Thanks so much Hans!

Now to incorporate it in my current db!

Thanks again!

Reply | Quote

Thanks for the Frosting on the Cake!

Reply | Quote

Would you like a glazed cherry on the frosting?

Reply | Quote

Hans, the code is brilliant! For me, the cherry would be to know how to access the large ‘notes’ window that is available in Outlook. I have some information that I would like to capture in that field, but update in Access. There doesn’t seem to be a .Notes or .Comments option when I try and modify the code …

Many thanks,

kiwi44

Reply | Quote

The notes field is the Body property of the ContactItem object. So add the following in cmdExport_Click:

– At the end of the declarations:

Dim strNotes As String

– In the With rst … End With block:

strNotes = Nz(!Notes)

– In the With item … End With block:

.Body = strNotes

Reply | Quote

Thank you – such intuitive naming of the field … This code will save me hours of work, and avoid lots of potential errors. Thank you to all who contributed, especially Helen F.

kiwi44

Reply | Quote

I agree that “Body” is not very intuitive for a contact item, but this is a field shared by most or all Outlook items (mail items, contact items, task items, …). In a mail item, Body is the “logical” name, and it’s easiest to use the same name for other items too.

Reply | Quote

1) I guess the layout is upwards compatible. Later versions may have more fields than older versions.

2) If you double click one of the created contacts, the Category is displayed in the lower right corner.

Reply | Quote