VBA (outlook)

Hi there,

Trying to make something to return the canonical name when given a username.

So far each msgbox keeps returning the username
here's what i got:

      Set CNUsers = GetObject ("LDAP://OU=users,OU=city,DC=company,DC=com")
      CNUsers.Filter = Array("user")

      For Each User in CNUsers
            chkLoginname = User.sAMAccountName
            If chkLoginname = HDUsername Then
                  msgbox "chkLoginname = " & chkLoginname
                  msgbox "HDUsername = " & HDUsername
                  DisplayName = User.displayName
                  msgbox "DisplayName = " & DisplayName
                                                    ' at this point, User.displayName still returns their short username, ie: jdoe instead
                                                    ' of John Doe
                      txfullname.text = DisplayName
                  Exit For
            End If
                 Next



Please help!

Jamie
LVL 1
jcourtesAsked:
Who is Participating?
 
David LeeConnect With a Mentor Commented:
Try this:

rsDetails.Source = "SELECT ADsPath, displayName, Company, Department, Division FROM 'LDAP://DomainName' WHERE objectClass='user' AND objectCategory='Person' AND samAccountName='" & HDUsername & "'"
0
 
David LeeCommented:
Hi, Jamie.

To get the CanonicalName you'll need to retrieve the CanonicalName property.  I'm not sure it's part of the property cache, so you may have to use GetEx.  Sorry for not being sure, but prefer reading AD via ADO and not LDAP.  I should also point out that CanonicalName may be returned as an array.

Cheers!
0
 
jcourtesAuthor Commented:
I found my answer actually. here's what's working now:


      Set CNUsers = GetObject ("LDAP://OU=Users,OU=Montreal,DC=company,DC=com")
      CNUsers.Filter = Array("user")

      For Each User in CNUsers
            chkLoginname = User.sAMAccountName
            If chkLoginname = HDUsername Then
                  txfullname.text = User.givenName & " " & User.sn
                  txtelephone.text = User.telephoneNumber
                  txlocation.text = User.l
                  Exit For
            End If
      Next



The only problem is that it's in form load and it makes it take 4-5 seconds to open as it searches AD.

Any recomendations on something that would be much quicker, or something that runs after load? i dunno... still kinda new at this.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
David LeeCommented:
What is it you're trying to accomplish?
0
 
jcourtesAuthor Commented:
BluedevilFan,

I'm basically trying to make a helpdesk style form which has txt boxes with some of the information of the user.
their IP address, hostname, username, telephone number+location+fullname(active directory)

I perhaps need a more direct way of getting the user properties than going through the list until it finds one...

(the last section)

'''''
'
''''''


Public ipaddarr()
Public nCounter

Function Item_Open()
      Dim      HDUsername      'Declare everything
      Dim      HDHostname
      Dim      WshNetwork
      Dim      Testvalue
      Dim      txhostname
      Dim      txusername
      Dim      txipaddress1
      Dim      transipaddress
      Dim      CNUsers

      Dim      pthUsername
      Dim      chkLoginname
      Dim      xfertest

      Set WshNetwork = CreateObject("WScript.Network")      'Object decs.
      Set oPage = Item.GetInspector.ModifiedFormPages
      Set txhostname = oPage("Message").Controls("txhostname")
      Set txusername = oPage("Message").Controls("txusername")
      Set txipaddress1 = oPage("Message").Controls("txipaddress1")
      Set txipaddress2 = oPage("Message").Controls("txipaddress2")
      Set txfullname = oPage("Message").Controls("txfullname")
      Set txtelephone = oPage("Message").Controls("txtelephone")
      Set txlocation = oPage("Message").Controls("txlocation")
      Set IPConfigSet = GetObject("winmgmts:").ExecQuery("select IPAddress from Win32_NetworkAdapterConfiguration where IPEnabled=TRUE")

      txhostname.text = ""
      txusername.text = ""
      txipaddress1.text = ""
      txipaddress2.text = ""
      txfullname.text = ""
      txtelephone.text = ""
      txlocation.text = ""

      HDHostname = WshNetwork.ComputerName
      HDUsername = WshNetwork.UserName
      Set WshNetwork = Nothing

      nCounter = 0
      For Each IPConfig In IPConfigSet
                If Not IsNull(IPConfig.IPAddress) Then
                    For I = LBound(IPConfig.IPAddress) To UBound(IPConfig.IPAddress)
                        nCounter = nCounter+1
                        Redim Preserve ipaddarr(nCounter)
                        ipaddarr(nCounter) = IPConfig.IPAddress(I)
                    Next
                End If
      Next

      txusername.text = HDUsername      'Write to fields
      txhostname.text = HDHostname
      txipaddress1.text = ipaddarr(1)
      txipaddress2.text = ipaddarr(2)
      '
      Set CNUsers = GetObject ("LDAP://OU=Users,OU=Montreal,DC=company,DC=com")
      CNUsers.Filter = Array("user")

      For Each User in CNUsers
            chkLoginname = User.sAMAccountName
            If chkLoginname = HDUsername Then
                  txfullname.text = User.givenName & " " & User.sn
                  txtelephone.text = User.telephoneNumber
                  txlocation.text = User.l
                  Exit For
            End If
      Next
      '
End Function
   
0
 
David LeeCommented:
Nice idea.  You can speed this up some by retrieving the desired user's account directly rather than having to read through all the AD accounts until you find the one you want.  Are you familiar with reading AD via ADO?
0
 
jcourtesAuthor Commented:
nope, i've undertook this project educating myself on using LDAP, I take it you suggest ADO ?
0
 
David LeeCommented:
Yes, because it allows you to query AD using SQL syntax.  Here's an example.  This is a bit of VB Script, but it shows how this works.  It allows you to treat AD like a database table, the lone exception being that this is read only.  In this example we'd retrieve a single AD account, the one belonging to the username jdoe.

Set rsDetails = Wscript.CreateObject("ADODB.Recordset")
rsDetails.ActiveConnection = "Provider=ADSDSOObject"
'Replace DomainName on the following line with the name of your domain
rsDetails.Source = "SELECT ADsPath, displayName, Company, Department, Division FROM 'LDAP://DomainName' WHERE objectClass='user' AND objectCategory='Person' AND samAccountName="jdoe'"
rsDetails.CursorType = 0
rsDetails.CursorLocation = 2
rsDetails.LockType = 1
rsDetails.Open()
If Not rsDetails.EOF Then
    With rsDetails
        Wscript.Echo "Name=" & .Fields("displayName") & " Company=" & .Fields("Company") & " Department=" & .Fields("Department") & " Division=" & .Fields("Division")
    End With
Wend
rsDetails.Close
Set rsDetails = Nothing
0
 
jcourtesAuthor Commented:
BlueDevilFan,

That code is much faster, I like!  </borat>

I've incorporated it into my code, and it works.

I do have a problem with it tho
on the line:
rsDetails.Source = "SELECT ADsPath, displayName, Company, Department, Division FROM 'LDAP://DomainName' WHERE objectClass='user' AND objectCategory='Person' AND samAccountName='jdoe'"

I'm trying to provide it with the username from a variable (HDUsername).
I havent found the correct way to reference the variable so the samAccountName=HDUsername

Can you assist?

Thanks,
Jamie

0
 
jcourtesAuthor Commented:
BlueDevilFan,

Thank you so much, you're really good!

Here is the end result. You'll notice the extra if's, it seems I needed to validate that the fields weren't empty before trying to write their values to my objects on the form. If I didn't, it would generate errors.
But it works great, practically instantaneous, and definately a valuable piece of code to keep near.
I have no SQL experience, but I can work around things. Great assistance on another one of my questions.

Thank you again,
Jamie

===============

      Set rsDetails = CreateObject("ADODB.Recordset")
      rsDetails.ActiveConnection = "Provider=ADSDSOObject"
      rsDetails.Source = "SELECT ADsPath, displayName, l, givenName, sn, telephoneNumber FROM 'LDAP://server' WHERE objectClass='user' AND objectCategory='Person' AND samAccountName='" & HDUsername & "'"
      rsDetails.CursorType = 0
      rsDetails.CursorLocation = 2
      rsDetails.LockType = 1
      rsDetails.Open()
      If Not rsDetails.EOF Then
                With rsDetails
                  txfullname.text = .Fields("givenName") & " " & .Fields("sn")
                  If Not IsNull(.Fields("telephoneNumber")) Then
                        txtelephone.text = .Fields("telephoneNumber")
                  End If
                  If Not IsNull(.Fields("l")) Then
                        txlocation.text = .Fields("l")
                  End If
                End With
      end if
      rsDetails.Close
      Set rsDetails = Nothing
0
 
David LeeCommented:
You're welcome, Jamie.  
0
 
sirbountyCommented:
"Thank you so much, you're really good! "

And when you're really good, they call you Cracker Jack!
Haha...hi 'neighbor' ;)  
Nice work here...

Sincerely,
LongTimeFan
0
 
David LeeCommented:
Thanks, SB!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.