Working with Outlook Contacts

Outlook offers a convenient and attractive interface for working with contacts (though, as noted earlier in this chapter, it does not support linking companies to contacts or other one-to-many links). Most people keep Outlook open at all times, whereas they may only open an Access database as needed. Because of this, if you have a single table of Access contacts, you may wish to export the data to Outlook, so you can quickly open a contact item without having to first open an Access database.

If you have a set of linked Access tables of contact data, and you want to keep the data in the tables synchronized with matching Outlook contact items, you need more than a simple export. See Chapter 11 for a discussion of two-way synchronizing between Access and Outlook contacts.

To export data from an Access flat-file contacts table (such as tblContactsToExport) to Outlook contacts in a custom Contacts folder called Contacts from Access, use the function listed as follows (it can also be run from the mcrExportFlatFileContactsToOutlook macro):

Public Function ExportFlatFileContactsToOutlook()

On Error GoTo ErrorHandler

Set appOutlook = GetObject(, "Outlook.Application")

Dim lngContactID As Long

Dim lngContactCount As Long

Dim fld As Outlook.Folder

Dim fldContacts As Outlook.Folder

Dim conNew As Outlook.Contactltem

Dim conTest As Outlook.Contactltem

Dim strFullName As String

Dim strFirstName As String

Dim strLastName As String

Dim strBusinessPhone As String

Dim strMobilePhone As String

Dim strFaxNumber As String

Dim strNotes As String

Dim strJobTitle As String

Dim strStreetAddress As String

Dim strCity As String

Dim strStateProv As String

Dim strPostalCode As String

Dim strCountry As String

Dim strCompanyName As String

Dim strEMail As String

Dim strSalutation As String

Set appOutlook = GetObject(, "Outlook.Application") Set nms = appOutlook.GetNamespace("MAPI")

Use the following line to export to the default local Contacts folder:

'Set fldContacts = nms.GetDefaultFolder(olFolderTasks)

On Error Resume Next

Use the following lines to export to a custom Contacts folder, creating it if necessary. If setting a reference to the folder fails, the folder will be created.

CROSS-REF

Set fld = nms.Folders("Personal Folders") Set fldContacts = fld.Folders("Contacts from Access") If fldContacts Is Nothing Then Set fldContacts = _

fld.Folders.Add("Contacts from Access", _ olFolderContacts)

End If

On Error GoTo ErrorHandler lngContactCount = 0 Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblContactsToExport")

With rst

Do While Not .EOF

Check for required name information.

strFullName = Nz(![FirstName]) & " " _

& Nz(![LastName]) Debug.Print "Contact name: " & strFullName If strFullName = "" Then

GoTo NextContact End If

Check whether there already is an Outlook contact item for this person.

On Error Resume Next

Set conTest = fldContacts.Items(strFullName) If conTest.FullName <> strFullName Then

No matching contact found.

Debug.Print strFullName & " not found" ElseIf conTest.FullName = strFullName Then Debug .Print strFullName & " found" GoTo NextContact End If

On Error GoTo ErrorHandler lngContactID = Nz(![ContactID]) strCompanyName = Nz(![CompanyName]) strFirstName = Nz(![FirstName]) strLastName = Nz(![LastName]) strSalutation = Nz(![Salutation]) strEMail = Nz(![EmailName]) strJobTitle = Nz(![JobTitle]) strBusinessPhone = Nz(![WorkPhone]) _

& IIf(Nz(![WorkExtension]) <> "", " x " _ & ![WorkExtension], "") strMobilePhone = Nz(![MobilePhone]) strFaxNumber = Nz(![FaxNumber]) strNotes = Nz(![Notes])

strStreetAddress = Nz(![StreetAddress]) strCity = Nz(![City])

strStateProv = Nz(![StateOrProvince]) strPostalCode = Nz(![PostalCode]) strCountry = Nz(![Country])

Create a new contact item in the selected Contacts folder.

Set conNew = fldContacts.Items.Add With conNew

.CustomerID = lngContactID .FirstName = strFirstName .LastName = strLastName .JobTitle = strJobTitle

.BusinessAddressStreet = strStreetAddress .BusinessAddressCity = strCity .BusinessAddressState = strStateProv .BusinessAddressPostalCode = strPostalCode .BusinessAddressCountry = strCountry .CompanyName = strCompanyName .EmailiAddress = strEMail

.BusinessTelephoneNumber = strBusinessPhone .BusinessFaxNumber = strFaxNumber .MobileTelephoneNumber = strMobilePhone .NickName = strSalutation .Body = strNotes .Close (olSave) End With lngContactCount = lngContactCount + i NextContact:

.MoveNext Loop End With rst.Close

If lngContactCount = 0 Then

MsgBox "No unique contacts to export to Outlook" Else

MsgBox lngContactCount & " contact(s) exported to Outlook" End If

ErrorHandlerExit: Exit Function

ErrorHandler:

Outlook is not running; open Outlook with CreateObject.

If Err.Number = 42 9 Then

Set appOutlook = CreateObject("Outlook.Application") Resume Next Else

& "; Description: " & Err.Description Resume ErrorHandlerExit End If

End Function

Figure 8.27 shows some of the contacts exported to the custom Contacts from Access folder.

FIGURE 8.27

Contacts in a custom Outlook folder exported from Access, in the new Business Card view.

FIGURE 8.27

To import contacts from your local Outlook Contacts folder into an Access table (tblImportedContacts), use the following function (it can also be run from the mcrImportContactsFromOutlook macro):

Public Function ImportContactsFromOutlook()

On Error GoTo ErrorHandler

Dim lngContactCount As Long

Dim fld As Outlook.Folder

Dim fldContacts As Outlook.Folder

Dim con As Outlook.Contactltem

Dim strFullName As String

Dim strFirstName As String

Dim strLastName As String

Dim strBusinessPhone As String

Dim strHomePhone As String

Dim strMobilePhone As String

Dim strFaxNumber As String

Dim strNotes As String

Dim strJobTitle As String

Dim strWorkAddress As String

Dim strWorkCity As String

Dim strWorkStateProv As String

Dim strWorkPostalCode As String

Dim strWorkCountry As String

Dim strHomeAddress As String

Dim strHomeCity As String

Dim strHomeStateProv As String

Dim strHomePostalCode As String

Dim strHomeCountry As String

Dim strCompanyName As String

Dim strEMail As String

Dim strSalutation As String

Dim itm As Object

Dim strSQL As String

Dim strWebSite As String

Set appOutlook = GetObject(, "Outlook.Application") Set nms = appOutlook.GetNamespace("MAPI")

Use the following line to import from the default local Contacts folder:

'Set fldContacts = nms.GetDefaultFolder(olFolderTasks)

On Error Resume Next

Use the following lines to set a reference to a custom Contacts folder, creating it if necessary. If setting a reference to the folder fails, the folder will be created.

Set fld = nms.Folders("Personal Folders")

Set fldContacts = fld.Folders("Contacts to Export")

If fldContacts Is Nothing Then Set fldContacts = _

fld.Folders.Add("Contacts to Export", _ olFolderContacts)

End If

On Error GoTo ErrorHandler Clear the table of old data.

strSQL = "DELETE * FROM tbllmportedContacts"

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

lngContactCount = 0

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblImportedContacts") Iterate through contacts in selected Contacts folder and import them to the Access table.

For Each itm In fldContacts.Items If itm.Class = olContact Then Set con = itm With con strFirstName = Nz(.FirstName) strLastName = Nz(.LastName) strJobTitle = Nz(.JobTitle)

strWorkAddress = Nz(.BusinessAddressStreet) strWorkCity = Nz(.BusinessAddressCity) strWorkStateProv = Nz(.BusinessAddressState) strWorkPostalCode = _

Nz(.BusinessAddressPostalCode) strWorkCountry = Nz(.BusinessAddressCountry) strHomeAddress = Nz(.HomeAddress) strHomeCity = Nz(.HomeAddressCity) strHomeStateProv = Nz(.HomeAddressState) strHomePostalCode = Nz(.HomeAddressPostalCode) strHomeCountry = Nz(.HomeAddressCountry) strCompanyName = Nz(.CompanyName) strEMail = Nz(.EmaillAddress) strBusinessPhone = _

Nz(.BusinessTelephoneNumber) strFaxNumber = Nz(.BusinessFaxNumber) strMobilePhone = Nz(.MobileTelephoneNumber) strSalutation = Nz(.NickName)

strWebSite = Nz(.WebPage) strNotes = Nz(.Body) .Close (olSave) End With

With rst rst.AddNew

![CompanyName] = strCompanyName ![FirstName] = strFirstName ![LastName] = strLastName ![Salutation] = strSalutation ![EmailName] = strEMail ![JobTitle] = strJobTitle ![WorkPhone] = strBusinessPhone ![MobilePhone] = strMobilePhone ![FaxNumber] = strFaxNumber ![Notes] = strNotes ![WorkAddress] = strWorkAddress ![WorkCity] = strWorkCity

![WorkStateOrProvince] = strWorkStateProv ![WorkPostalCode] = strWorkPostalCode ![WorkCountry] = strWorkCountry ![HomeAddress] = strHomeAddress ![HomeCity] = strHomeCity

![HomeStateOrProvince] = strHomeStateProv ![HomePostalCode] = strHomePostalCode ![HomeCountry] = strHomeCountry ![WorkPhone] = strHomePhone ![WebSite] = strWebSite .Update End With lngContactCount = lngContactCount + 1 End If Next itm rst.Close

If lngContactCount = 0 Then

MsgBox "No contacts to import from Outlook" Else

MsgBox lngContactCount _

& " contact(s) imported from Outlook"

End If ErrorHandlerExit: Exit Function

If Err.Number = 429 Then

Set appOutlook = CreateObject("Outlook.Application") Resume Next

Else

& "; Description: " & Err.Description Resume ErrorHandlerExit End If

End Function

FIGURE 8.28

A table of contact data imported from an Outlook folder.

Ess TDumportenLoniacu

[I Contact ID - First Name -

Last Name -

Salutation - WorkAddress

WorkCity -

State/Province

- Postal Co -

VV0

-

IS Heather

Acosta

10193 152nd Terr. Suite 346

Clearwater

MY

11111-1111

us;

20 Nancy

Davolio

507-20th Ave. E. Apt. 2A

Seattle

WA

36221-6787

US/

21 Jennifer

Abramson

12990 26th Way Room 973

Union City

NE

45779-4912

us;

22 Andrea

Gates

5109 64th Ave. Suite 963

Greenwood

NC

84839-3930

us/

23 Roy

Acosta

1230 Bartholomew Ln. Ste. 519

Aurora

UT

83567-1269

us/

24 Rex

Acosta

4005 RidgelineWay Suite 968

Willow

PA

12066-5154

US/

25 Holly

Ackerman

13032 30th St.

Lake Wallace

GA

41198-1091

US/

26 Rene

Walker

3023 64th Blvd. Room 645

^m

AK

89880-4262

us/

27 Lou

Gay lord

5449 25th Blvd. eidg. 215

Lincoln

MD

49557-1011

US/

1 Record h 4,|2 of 17 | ► H t

1 Search A

When working with contacts, the ideal situation would be to maintain your most complete information in a set of linked Access tables, and synchronize them with Outlook contacts that would display most of the information in an easy-to-use interface. Chapter 11 covers synchronizing linked Access contact tables with a folder of Outlook contacts.

CRGss-_RcrJ

0 0

Post a comment