Organizing email messages by subject
Listing the next week's appointments
In the previous two chapters, you learned a lot of details about the VBA programming language and the Outlook Object Model. This information is essential, but it can also be a terrific learning aid to see these concepts put into use in a real-world situation. To this end, this chapter presents two complete and tested Outlook VBA applications that perform tasks that Outlook users really might want to perform. You can use these projects as-is, modify them to suit your specific needs, or simply use them as learning aids.
You had an introduction to this project in Chapter 24. This chapter goes into a lot more detail and presents a complete, working application.
At the heart of this application is a VBA function called MoveMessagesBySubject(). It takes two arguments:
The text that you are searching for
The name of the destination folder
The function's return value is type Boolean. It returns True if the code completes successfully and False if there was a problem. The most likely problem that might occur is that the destination folder cannot be found. However, the function includes error handling code to deal with unforeseen errors.
The function MoveMessagesBySubject() is presented in Listing 25.1. Note that this function calls the function FindFolder(), which was presented in Chapter 24. In other words, FindFolder() must be in the same module as MoveMessagesBySubject() or you will get an error message.
Example 25.1. The MoveMessagesBySubject() Function
Public Function MoveMessagesBySubject(Subject As String, _
DestinationFolder As String) As Boolean
' Moves all messages from the Inbox to the specified folder
' if the message subject contains the text in the Subject
' argument.
' Returns True on success, False on error.
' Requires access to the function FindFolder() to run.
Dim fInbox As Outlook.Folder
Dim fDestination As Outlook.Folder
Dim m As Outlook.MailItem
Dim MyOutlookNamespace As Outlook.NameSpace
Dim FoldersToMove As New Collection
'Obtain the required MAPI namespace.
Set MyOutlookNamespace = GetNamespace("MAPI")
' Set up error handling.
On Error GoTo ErrorHandler
' Get the references to the Inbox and destination folders.
Set fInbox = MyOutlookNamespace.GetDefaultFolder(olFolderInbox)
Set fDestination = FindFolder(DestinationFolder)
' If destination folder not found, display
' message and exit.
If fDestination Is Nothing Then
MsgBox ("The destination folder could not be found.")
MoveMessagesBySubject = False
Exit Function
End If
' Now loop through the Inbox looking at each mesasage.
For Each m In fInbox.Items
If InStr(m.Subject, Subject) > 0 Then
' Add message to the "to be moved" collection.
FoldersToMove.Add m
End IfNext
' If any matching messages were found, move them.
If FoldersToMove.Count > 0 Then
For Each m In FoldersToMove
m.Move fDestination
Next
Else
MsgBox "There are no messages to move."
End If
MoveMessagesBySubject = True
ErrorExit:
Exit Function
ErrorHandler:
MoveMessagesBySubject = False
Resume ErrorExit
End FunctionYou should note two things about this function. First, it uses the function FindFolder() to obtain a reference to the destination folder. This function was presented in Chapter 24. It must be available in your Outlook project.
Second, you can see how the code uses a collection to move folders. As mentioned in Chapter 24, it can cause problems if you move an item out of the Inbox (or any other folder) while the For Each...Next loop is still looping through the folder. Instead, you should keep track of which items are to be moved, and the Collection object is ideal for this purpose. Then, after the For Each...Next loop has completed, you can move the items by going through the collection.
As written, the function moves any message in which the message subject contains the specified text. This is done using the Instr() function, which compares two strings:
Instr(String1, String2)
If String2 is found anywhere within String1, the function returns a value greater than 0 (in fact, it returns the position at which String2 is found). Otherwise it returns 0. You could also move messages only if the message subject exactly matches the specified text by changing that line of code to:
If StrComp(m.Subject, Subject, vbTextCompare) = 0
The StrComp function compares two strings and returns:
0 if the two strings are the same.
−1 if the first string is less than (before alphabetically) the second string.
1 if the first string is more than (after alphabetically) the second string.
The vbTextCompare argument tells StrComp() to ignore the case of letters. Other options are explained in the VBA documentation.
There are lots of other criteria you can use to move or process messages, such as receipt date, sender name, whether the message has been read, and so on. Table 24.3 described the various message properties that may be useful when processing messages.
There's one more element needed to complete this application. You cannot run MoveMessagesBySubject() directly. You need a macro that can be run from the Macros dialog box and also lets the user enter the required information—subject text to search for and destination folder name. This is accomplished by the macro MoveFolder(), shown in Listing 25.2.
Example 25.2. The MoveMessages () Macro
Public Sub MoveMessages()
Dim Subject As String, DestinationFolder As String
Dim result As Boolean
Do
Subject = InputBox("Enter the subject text to look for", _
"Move Folders By Subject")
Loop Until Len(Subject) > 0
Do
DestinationFolder = InputBox("Enter the name of the destination folder", _
"Move Folders By Subject")
Loop Until Len(DestinationFolder) > 0
result = MoveMessagesBySubject(Subject, DestinationFolder)
If result Then
MsgBox "Messages moved successfully"
Else
MsgBox "An unknown error occurred"
End If
End SubThis macro uses the InputBox statement to prompt the user for the text to search for and the name of the destination folder. Note that the two InputBox statements are enclosed in Do...Until loops. This is for data validation purposes—to guard against the possibility that the user accidentally enters a blank string. The Len() function returns the length of a string (number of characters it contains), and the loops continue prompting the user until a non-empty string is entered.
The steps required to add this code to your Outlook installation are simple:
In the Project Explorer, double-click Module1 to open it.
If there is any code in the module, move the editing cursor to the end of the module.
Copy the code for the macro
MoveMessages()and the proceduresFindFolder()andMoveMessagesBySubject()and paste them into the module.Click the Save button on the VBA Editor's toolbar.
After you perform these steps, the macro MoveMessages will be listed in the Macros dialog box from where you can run it (see Figure 25.1).
The demonstration that is presented here serves two purposes. First, it presents a useful example of using VBA to work with your calendar items. Second, it shows how to work with Outlook Notes programmatically. In addition, it shows you some of the things you can do with dates in VBA.
This application is a macro—that is, it is a Sub procedure with no arguments. I have named it ListAppointmentsThisWeek. Its operation proceeds as follows:
Get a reference to the default Calendar folder (as was described in Chapter 24).
Create a date that is one week from the present.
Create the note heading (the first line of the note) that consists of the text
Week offollowed by today's date. You'll recall from Chapter 13 that the first line of a note is automatically used as its subject.Loop through all appointments in the folder using a
For Each...Nextloop.Examine each appointment's start date—it must be greater than or equal to today's date and less than or equal to the date you created one week hence.
If the appointment meets these criteria, extract the required information from it.
When all appointments have been processed, create and display the note.
The technique used here to compile all the information from all matching appointments is to create a string variable and add each additional bit of information to the end of the string. Note the use of the constant vbCrLf, which is the newline character—it moves everything that follows to the next line.
Be aware that this macro will find appointments only in the default Calendar folder. If you have created additional calendar folders, and want the macro to look in them, you will have to modify the code to get a reference to these additional folders and process the appointments they contain as well. You saw how to get a reference to a user-created folder in Chapter 24.
Figure 25.2 shows a note created by this macro. The code for the macro is presented in Listing 25.3.
Figure 25.2. The macro ListAppointmentsThisWeek creates a note containing all your appointments for the next week.
Example 25.3. The ListAppointmentsThisWeek Macro
Public Sub ListAppointmentsThisWeek() ' Creates a Note containing a list of all appointments ' for the coming week. Dim MyCalendar As Outlook.MAPIFolder Dim MyAppt As Outlook.AppointmentItem
Dim MyOutlookNS As Outlook.NameSpace
Dim temp As String
Dim OneWeekHence As Date
Dim doc As Outlook.NoteItem
' Get a reference to the calendar folder.
Set MyOutlookNS = GetNamespace("MAPI")
Set MyCalendar = MyOutlookNS.GetDefaultFolder(olFolderCalendar)
' Create a date a week from now.
OneWeekHence = DateAdd("d", 7, Date)
' Create the Note heading
temp = "Week of" & Date & vbCrLf & vbCrLf
' Loop for each appointment in the folder.
For Each MyAppt In MyCalendar.Items
' Find appointments within the next week.
If MyAppt.Start >= Date And _
MyAppt.Start <= OneWeekHence Then
' The info is put together in temp.
temp = temp & MyAppt.Subject & vbCrLf
temp = temp & " Date: " & _
Format(MyAppt.Start, "Medium Date") & vbCrLf
temp = temp & " When: " & Format(MyAppt.Start, "Medium Time") _
& vbCrLf
temp = temp & " Ends: " & Format(MyAppt.End, "Medium Time") _
& vbCrLf
temp = temp & " Where: " & MyAppt.Location & vbCrLf & vbCrLf
End If
Next
' Create a new note.
Set doc = CreateItem(olNoteItem)
' Add the compiled text to the Note.
doc.Body = temp
' Display the note.
doc.Display
End SubMacros and VBA programming let you automate commonly performed tasks in Outlook. Although it takes some time and effort to create the macros, you'll often find that this investment is generously repaid in saved time and reduced errors. This chapter presented two real-world examples of VBA code that performs useful, real-world tasks. These applications can serve as the basis for your own projects.