More Outlook Resource Sites

Microsoft Developer Network (MSDN)

FAQs and other general resources

share code 18-Dec-2018 14:18

Looking for help with Outlook programming projects — VSTO, add-ins, VBA, custom Outlook forms, etc.? You′ve come to the right place!

NEW! >> Subscribe to this site via RSS. For more RSS options, see the complete list of feeds on our main news page.

Code level: advanced    Code area: Outlook Form Design Printer Friendly Version
Title: Manage local items list for combo or list box
Description: If you want dynamic, per-user combo or list boxes on a custom form, you must provide a mechanism to store and load the list items. This sample uses a local text file for each combo or list box control. Each control needs a SetComboListFromFile statement in the Item_Open event handler and a WriteMRUToFile statement in the Item_Write event handler. (The application was originally written to handle an MRU -- most recently used list.) What is not shown is where the list items for the combo or list box originate. This will be specific to each form application -- maybe user typed text, selections from some other source, etc.
Date: 21-Jun-2004  01:48
Code level: advanced
Code area: Outlook Form Design
Posted by: Sue Mosher
This message is displayed as VB.NET
 Option Explicit
Dim mstrAppPath
Dim mfso

Function Item_Open()
    Dim objInsp
    Dim cmb
    Dim colControls
    On Error Resume Next
    
    ' full path to folder without trailing slash
    mstrAppPath = "C:\Program Files\MyApp"
    ' for new item, read data from MRU list into combo boxes
    Call InitAppPath
    
    Set objInsp = Item.GetInspector
    Set colControls = _
    objInsp.ModifiedFormPages("Customer Visit").Controls
    
    Set cmb = colControls("cmbCustCompanyName")
    If Err = 0 Then
        Call SetComboListFromFile("CustCompanyName.txt", cmb)
    Else
        Err.Clear
    End If
    
    Set cmb = Nothing
    Set colControls = Nothing
End Function

Function Item_Write()
    Dim objInsp
    Dim colControls
    Dim cmb
    On Error Resume Next
    
    Set objInsp = Item.GetInspector
    Set colControls = _
    objInsp.ModifiedFormPages("Customer Visit").Controls
    
    Set cmb = colControls("cmbCustCompanyName")
    If Err = 0 Then
        Call WriteMRUToFile("CustCompanyName.txt", cmb)
    Else
        Err.Clear
    End If
    
    Set cmb = Nothing
    Set colControls = Nothing
    Set objInsp = Nothing
End Function


' ******************************************************
' Routines for MRU combo boxes
' ******************************************************

Sub InitAppPath()
    Dim lngLoc
    Dim strParentPath
    Dim strFolderName
    ' mstrAppPath set in Item_Open
    
    ' create new folder if necessary; code handles just
    ' two levels of folders
    Set mfso = CreateObject("Scripting.FileSystemObject")
    If Not mfso.FolderExists(mstrAppPath) Then
        lngLoc = InStrRev(mstrAppPath, "\")
        strParentPath = Left(mstrAppPath, lngLoc - 1)
        strFolderName = Mid(mstrAppPath, lngLoc + 1)
        If Not mfso.FolderExists(strParentPath) Then
            mfso.CreateFolder strParentPath
        End If
        mfso.CreateFolder mstrAppPath
    End If
    
    ' add trailing slash
    mstrAppPath = mstrAppPath & "\"

End Sub


' read data from text file into combo box or list box
Sub SetComboListFromFile(strFileName, ctlList)
    Dim strFilePath, strText
    Dim f
    Dim ts
    Dim arr
    
    strFilePath = mstrAppPath & strFileName
    If mfso.FileExists(strFilePath) Then
        Set f = mfso.GetFile(strFilePath)
        Set ts = f.OpenAsTextStream
        If Not ts.AtEndOfStream Then
            strText = ts.ReadAll
            If strText <> "" Then
                arr = Split(strText, vbCrLf)
                ctlList.List = arr
            End If
        End If
    Else
        Set f = mfso.CreateTextFile(strFilePath)
        f.Close
    End If
    Set ts = Nothing
    Set f = Nothing
End Sub

' add new entry to combo box MRU list
Sub WriteMRUToFile(strFileName, ctlCombo)
    Dim arr ' array of combo box entries
    Dim strFilePath, strText
    Dim f
    Dim i
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    On Error Resume Next
    
    ' see if we need to write data
    If ctlCombo.Value <> "" And Not ctlCombo.MatchFound Then
        ' add item, get array, and sort
        ctlCombo.AddItem ctlCombo.Value
        arr = ctlCombo.List
        QSort arr, LBound(arr), UBound(arr)
        For i = 0 To UBound(arr)
            strText = strText & vbCrLf & arr(i, 0)
        Next
        strText = Mid(strText, 3)
        
        ' get file to write to
        strFilePath = mstrAppPath & strFileName
        Set f = mfso.OpenTextFile(strFilePath, ForWriting, True)
        f.Write strText
        f.Close
    End If
    
    Set f = Nothing
End Sub


' recursive quick sort routine for combo box list
Sub QSort(aData, iaDataMin, iaDataMax)
    Dim Temp
    Dim Buffer
    Dim iaDataFirst
    Dim iaDataLast
    Dim iaDataMid
    
    iaDataFirst = iaDataMin
    iaDataLast = iaDataMax
    
    If iaDataMax <= iaDataMin Then Exit Sub
    iaDataMid = (iaDataMin + iaDataMax) \ 2
    
    Temp = aData(iaDataMid, 0)
    Do While (iaDataFirst <= iaDataLast)
        'Comparison here
        Do While aData(iaDataFirst, 0) < Temp
            iaDataFirst = iaDataFirst + 1
            If iaDataFirst = iaDataMax Then Exit Do
        Loop
        
        'Comparison here
        Do While Temp < aData(iaDataLast, 0)
            iaDataLast = iaDataLast - 1
            If iaDataLast = iaDataMin Then Exit Do
        Loop
        
        If (iaDataFirst <= iaDataLast) Then
            Buffer = aData(iaDataFirst, 0)
            aData(iaDataFirst, 0) = aData(iaDataLast, 0)
            aData(iaDataLast, 0) = Buffer
            iaDataFirst = iaDataFirst + 1
            iaDataLast = iaDataLast - 1
        End If
    Loop
    
    If iaDataMin < iaDataLast Then
        QSort aData, iaDataMin, iaDataLast
    End If
    
    If iaDataFirst < iaDataMax Then
        QSort aData, iaDataFirst, iaDataMax
    End If
End Sub

All 22comments
Page [ 1 2 3 Next >>  
  19-Jul-2004  13:52   
Could I implement this on the email form, if so how..
  20-Jul-2004  10:37   
Copy and paste the code into your form's script, then update it to use the desired folder path and the correct page and control names. As explained above, each Each control needs a SetComboListFromFile statement in the Item_Open event handler and a WriteMRUToFile statement in the Item_Write event handler.
  30-Aug-2004  22:47   
Will this one off the form? Is it possible to implement this type of functionality from a COM add-in without one-offing the form?
  31-Aug-2004  09:43   
No, this technique does not one-off the form; it does not use the control.PossibleValues property. You could implement similar functionality with a COM add-in but with much more difficulty. While it's easy to detect an item opening with the Inspectors.NewInspector event, you would also need a wrapper class for all the items of interest to handle their Write event; see http://www.outlookcode.com/d/vb.htm#wrapper
  10-Oct-2006  15:19   
Using the control.PossibleValues property can one off a form? it doesnt seem to be one offing my form...

i hope this doesnt always cause a problem, becuase i have been using it on my form and it is an integral part of it.
  10-Oct-2006  15:28   
Yes, setting control.PossibleValues in code is one of the known causes of one-offing. I would never use it in runtime code, only in say, VBA code, that runs at design time to automate the form creation process.
  06-Nov-2006  18:30   
I am the combo box values to the file and I want to make sure the values are unique. I have added the following code for error checking but it is not working.


' add new entry to combo box MRU list
Function WriteMRUToFile(strFileName, ctlCombo)
    Dim arr ' array of combo box entries
    Dim strFilePath, strText
    Dim f
    Dim i
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    On Error Resume Next
    
   If ctlCombo.Match then
       gblError = True
     Msgbox "The Document ID already exists.", vbCritical, "Duplicate Document ID"
       Exit Function
   Else
     gblError = False
   End if

     If ctlCombo.Value <> "" And Not ctlCombo.MatchFound Then
        ' add item, get array, and sort
        ctlCombo.AddItem ctlCombo.Value
        arr = ctlCombo.List
        QSort arr, LBound(arr), UBound(arr)
        For i = 0 To UBound(arr)
            strText = strText & vbCrLf & arr(i, 0)
        Next
        strText = Mid(strText, 3)
        
        ' get file to write to
        strFilePath = mstrAppPath & strFileName
        Set f = mfso.OpenTextFile(strFilePath, ForWriting, True)
        f.Write strText
        f.Close
    End If
    
    Set f = Nothing
End Function

Please help!
 
  07-Dec-2006  21:40   
What specifically is not working?
  21-Mar-2007  03:27   
Hi Sue,

I wrote the following code:
Public Function SynergyRelaties() As String
  Dim oConnection As ADODB.Connection
  Dim oRecordset As ADODB.Recordset
  Dim sConnect As String
  Dim sQRelatie As String
  Dim sRelaties As String

  sConnect = "DSN=Synergy demo;OLE DB Services=-2;"
  sQRelatie = "SELECT cmp_name FROM cicmpy ORDER BY cmp_name"
  Set oConnection = New ADODB.Connection
  Set oRecordset = New ADODB.Recordset

  oConnection.Open (sConnect)
  oRecordset.Open sQRelatie, oConnection, adOpenStatic, adLockOptimistic
  Do While (Not oRecordset.EOF)
    sRelaties = sRelaties & oRecordset.Fields("cmp_name") & ";"
    oRecordset.MoveNext
  Loop
  SynergyRelaties = sRelaties
  oRecordset.Close
  Set oRecordset = Nothing
  oConnection.Close
  Set oConnection = Nothing
End Function

This code builds a comma separated string which I wanna show in a combobox on a custom made Appointment form in Outlook. I have this code:
Set FormPage = Item.GetInspector.ModifiedFormPages("Synergy data")
Set Control = FormPage.Controls("cmbRelatie")
Control.PossibleValues = sSelRelatie

How do I get the result of the function "SynergyRelaties" available in the code behind the form? Therefore sRelaties should end up in sSelRelatie. I can't 'Call' this function. I started the code on startup (that's not exactly what I want) and stored the result in a public variable but even that didn't help.
  21-Mar-2007  03:56   
I already worked it out. Now I need to show the earlier made selection on re-opening.
Page [ 1 2 3 Next >>