Resolve UserName in Exchange Server

[LarryDeLaruelle]

We are using an application called bmail to send help desk notices to the appropriate department. The email are sent to an external account (dept@fccin.org). The 'from' line is populated with the user's name captured as Environ("UserName"). This format is correct for internal email.

Upon receipt of the email, Exchange appends '@fccin.org' to the name in the from field. The problem is that users' email address format is FirstInitialLastName@fccin.org. Our help department staff are not happy with the undeliverable mail when they attempt to reply to the requests.

Is there a way, within Access, to query the exchange server to resolve the username to a valid email address?

We are using Access 2000 and Exchange Server 2003.

Thanks.

Larry De Laruelle

 

[joelflorendo]

I use the following LDAP query against our AD server which should have the data you're looking for. You can edit the SQL to suit. You'll just have to find out what the field names are of whatever data you're looking for but I think the query I'm running should give you everything you need.

The sAMAccountName is the domain alias which should just be the first part of the email address and then append @yourdomain to the end of that to get the valid email. You can filter by the displayName field since that's usually how it shows up in the from line or else you can use the sn (surname) and givenName (firstname) fields.

I kind of had to piece this together since my code was split out into different modules so hopefully it works ok.

Const ADS_SCOPE_SUBTREE = 2
    
Dim oRoot As Object
Dim aCmd As ADODB.Command
Dim aCnn as ADODB.Connection
Dim adRS As ADODB.Recordset
Dim sSql As String

Set oRoot = GetObject("LDAP://RootDSE")
sSearchRoot = "LDAP://" & oRoot.Get("defaultNamingContext")

Set aCnn = New ADODB.Connection
aCnn.Provider = "ADsDSOObject"
aCnn.Open "Active Directory Provider"

Set aCmd = New ADODB.Command
Set aCmd.ActiveConnection = aCnn
    
aCmd.Properties("Page Size") = 1000
aCmd.Properties("Searchscope") = ADS_SCOPE_SUBTREE

sSql = "SELECT sAMAccountName, sn, givenName, displayName, proxyAddresses, objectCategory " & _
       "FROM '" & sSearchRoot & "' " & _
       "WHERE msExchHideFromAddressLists <> TRUE AND objectClass = 'user' AND objectCategory='person' " & _
       "ORDER BY sAMAccountName ASC"
       
aCmd.CommandText = sSql

Set adRS = aCmd.Execute

aCnn.Close

Set aCnn = Nothing
Set aCmd = Nothing
Set oRoot = Nothing