General discussion

Locked

Exporting data to Outlook Contact Folder

By Father Jack ·
I am writing a process in Access 97 to export data to an Outlook2000 Contact Folder but have run into a problem. Each line of the address is exported individually into the Outlook fields but when the address is viewed as a contact the lines have been scrambled. However looking at the All Contact Fields list that data has been exported correctly. has anyone got any ideas please ?

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Exporting data to Outlook Contact Folder

by Stillatit In reply to Exporting data to Outlook ...

Outlook address fields can be addressed either as the parts or the whole. BusinessAddress, for example, can be set as item.BusinessAddress or it can be addressed in parts, as item.BusinessStreet1 item.BusinessStreet2 item.BusinessStreet3 item.BusinessCity item.BusinessState item.BusinessPostalCode item.BusinessAddressCountry and Outlook will parse it correctly. In the combined versions there is a lf [chr(10)] between the parts.

In the worst case, create a spreadsheet from Access and importthat into Outlook.

There is a pretty good Microsoft Press book titled "Programming Outlook and Exchange" which I found useful.

Also look at http://support.microsoft.com/support/Office/InProdHlp/Outlook/rehowOutlookFieldsEquivalentProperties.asp

Good luck.

Collapse -

Exporting data to Outlook Contact Folder

by Father Jack In reply to Exporting data to Outlook ...

I am using the .businessstreet1 / .businessstreet2 / .businessstreet3 / etc method and it is the parsing that is not following suit to combine the pieces correctly. There are approx 1300 contatcs to create and i want the process to be automatic for the user so the Excel option is a non-starter.
Any more ideas please ?

Collapse -

Exporting data to Outlook Contact Folder

by Stillatit In reply to Exporting data to Outlook ...

How are the lines scrambled? If your Access database shows the address as:
123 Main St.
Suite 1a
Anytown
CA
90000
and you plug the data into BusinessStreet1, BusinessStreet2, BusinessCity, BusinessState, BusinessPostalCode:
a) Do the fields move into the appropriate fields in Outlook?
b) Do the pieces combine correctly in BusinessAddress?
c) Does it display properly on the General tab?

Remember that the carriage-return delimiters do not show up in BusinessStreet or BusinessAddress, but you have to build it that way. If you shove the data lines into BusinessStreet, you would put in "123 Main St." & chr(13) & "Suite 1a"

It might be useful to go into the Basic editor in Outlook, and from Basic grab a contact built in Outlook and one of your exports. Use the watch window or some code to find out what is really happening.

The comment below is a subroutine I use for tearing apart the compound address fields when I am doing a fancy export.

Good luck

Collapse -

Exporting data to Outlook Contact Folder

by Stillatit In reply to Exporting data to Outlook ...

Private Sub ParseStreetAddress()
Dim locAdd4 As String
Dim locAdd5 As String
Dim locAdd6 As String
Dim locN1 As Integer
Dim locN2 As Integer
Dim locN3 As Integer
Dim locN4 As Integer
Dim locN5 As Integer
Dim locN6 As Integer
Dim locCRLF As String
Dim locTmp As String
Dim locLF As String

locCRLF = Chr(13) & Chr(10)
locLF = Chr(10)
gblAdd1 = ""
gblAdd2 = ""
gblAdd3 = ""
locAdd4 = ""
locAdd5 = "" locAdd6 = ""

If Len(gblAdd0) = 0 Then Exit Sub

locN1 = InStr(gblAdd0, locLF) 'Find LF
If locN1 = 0 Then
'No, grab the first address line and quit
gblAdd1 = gblAdd0
Exit Sub
End If

'Found LF, grab first address line
gblAdd1 = Left(gblAdd0, locN1 - 1)
If Right(gblAdd1, 1) = Chr(13) Then gblAdd1 = Left(gblAdd1, Len(gblAdd1) - 1)


locN2 = InStr(locN1 + 1, gblAdd0, locLF) 'Is there another one?
If locN2 = 0 Then
'No, grab the second address line and quit
gblAdd2 = Mid(gblAdd0, locN1 + 1)
Exit Sub

Collapse -

Exporting data to Outlook Contact Folder

by Stillatit In reply to Exporting data to Outlook ...

Else
'Found CRLF, grab second address line
gblAdd2 = Mid(gblAdd0, locN1 + 1, locN2 - locN1 - 1)
If Right(gblAdd2, 1) = Chr(13) Then gblAdd2 = Left(gblAdd2, Len(gblAdd2) - 1)
End If

locN3 = InStr(locN2 + 1, gblAdd0, locLF) 'Is there another one?
If locN3 = 0 Then
'No, grab the third address line and quit
gblAdd3 = Mid(gblAdd0, locN2 + 1)
Exit Sub
Else
'Found LF, grab third address line
gblAdd3 =Mid(gblAdd0, locN2 + 1, locN3 - locN2 - 1)
If Right(gblAdd3, 1) = Chr(13) Then gblAdd3 = Left(gblAdd3, Len(gblAdd3) - 1)
End If

locN4 = InStr(locN3 + 1, gblAdd0, locLF) 'Is there another one?
If locN4 = 0 Then'No, grab the fourth address line
locAdd4 = Mid(gblAdd0, locN3 + 1)
'Combine the first and second addresses, move 3 to 2, move 4 to 3
locTmp = gblAdd1 & " " & gblAdd2
gblAdd1 = locTmp
gblAdd2 = gblAdd3 gblAdd3 = locAdd4
Exit Sub
Else
'Found LF, grab fourth address line
locAdd4 = Mid(gblAdd0, locN3 + 1, locN4 - locN3 - 1)
'Combine the first and second addresses, move 3 to 2, move 4 to 3
locTmp = gblAdd1 & " " & gblAdd2
gblAdd1 = locTmp
gblAdd2 = gblAdd3
gblAdd3 = locAdd4

End If

' grab rest of the string
'Clear all CR, LF, or other control chars to space
locAdd5 = Mid(gblAdd0, locN4 + 1)
For locN1 = 1 To Len(locAdd5)
If Asc(Mid(locAdd5, locN1, 1)) < 32 Then Mid(locAdd5, locN1, 1) = " "
Next

'Combine the second and third addresses, move this one to 3
locTmp = gblAdd2 & " " & gblAdd3
gblAdd2 = locTmp
gblAdd3 = locAdd5

End Sub

Collapse -

Exporting data to Outlook Contact Folder

by Father Jack In reply to Exporting data to Outlook ...

a)Yes
b)No
c)No
Sorry got the answer, soon after saying you rfirst answer didnt help! It did, got the idea to build whole address into one string and pass that to item.bussinessaddress and let outlook deal with parsing it into the street,city,state(is everything american?),etc. It works fine now.
Thank you very much for the idea.
The export takes > 15 minutes now and the pc crashes if the folder is open in outlook at the same time as access doing the export, is this normal/to be expected ?
thanks again, code looks interesting, will copy it and try to understandit !!
Fatherjack

Collapse -

Exporting data to Outlook Contact Folder

by Father Jack In reply to Exporting data to Outlook ...

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums