DASL filter in Outlook 2003: Username

We use a DASL filter for filtering tasks in a task folder. This works fine:

"http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/Bearbeiter" = 'kn'

Unfortunately we have to create a separate view for each employee (.../Bearbeiter='EmployeeA', .../Bearbeiter='EmployeeB' ...)

Could we use a function which returns the Windows username or the mailbox users name?
Example:
"http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/Bearbeiter" = UserName()

Best regards
Chris
mbwjkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
Assuming username() is a function returning the name then enclosing the single quoute in double quotes as follows::

"http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/Bearbeiter" = "'" & UserName() & "'"

Chris
0
mbwjkAuthor Commented:
Hi Chris,

unfortunately, UserName() (currently) is no valid function in Outlook DASL for filtering within task views. It was just an example for that what I want to do.

I would like to create a single view for a public task folder (Exchange Server 2003). Each user in our network should only see those tasks where the user defined field "Bearbeiter" contains the AD username.

Maybe I can create a function like UserName()? If so: How can I do this?

Thank you and best regards!
Chris
0
mbwjkAuthor Commented:
This is, what I would like to do.
Image.JPG
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Chris BottomleySoftware Quality Lead EngineerCommented:
Unfortunately I have no access to an exchange server to test possible methods of reading the user name itself.  If nothing has been identified befor eMOnday i'll check on my old outlook pc for anything from the past.

Chris
0
mbwjkAuthor Commented:
Any idea? We would like to create a single view for a task folder within Outlook 2003 / Exchange 2003, which shows different entries for each user, according to the windows user name.

Thanks
Chris
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Bear with me .. I hadn't done anything but i'll check in a few minutes

APologies
Chris
0
Chris BottomleySoftware Quality Lead EngineerCommented:
AS I mentiuoned I no longer have access to a system for testing but try this:

Dim sysInfo as object
    set sysinfo = createobject("adsysteminfo")
    msgbox sysinfo.username

Assuming it works, let me know what you get back, (replace sensitive info with equivalent rubbish characters i.e. dn=fred.com as dn=abcd.com) and if something in the data represdents the user name as you would like it and we'll extrract that datum for you.  If it doesn't work then we can keep looking!

|Chris
0
mbwjkAuthor Commented:
Hi Chris,

thanks. Unfortunately I have no VBA interface within the Outlook filter dialog (see screenshot). I'm looking for a DASL filter which considers the windows user name. In this example, 'kn' should be replaced by an expression, which dynamically returns the windows user name.

Best regards
Chris
Image.jpeg
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Yes I (believe) I understood that, what I need is a test on the supplied lines of code as a simple vba sub to see what it returns ... assuning it is relevant data then I can turn it around to insert into the filter.

i.e. first find how to get the username.

Chris
sub testUserName
Dim sysInfo as object
        set sysinfo = createobject("adsysteminfo")
        msgbox sysinfo.username
end sub

Open in new window

0
mbwjkAuthor Commented:
Hi Chris,

that's fine.

msgbox sysinfo.username returns the distinguishedName (LDAP notation, "CN=xxx;OU=xxx;DC=xxx").

Alternatively, the attached Code returns the windows user name (sAMAccountName).

Best regards!
Chris
Sub testUserName()

 Dim sysInfo As Object
 Dim objUser As Object

 Set sysInfo = CreateObject("adsysteminfo")
 Set objUser = GetObject("LDAP://" & sysInfo.username)
 MsgBox objUser.sAMAccountName

End Sub

Open in new window

0
Chris BottomleySoftware Quality Lead EngineerCommented:
If I understand correctly then the objUser.sAMAccountName is teh windows name you want in your code so one way is (hopefully) ...

Chris
sub YOURSUB
'...
"http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/Bearbeiter" = "'" & UserName & "'"
'...
end sub

function username() as string
Dim sysInfo As Object 
Dim objUser As Object 
 
    Set sysInfo = CreateObject("adsysteminfo") 
    Set objUser = GetObject("LDAP://" & sysInfo.username) 
    username = objUser.sAMAccountName 
 
end function

Open in new window

0
mbwjkAuthor Commented:
Hi Chris,

that's correct. Unfortunately I can't use any VBA code within

* Start Outlook 2003
* Menu View, Arrange By, Current View, Define view
* Select appropriate view for tasks folder
* Button "Modify..."
* Button "Filter"
* Tab "SQL"

I've only got this dialog where I can enter a DASL-Filter. There is no VBA editor available.

Any idea?

Best regards
Chris
0
Chris BottomleySoftware Quality Lead EngineerCommented:
I have used VBA, to create filters before so if VBA is acceptable then with a bit of work we should be able to combine the two?

Chris
0
mbwjkAuthor Commented:
Hi Chris,

great idea! I didn't think about this yet. The attached code works. Do you know how I can define a DASL filter expression for the newly created objNewView?

Best regards!
Chris
Sub Test()
    
    'Copies a view
    Dim objViews As Outlook.Views
    Dim objNewView As Outlook.View

    Set objViews = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Views
    Set objNewView = objViews("Nachrichten").Copy(Name:="Test", SaveOption:=olViewSaveOptionThisFolderEveryone)

End Sub

Open in new window

0
Chris BottomleySoftware Quality Lead EngineerCommented:
Okay I have plagiarised some code, (it's mine so no worries!).  I cannot promise it is error free as I have tested it with the from field .. your string was unrecognised by my english version.

See how you get on after pasting the following into a normal code module - I have included username so make sure you only have the one copy.  It ought to work off a VBA line as follows:

changeViewFilter application.session.getdefaultfolder(olfolderinbox), "Test", username

If all goes well it should create a view on teh inbox called test so rename as you wish in the call.

Chris
function username() as string 
Dim sysInfo As Object  
Dim objUser As Object  
  
    Set sysInfo = CreateObject("adsysteminfo")  
    Set objUser = GetObject("LDAP://" & sysInfo.username)  
    username = objUser.sAMAccountName  
  
end function
Sub changeViewFilter(fldr As MAPIFolder, strViewName As String, strUSer As String)
'changeViewFilter Application.Session.GetDefaultFolder(olFolderInbox), "VMS"
Const strFilterPrefix As String = "<filter>("
Const strFilterAffix As String = ")</filter>"
'Const strSchema As String = "&quot;urn:schemas:httpmail:fromname&quot; = '"
Const strSchema As String = "&quot;http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/Bearbeiter&quot; = '"
Dim vw As View
Dim str() As String

    'Find the filter and either clear current data or insert it without data
    Set Application.ActiveExplorer.CurrentFolder = Application.Session.GetDefaultFolder(olFolderDrafts)
    Set vw = establishView(fldr, strViewName)
    If findFilterString(vw.XML) Then
    'Need to clear out the filter content
        vw.XML = clearFilterBody(vw.XML)
        vw.Save
        'vw.Apply
    End If
    'Need to insert filter structure
    vw.XML = fillFilterBody(insertFilterBody(vw.XML), strSchema & strUSer & "'")
    vw.Save
    vw.Apply
    Set Application.ActiveExplorer.CurrentFolder = Application.Session.GetDefaultFolder(olFolderInbox)
    
End Sub

Function findFilterString(str As String) As Boolean
Dim regex As Object

    Set regex = CreateObject("vbscript.regexp")
    With regex
        .IgnoreCase = True
        .Global = True
        .Pattern = "<filter>\((.*)\)</filter>"
    End With
    findFilterString = regex.test(UCase(str))
    
End Function

Function clearFilterBody(str As String) As String
Dim regex As Object

    Set regex = CreateObject("vbscript.regexp")
    With regex
        .IgnoreCase = True
        .Pattern = "(.*)(<filter>\(.*\)</filter>\r\n\t)(.*)"
        clearFilterBody = .Replace(str, "$1$3")
    End With
    
End Function

Function insertFilterBody(str As String) As String
Dim regex As Object

    Set regex = CreateObject("vbscript.regexp")
    With regex
        .IgnoreCase = True
        .Pattern = "(.*<viewname>.*</viewname>)(.*)"
        insertFilterBody = .Replace(str, "$1\r\n\t<filter>()</filter>$2")
    End With
    
End Function

Function fillFilterBody(strMain As String, strInsert As String) As String
Dim regex As Object

    Set regex = CreateObject("vbscript.regexp")
    With regex
        .IgnoreCase = True
        .Pattern = "(.*<filter>\()(.*)(\)</filter>.*)"
        fillFilterBody = .Replace(strMain, "$1" & strInsert & "$3")
    End With
    
End Function

Function establishView(fldr As MAPIFolder, strViewName As String) As Object
' Note view name is case sensitive!
'?establishview(application.Session.GetDefaultFolder(olFolderInbox), "VMS").xml
Dim vw As View
    
    With fldr.Views
        .item("Messages").Apply
        On Error Resume Next
        Set vw = .item(strViewName)
        If vw Is Nothing Then
            Set vw = .Add(Name:=strViewName, _
                         ViewType:=olTableView, SaveOption:=olViewSaveOptionThisFolderEveryone)
' =================================================================
' May need to change folder from inbox elsewhere to instantiate the save
Set Application.ActiveExplorer.CurrentFolder = Application.Session.GetDefaultFolder(olFolderDrafts)
Set Application.ActiveExplorer.CurrentFolder = fldr
' =================================================================
            vw.Save
            vw.Apply
        End If
    End With
    Set establishView = vw
    
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbwjkAuthor Commented:
Hi  Chris,

thank you very much! This was a great help for us.

Best regards!
Chris
0
Chris BottomleySoftware Quality Lead EngineerCommented:
For my education and the value to others, (it's not a simple subject) ... can you spare the time to say if you had to make any corrections and if so what they were?

Chris
0
mbwjkAuthor Commented:
We have template view for a tasks folder (ticketing system), which is quite complex (view properties, filter). Each member of the help desk should only see those tasks, where "processor" equals to his (short) user name.

With your help we created a VBA function which copies the template view for each member of the help desk. After copying, the XML property of the view (where the filter expressions are stored) is modified, according to the processors user name.

Best regards!
Chris
Sub CopyViews()
        
    Dim objFolder As Outlook.MAPIFolder
    Dim objSourceView As Outlook.View
    Dim objView As View
    
       Set objFolder = GetFolder("Öffentliche Ordner\Favoriten\Aufgaben Anwenderbetreuung")
       CopyView objFolder.Views, "Smith, John", "sj"
       CopyView objFolder.Views, "Brown, Chris", "bc"
       CopyView objFolder.Views, "Miller, Arthur", "ma"
       '...
    
    End Sub
    
    Function CopyView(objViews As Views, UserName As String, UserNameShort As String)
    
    Dim objSourceView As View
    Dim objNewView As View
    
       'reference the default view
       Set objSourceView = objViews("Tasks Template")
       'delte it, if it already exists
       On Error Resume Next: objViews("Tasks " & UserName).Delete: On Error GoTo 0
       'create a task and name it with the full name of the person
       Set objNewView = objSourceView.Copy(Name:="Tasks " & UserName, SaveOption:=olViewSaveOptionThisFolderEveryone)
       'the template filter contains 'xxx' within the processors field, which now gets replaced by the short username
       objNewView.XML = Replace(objNewView.XML, "&apos;xxx&apos;", "&apos;" & UserNameShort & "&apos;")
       objNewView.Save
    
    End Function
    
    Function GetFolder(FolderPath)
      ' folder path needs to be something like
      '   "Public Folders\All Public Folders\Company\Sales"
      Dim aFolders
      Dim fldr
      Dim i
      Dim objNS
    
      On Error Resume Next
      strFolderPath = Replace(FolderPath, "/", "\")
      aFolders = Split(FolderPath, "\")
    
      'get the Outlook objects
      ' use intrinsic Application object in form script
      Set objNS = Application.GetNamespace("MAPI")
    
      'set the root folder
      Set fldr = objNS.Folders(aFolders(0))
    
      'loop through the array to get the subfolder
      'loop is skipped when there is only one element in the array
      For i = 1 To UBound(aFolders)
        Set fldr = fldr.Folders(aFolders(i))
        'check for errors
        If Err <> 0 Then Exit Function
      Next
      Set GetFolder = fldr
    
      ' dereference objects
      Set objNS = Nothing
    End Function

Open in new window

0
Chris BottomleySoftware Quality Lead EngineerCommented:
So tasks rather than mails but thank you for providing that it hopefully improves the quality of the PAQ for others especially since to avoid all the hassle of creating a view from scratch you are using a template to be copied and updated.  A valuable additional approach.

Chris
0
mbwjkAuthor Commented:
Thanks very much for your help!

Best regards
Chris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.