Solved

VBA (outlook)

Posted on 2006-11-14
13
443 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

746 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

10 Experts available now in Live!

Get 1:1 Help Now