Solved

VBA (outlook)

Posted on 2006-11-14
13
447 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now