Creating and Using Userforms in VBA

VBA code might sometimes not be enough to perform the operations you need within Outlook. If you need to gather information before running your code, or just want to use Outlook to perform advanced operations with email messages or other Outlook items, you can create userforms directly in VBA. These forms are very different from custom Outlook forms. Userforms are very similar to Visual Basic forms. They can have code that runs when the form loads or in response to an event such as a button click or data entered in the form.

Add a new userform to your project by right-clicking anywhere in the Project Explorer and selecting Insert, UserForm. A new userform is inserted in your project and the control toolbox is displayed, shown in Figure 33.7.

Figure 33.7. A new userform has no controls or code when it's added to your project.

TIP

The first thing you should always do with a new userform is to give it a meaningful name. All new userforms are created with a generic name, such as UserForm1 or UserForm2. To rename a userform, right-click the form and choose Properties. This displays the Properties dialog box. Click in the (Name) field and enter a new name for your userform. For our example, name your userform frmCompleteTasks.

You can add any number of different controls to your form. One way to use a VBA form within Outlook is to accomplish two simultaneous related operations. For example, if you track all of your tasks and create journal entries when a task is completed, you can complete a task and create a journal entry automatically through the use of a VBA form. You can add a drop-down list to your userform and populate it with a listing of all tasks with a status of Not Started or In Progress. Then you can add a command button to mark the selected task complete and create an automatic journal entry in your Journal folder.

To accomplish this task, add a combo box, label, and command button to your form. Userforms do not have a Load event, so use the Activate event to populate your combo box (cboTasks) with a list of all the active tasks in your Tasks folder using the following code. You can place the call to the following procedure in the Activate event of your form.

 Sub LoadTasks() Dim olNS As Outlook.NameSpace Dim olFolder As Outlook.MAPIFolder Dim olItems As Outlook.Items Dim olItem As Outlook.TaskItem Set olNS = Application.GetNamespace("MAPI") Set olFolder = olNS.GetDefaultFolder(olFolderTasks) Set olItems = olFolder.Items For Each olItem In olItems         If olItem.Status = olTaskNotStarted Or olTaskInProgress Then             Me.cboTasks.AddItem olItem.Subject         End If Next End Sub 

Next, create a procedure in a module to load the VBA form. Use the following code in that module:

 Sub CompleteTasks() frmCompleteTasks.Show End Sub 

This code loads the form and runs the previously detailed code to populate the combo box. The only other piece of missing code is the code behind the command button to complete the task and create the Journal entry. Put the following code in the Click event of the command button to complete your project:

 Private Sub cmdComplete_Click() Dim olNS As Outlook.NameSpace Dim olTaskFolder As Outlook.MAPIFolder Dim olItems As Outlook.Items Dim objTaskItem As Outlook.TaskItem Dim objJournalItem As Outlook.JournalItem Set olNS = Application.GetNamespace("MAPI") Set olTaskFolder = olNS.GetDefaultFolder(olFolderTasks) If Me.cboTasks = "" Then     MsgBox "Please select a task to continue", vbOKOnly, "No Task Selected"     Exit Sub Else     Set olItems = olTaskFolder.Items         Set objTaskItem = olItems.Find("[Subject] = '" & cboTasks.Text & "'")             With objTaskItem                 .Status = olTaskComplete                 .Close (olSave)             End With          'Create Journal Entry     Set objJournalItem = Application.CreateItem(olJournalItem)     objJournalItem.Subject = cboTasks     objJournalItem.Type = "Task"     objJournalItem.Body = "Task: " & cboTasks & " completed on " & Now()     objJournalItem.Close (olSave)          Set objJournalItem = Nothing     Set objTaskItem = Nothing     Set olItems = Nothing     Set olTaskFolder = Nothing     Set olNS = Nothing         End If End Sub 

When you click the command button, Outlook marks the task as complete and creates a new journal entry recording the date and time the task was completed.