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.
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.
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 ?
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.
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
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
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
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
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Exporting data to Outlook Contact Folder