Okay, I've asked a couple of questions and maybe I should be giving the whole
picture.

I pull data from 5 different sources and the only common data is name and
alias. 3 of these sources give me data with that stupid return character
after SOME of the fields but not all. I need to remove that character. The
clean function works but only if there is a non-readable character. If not I
get the #Value in the cell.

One of the other sources has names but not necessarily alias' and not
necessarily the name spelled correctly. (That field is user entered and I
can't get around that).

I need to combine all the data then check the names against the last source,
but not every entry may have a name in the last source so those need to be
checked against the Outlook global address list and return their alias.

I thought this would be easy. I can call the Outlook application but I
can't figure out how to look up a name and return the alias. Am I being too
optomistic about this being simple?

Here is some code that I use to get e-mail addresses from the global address
list. You need to reference the CDO library to use this function.

Option Explicit
Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E
Const g_strMAPILogOn As String = "MS Exchange Settings"
Const g_strAddressList As String = "Global Address List"
Const g_strEMailAddressIdentifier As String = "SMTP"


Private Sub Test()

MsgBox GetEMailAddress("James Thomlinson")

End Sub


Public Function GetEMailAddress(ByVal strName As String) As String
Dim objSession As MAPI.Session
Dim objField As MAPI.Field
Dim MyAddressList As MAPI.AddressList
Dim MyAddressEntries As MAPI.AddressEntries
Dim MyEntry As MAPI.AddressEntry
Dim SomeEntry As MAPI.AddressEntry
Dim MyRecipient As MAPI.Recipient
Dim v As Variant
Dim strReturnValue As String

'Initialize Local Variables
strReturnValue = "No Address Found" 'Retrun Value if not found

' Create Session object and Logon.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon (g_strMAPILogOn)

'Create the Address list from the Global Address List
Set MyAddressList = objSession.AddressLists(g_strAddressList)
If MyAddressList Is Nothing Then
MsgBox g_strAddressList & " Unavailable!", vbCritical, "Critical
Error"
Exit Function
End If

'Initialize MyAddressEntires with the entries in the Address List
Set MyAddressEntries = MyAddressList.AddressEntries

'Traverse through the entries searching for a match
For Each SomeEntry In MyAddressEntries
Set MyEntry = SomeEntry
If Trim(UCase(strName)) = Trim(UCase(MyEntry.Name)) Then
Set objField = MyEntry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES)

' PR_EMS_AB_PROXY_ADDRESSES is a multivalued property
(PT_MV_TSTRING).
' Therefore, you need to extract the individual members.
For Each v In objField.Value
If InStr(1, UCase(v), g_strEMailAddressIdentifier) Then
strReturnValue = Mid(v, 6, 256)
Exit For
End If
Next 'Next Field Value
Exit For
End If
Next 'Next Address Entry

'Return Function Value
GetEMailAddress = strReturnValue

'Housekeeping
Set objField = Nothing
Set MyAddressList = Nothing
Set MyAddressEntries = Nothing
Set MyEntry = Nothing
Set MyRecipient = Nothing
objSession.Logoff
Set objSession = Nothing

End Function

As for the extra characters and such that is parsing and it depends on the
data...

"JJ" wrote:

Okay, I've asked a couple of questions and maybe I should be giving the whole
picture.

I pull data from 5 different sources and the only common data is name and
alias. 3 of these sources give me data with that stupid return character
after SOME of the fields but not all. I need to remove that character. The
clean function works but only if there is a non-readable character. If not I
get the #Value in the cell.

One of the other sources has names but not necessarily alias' and not
necessarily the name spelled correctly. (That field is user entered and I
can't get around that).

I need to combine all the data then check the names against the last source,
but not every entry may have a name in the last source so those need to be
checked against the Outlook global address list and return their alias.

I thought this would be easy. I can call the Outlook application but I
can't figure out how to look up a name and return the alias. Am I being too
optomistic about this being simple?

Great!!!

The data is being pulled from a Lotus Notes DB and I've noticed that it
usually is just the return character. Is there a character code for it? I
can do a find replace if I can get the Chr(#), can't I?

"Jim Thomlinson" wrote:

Here is some code that I use to get e-mail addresses from the global address
list. You need to reference the CDO library to use this function.

Option Explicit
Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E
Const g_strMAPILogOn As String = "MS Exchange Settings"
Const g_strAddressList As String = "Global Address List"
Const g_strEMailAddressIdentifier As String = "SMTP"


Private Sub Test()

MsgBox GetEMailAddress("James Thomlinson")

End Sub


Public Function GetEMailAddress(ByVal strName As String) As String
Dim objSession As MAPI.Session
Dim objField As MAPI.Field
Dim MyAddressList As MAPI.AddressList
Dim MyAddressEntries As MAPI.AddressEntries
Dim MyEntry As MAPI.AddressEntry
Dim SomeEntry As MAPI.AddressEntry
Dim MyRecipient As MAPI.Recipient
Dim v As Variant
Dim strReturnValue As String

'Initialize Local Variables
strReturnValue = "No Address Found" 'Retrun Value if not found

' Create Session object and Logon.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon (g_strMAPILogOn)

'Create the Address list from the Global Address List
Set MyAddressList = objSession.AddressLists(g_strAddressList)
If MyAddressList Is Nothing Then
MsgBox g_strAddressList & " Unavailable!", vbCritical, "Critical
Error"
Exit Function
End If

'Initialize MyAddressEntires with the entries in the Address List
Set MyAddressEntries = MyAddressList.AddressEntries

'Traverse through the entries searching for a match
For Each SomeEntry In MyAddressEntries
Set MyEntry = SomeEntry
If Trim(UCase(strName)) = Trim(UCase(MyEntry.Name)) Then
Set objField = MyEntry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES)

' PR_EMS_AB_PROXY_ADDRESSES is a multivalued property
(PT_MV_TSTRING).
' Therefore, you need to extract the individual members.
For Each v In objField.Value
If InStr(1, UCase(v), g_strEMailAddressIdentifier) Then
strReturnValue = Mid(v, 6, 256)
Exit For
End If
Next 'Next Field Value
Exit For
End If
Next 'Next Address Entry

'Return Function Value
GetEMailAddress = strReturnValue

'Housekeeping
Set objField = Nothing
Set MyAddressList = Nothing
Set MyAddressEntries = Nothing
Set MyEntry = Nothing
Set MyRecipient = Nothing
objSession.Logoff
Set objSession = Nothing

End Function

As for the extra characters and such that is parsing and it depends on the
data...

"JJ" wrote:

Okay, I've asked a couple of questions and maybe I should be giving the whole
picture.

I pull data from 5 different sources and the only common data is name and
alias. 3 of these sources give me data with that stupid return character
after SOME of the fields but not all. I need to remove that character. The
clean function works but only if there is a non-readable character. If not I
get the #Value in the cell.

One of the other sources has names but not necessarily alias' and not
necessarily the name spelled correctly. (That field is user entered and I
can't get around that).

I need to combine all the data then check the names against the last source,
but not every entry may have a name in the last source so those need to be
checked against the Outlook global address list and return their alias.

I thought this would be easy. I can call the Outlook application but I
can't figure out how to look up a name and return the alias. Am I being too
optomistic about this being simple?

That should work. You just need to look up the ASCII reference.

"JJ" wrote:

Great!!!

The data is being pulled from a Lotus Notes DB and I've noticed that it
usually is just the return character. Is there a character code for it? I
can do a find replace if I can get the Chr(#), can't I?

"Jim Thomlinson" wrote:

Here is some code that I use to get e-mail addresses from the global address
list. You need to reference the CDO library to use this function.

Option Explicit
Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E
Const g_strMAPILogOn As String = "MS Exchange Settings"
Const g_strAddressList As String = "Global Address List"
Const g_strEMailAddressIdentifier As String = "SMTP"


Private Sub Test()

MsgBox GetEMailAddress("James Thomlinson")

End Sub


Public Function GetEMailAddress(ByVal strName As String) As String
Dim objSession As MAPI.Session
Dim objField As MAPI.Field
Dim MyAddressList As MAPI.AddressList
Dim MyAddressEntries As MAPI.AddressEntries
Dim MyEntry As MAPI.AddressEntry
Dim SomeEntry As MAPI.AddressEntry
Dim MyRecipient As MAPI.Recipient
Dim v As Variant
Dim strReturnValue As String

'Initialize Local Variables
strReturnValue = "No Address Found" 'Retrun Value if not found

' Create Session object and Logon.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon (g_strMAPILogOn)

'Create the Address list from the Global Address List
Set MyAddressList = objSession.AddressLists(g_strAddressList)
If MyAddressList Is Nothing Then
MsgBox g_strAddressList & " Unavailable!", vbCritical, "Critical
Error"
Exit Function
End If

'Initialize MyAddressEntires with the entries in the Address List
Set MyAddressEntries = MyAddressList.AddressEntries

'Traverse through the entries searching for a match
For Each SomeEntry In MyAddressEntries
Set MyEntry = SomeEntry
If Trim(UCase(strName)) = Trim(UCase(MyEntry.Name)) Then
Set objField = MyEntry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES)

' PR_EMS_AB_PROXY_ADDRESSES is a multivalued property
(PT_MV_TSTRING).
' Therefore, you need to extract the individual members.
For Each v In objField.Value
If InStr(1, UCase(v), g_strEMailAddressIdentifier) Then
strReturnValue = Mid(v, 6, 256)
Exit For
End If
Next 'Next Field Value
Exit For
End If
Next 'Next Address Entry

'Return Function Value
GetEMailAddress = strReturnValue

'Housekeeping
Set objField = Nothing
Set MyAddressList = Nothing
Set MyAddressEntries = Nothing
Set MyEntry = Nothing
Set MyRecipient = Nothing
objSession.Logoff
Set objSession = Nothing

End Function

As for the extra characters and such that is parsing and it depends on the
data...

"JJ" wrote:

Okay, I've asked a couple of questions and maybe I should be giving the whole
picture.

I pull data from 5 different sources and the only common data is name and
alias. 3 of these sources give me data with that stupid return character
after SOME of the fields but not all. I need to remove that character. The
clean function works but only if there is a non-readable character. If not I
get the #Value in the cell.

One of the other sources has names but not necessarily alias' and not
necessarily the name spelled correctly. (That field is user entered and I
can't get around that).

I need to combine all the data then check the names against the last source,
but not every entry may have a name in the last source so those need to be
checked against the Outlook global address list and return their alias.

I thought this would be easy. I can call the Outlook application but I
can't figure out how to look up a name and return the alias. Am I being too
optomistic about this being simple?