More help for Access 2003 and Outlook 2003, Please.

[mickey66]

I got the VBA script that I wrote to work completely well, with the help of Merle and George (THANK YOU!).

Now I have to try to figure out how to get rid of the extra field ("Transferred") in my database and add some error handling in the code. Because once it goes through it puts a check mark in the box under the field "Transferred" in the Assecc database and then it skips the whole contact information, so if any new information (i.e. Notes, change of phone numbers, etc...,) is put into the database then it won't get updated. Does that make sense?

Here's the code I have now:

Function TransferContacts()
'Transfer contact records from Contacts to Outlook.
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim appOutlook As New Outlook.Application
Dim ns As Outlook.Namespace
Dim fldContacts As Outlook.MAPIFolder
Dim itms As Outlook.Items
Dim objContactFolder As Object

'This is the only error handler I have in the code.
On Error GoTo ErrHandler

Set cnn = CurrentProject.Connection
Set appOutlook = CreateObject("Outlook.Application")
Set ns = appOutlook.GetNamespace("MAPI")
Set fldContacts = ns.GetDefaultFolder(olFolderContacts)
Set itms = fldContacts.Items

'Prevent duplicate contacts in Outlook.
'There's got to be an easier way to check for duplicates instead of putting the field "Transferred" in my database.
rst.Open "SELECT * FROM Lead_Generation_Contacts WHERE Transferred = 0", cnn, adOpenKeyset, adLockOptimistic

'Prevent error when recordset is empty, meaning there are no new contact records to transfer.
'And if there's not any new contacts, I need it to check for new information in the database, or updated information, etc.. New phone number, new notes to ADD ON not to OVERWRITE.
If rst.RecordCount = 0 Then
MsgBox "There are no new contact records to transfer", vbOKOnly, "Transfer stopped"
Exit Function
End If

rst.MoveFirst

'These are the only fields in the database that I need to pull out and either add to Outlook 2003 Contacts or update in Outlook 2003 Contacts
Do While Not rst.EOF
Set objContactFolder = itms.Add("IPM.Contact")
With objContactFolder
.CustomerID = Nz(rst!ContactNo)
.FullName = Trim(Nz(rst!ContactOneFirstName, "") & " " & Nz(rst!ContactOneLastName, ""))
.AssistantName = Trim(Nz(rst!ContactTwoFirstName, "") & " " & Nz(rst!ContactTwoLastname, ""))
.HomeTelephoneNumber = Nz(rst!HomePhone)
.MobileTelephoneNumber = Nz(rst!CellPhone)
.Body = Nz(rst!Notes)
.Email1Address = Nz(rst!Email)
.HomeAddress = Trim(Nz(rst!Address, "") & " " & Nz(rst!City, "") & ", " & Nz(rst!State, "") & " " & Nz(rst!Zip, ""))
.ManagerName = Nz(rst!LenderAssignedTo)
' .Created = Nz(rst!InitialDateProspected)
.Close olSave
End With

Set objContactFolder = Nothing

'This is the field in the database I need to be able to remove, but still have it figure out if there's been new information added to the database contact or changed information, the only field I don't want it to overwrite is the ( .Body = Nz(rst!Notes) ) which the field is called notes in the database.
rst.Update "transferred", -1
rst.MoveNext
Loop

Set cnn = Nothing
Set appOutlook = Nothing
Set ns = Nothing
Set fldContacts = Nothing
Set itms = Nothing
Exit Function

'Need more Error handlers then this, so nothing gets messed up in the database and it is all correct when I change information or add information to the database.
ErrHandler:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Function

Thanks,
Mickey

 

[mearle]

You are trying to synchronize contact data in an access table with contacts data in outlook(?)
This involves at least two things:
1) Adding new contacts records from Access to Outlook.
2) Copying across any changes to existing contacts from Access to Outlook.

At the moment you are only doing (1) because you know how to select these records.
You haven't done (2) because there's no way to know which records have been edited.

One way out is to select all records, rather than only new ones, and then depending on the value of Tranferred, either add the new contact, or, find the existing contact and overwrite the existing data with the access values - there is a Find method for itms to find an existing contact.

Alternatively you could use the result of the Find method (on all records) to determine whether the contact already exists in outlook, and hence whether to add or to update only - this way you can get rid of the Transferred field.

If this is too inefficient, you need to amend whatever system you have that allows contacts to be edited in Access, to record the fact that an edit has been done - so that this data can be used to select them.

Unless you know which errors you want to trap, and how to deal with them, the error handling is adequate.
Apart from that, I would amend:
Dim objContactFolder As Outlook.ContactItem

 

[tfuller]

Trying to do similar coding

I am also try to export a single contact records at a time from access to contacts; I am hoping to create an action on the input form export to Outlook contacts. I am using Access 2003 in a networked environment with users also utilizing Exchange server for email and contacts though we do keep cached copies locally utilizing Outlook 2003 client. Do you feel this is possible and how would the script you provided me edited for a single record at a time? Also, is yours setup in an environment with an Exchange server? Any suggestions would be greatly appreciated!