This code sample demonstrates how to use the contents of a text file to populate a ListBox. This method can also be used with ComboBoxes and TextBoxes.
The scenario: A client adds keywords to message subjects so it's easier to find the messages later but if he mistypes a keyword he can't find the message. My solution is a macro with a list of keywords to choose from. He selects one or more keywords then clicks OK to add the keyword(s) to the subject.
- The macro works with either open or selected messages.
- If you are using selected messages, the keyword is saved to the subject when you select a different message.
Step 1: Create a test file with the list of keywords, using one keyword per line.
Step 2: Open the VBA Editor using Alt+F11. Right click on Project1 and choose Insert > UserForm to add a UserForm to the VBA project. If the Toolbox isn't visible, go to the View menu and select Toolbox. Drag the edges to resize as needed. Change the Caption in Properties.
Add a Listbox to the form and drag to resize it. In the Properties screen, find MultiSelect property and change it to allow multiple selections, otherwise you'll only be able to select one entry at a time.
Drag the CommandButton control to the Userform. Change the Caption to Ok in Properties.
If you want to open a selected message to type keywords (eg, to add project numbers) add a Checkbox control and change the Caption.
Step 3: Right click on the UserForm, choose View Code then paste the following macro into the code editor. This code assumes the UserForm is named UserForm1, and the control names end with a 1.
Don't forget to update the path to the text file!
Private Sub CheckBox1_Click() If CheckBox1 Then OpenEdit = "Edit" End If End Sub Private Sub ListBox1_Click() End Sub Private Sub UserForm_Initialize() Dim fn As String, ff As Integer, txt As String fn = "C:\Users\drcp\Documents\keywords.txt" '< --- .txt file path txt = Space(FileLen(fn)) ff = FreeFile Open fn For Binary As #ff Get #ff, , txt Close #ff Dim myArray() As String 'Use Split function to return a zero based one dimensional array. myArray = Split(txt, vbCrLf) 'Use .List method to populate listbox. ListBox1.List = myArray lbl_Exit: Exit Sub End Sub Private Sub CommandButton1_Click() Dim i As Long Dim lngCount As Long Dim strPicks As String lngCount = 0 For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then lngCount = lngCount + 1 If lngCount = 1 Then strPicks = ListBox1.List(i) Else strPicks = strPicks & " " & ListBox1.List(i) End If End If Next i lstText = strPicks Unload Me lbl_Exit: Exit Sub End Sub
Step 4: Right click on Project1 and choose Insert > Module. Paste the following code in the Module. If desired, you can insert another module and name it Functions then move the function to the Functions module. (Functions can be used by more than one macro.)
Public lstText As String Public OpenEdit As String Public MType As String Public Sub SetSubject() Dim oMail As Outlook.MailItem OpenEdit = "" UserForm1.Show Set oMail = GetCurrentItem() oMail.Subject = oMail.Subject & " " & lstText If MType = "Inspector" Then oMail.Save If OpenEdit = "Edit" Then If MType = "Explorer" Then oMail.Display End If Set oMail = Nothing End Sub Function GetCurrentItem() As Object Dim objApp As Outlook.Application Set objApp = Application On Error Resume Next Select Case TypeName(objApp.ActiveWindow) Case "Explorer" Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1) MType = "Explorer" Case "Inspector" Set GetCurrentItem = objApp.ActiveInspector.CurrentItem MType = "Inspector" End Select Set objApp = Nothing End Function
Step 5: Create buttons on the ribbon or Quick Access Toolbar in both Outlook's main window and an open message for the SetSubject macro.
- Right-click on the ribbon, choose Customize Ribbon.
- Add a New Group on the right side.
- Select Macros on the left side and adds the SetSubject macro to the new group.
How to use macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor