Solved

VBA (outlook)

Posted on 2006-11-14
13
453 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:jcourtes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 17943059
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
 
LVL 1

Author Comment

by:jcourtes
ID: 17943176
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
 
LVL 76

Expert Comment

by:David Lee
ID: 17943225
What is it you're trying to accomplish?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:jcourtes
ID: 17943331
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
 
LVL 76

Expert Comment

by:David Lee
ID: 17943363
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
 
LVL 1

Author Comment

by:jcourtes
ID: 17943382
nope, i've undertook this project educating myself on using LDAP, I take it you suggest ADO ?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17943686
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
 
LVL 1

Author Comment

by:jcourtes
ID: 17946830
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
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 17948864
Try this:

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

Author Comment

by:jcourtes
ID: 17953105
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
 
LVL 76

Expert Comment

by:David Lee
ID: 17953135
You're welcome, Jamie.  
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 17953169
"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
 
LVL 76

Expert Comment

by:David Lee
ID: 17968027
Thanks, SB!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question