VBOffice

VBA Editor: First Steps

See how to open the VBA environment of Microsoft Outlook, and how to add macros to ThisOutlookSession.

Last modified: 2017/01/13 | Accessed: 334.950  | #5
◀ Previous article Next article ▶

Content

Category-Manager Category-Manager
With Category-Manager you can group your Outlook categories, share them with other users, filter a folder by category, automatically categorize new emails, and more. You can use the Addin even for IMAP.

The VBA editor is the development environment for your own macros. To open it press ALT+F11. Then press CTRL+R to open the project explorer, which lists all available code modules.

Where do I insert Macros?

At the beginning there's only the modul ThisOutlookSession, which you can see under Project1/Microsoft Outlook Objects in the project explorer. Open the module by a double click on it. You can paste all macros in to this module. That applies to all VBA Samples. (In the very few cases when a script belongs to another module, you'll be explicitly told so.)

Because you can put as many macros into one module as you like, you'll usually get along with just that one module. At one point it could become useful to group macros that belong together by different modules. However, from a technical point of view that's not necessary.

How do I start a Macro?

Actually, there's three ways to start a macro. Each one requires to follow some rules:

How it is called Where to insert the code? Rules for the declaration
Called manually, for instance by pressing ALT+F8, or by a command on the menu/ribbon Place the code in ThisOutlookSession or in a standard module The procedure must be public, arguments are not allowed.

Wrong:
Private Sub Sample(Argument)
End Sub
Correct:
Public Sub Sample()
End Sub
Started by the Rule Assistent as soon as an incoming email matches a certain criteria Place the code in ThisOutlookSession or in a standard module The procedure must be public, and one argument must be passed. If the rule is for emails, the argument must be of the MailItem type; for meeting requests it must be of the MeetingItem type.

Wrong:
Private Sub Sample()
End Sub
Correct:
Public Sub Sample(Object As MailItem)
  'This function can be called only for emails
End Sub
Or:
Public Sub Sample(Object As MeetingItem)
  'This function can be called only for meeting requests
End Sub
Called by an event, for instance opening an email, or clicking the Send button The code must be placed in the ThisOutlookSession module or in another class module (UserForms are class modules, too). See the details in the next chapter Events
Reporter Reporter
VBOffice Reporter is an easy to use tool for data analysis and reporting in Outlook. A single click, for instance, allows you to see the number of hours planned for meetings the next month.

Event Procedures

An event is, for instance, the click on the Send button (Send), or changing the selection in a folder view (SelectionChange). If you want Outlook to call your macro as soon as a certain event occurs, you must subscribe to that event. You do so by declaring a variable of the correct object type with the keyword WithEvents. The SelectionChange event belongs to the Explorer object (which is responsible for displaying a folder), so Explorer is the correct object type. The full declaration then looks like this one:

Private WithEvents MyExplorer As Explorer

Now it's easy, just let Outlook create the declaration of the procedure for you:

  • There are two dropdown boxes above the code window. Click the left one, and select your variable MyExplorer.
  • Now click the right one where you'll see all the events supported by the Explorer object. Choose SelectionChange.

Now Outlook declares the procedure for you, and you can write your code between the lines Sub... und End Sub.

The next step will be to initialize the variable. We'll do that in the Application_Startup procedure.

SAM SAM
Determine the "identity" of your emails. Set with SAM the sender and the folder folder for sent items with the help of rules.

Application_Startup

The event procedurs of the Application object are the only ones Outlook can call on its own. If anything needs to be initialized, that must be done in the Application_Startup procedure. This procedure must be located in ThisOutlookSession, Outlook wouldn't find it in any other module.

Again, simply let Outlook create the declaration for you. The only difference to the aforementioned sample (Explorer object) is that you don't need to declare a variable for the Application object as this one (and only this one) is already known to Outlook. So click on the left dropdown box, choose Application, then choose Startup from the other box.

In the previous section you've declared a variable to receive events of the Explorer object. We still have to store a reference on the Explorer to the variable. Write the following line into the Startup procedure:

Set MyExplorer = Application.ActiveExplorer

This will point the variable to the active Explorer at the startup of Outlook. From then on Outlook will be able to call your event procedure, for instance, as soon as the selection of Outlook items will be changed.

Initialize_Handler

Many samples, even in the VBA help file, mention a procedure called Initialize_Handler as a starting point. This is misleading because Outlook doesn't know that procedure and won't call it. If you want to use such a sample, either rename Initialize_Handler by Application_Startup, move the code from within Initialize_Handler to Application_Startup, or call Initialize_Handler from Application_Startup:

Sub Application_Startup()
  'call the next function:
  Initialize_Handler 
End Sub

Sub Initialize_Handler()
  'called by Application_Startup. now your code can be executed here
End Sub
ReplyAll ReplyAll
ReplyAll alerts you before unintentionally replying all, or if you are a confidential BCC recipient of the e-mail.

Copy Macros

For many needs there's a ready-to-use script that you just need to copy. However, what do you do if you want to use two different macros that both need the Application_Startup function? The problem is that the name of a function within one module must be unique, and declarations of variables outside of a function must be at the top of the code module. Hence you cannot simply paste the following two examples into the same module:

Private Inbox as MapiFolder

Sub Application_Startup()
  Set Inbox = Application.Session.GetDefaultFolder(olFolderInbox)
End Sub
Private Calendar as MapiFolder

Sub Application_Startup()
  Set Calendar = Application.Session.GetDefaultFolder(olFolderCalendar)
End Sub

To solve the issue move the second declaration of the out-of-procedure-variable up to the top, and move the code from the second Startup procedure into the first one:

Private Inbox as MapiFolder
Private Calendar as MapiFolder

Sub Application_Startup()
  Set Inbox = Application.Session.GetDefaultFolder(olFolderInbox)
  Set Calendar = Application.Session.GetDefaultFolder(olFolderCalendar)
End Sub
SAM SAM
Determine the "identity" of your emails. Set with SAM the sender and the folder folder for sent items with the help of rules.

Macros are not executed

You think you're done, the code most likely is placed in ThisOutlookSession, and there's no error when you click on Debug/Compile. Still Outlook doesn't start your code automatically. These are the likely causes:

  1. The program must be initialized
  2. The security settings do not allow the execution of VBA

Initialize Code

The sample created an event procedure for the Explorer. The variable MyExplorer is placed the head of the module, that is outside any procedure, and it will be set by running Application_Startup, that is when Outlook starts. If you edit the code after running the Startup procedure, the variable will be reset automatically to Nothing. For another init you need to start Outlook again, or call the Startup procedure manually. Do the latter by placing the cursor into the procedure, then press F5.

To make it easy let's say: Whenever your programm needs the Application_Startup procedure, you must run it after every change to the code.

Macro Security Settings

You'll find the settings here depending on the Outlook version:

  • Outlook 2007 and older: Tools/Trust Center/Macro Security
  • Outlook 2010 and higher: File/Options/Trust Center/Trust Center Settings/Macro Settings/

The four settings are ordered by descending security. In order to execute VBA you must select one of the lower three options. Without a certificate to sign the VBA project, choose either the third or the fourth setting.

Adjust Additional Settings

The default settings of the VBA editor make programming unnecessary difficult. In order to change the settings click on Tools/Options.

  • Require Variable Declaration: Check this one, then the instruction Option Explicit will automatically be added to the the very first row of each new code module. Unfortunately this doesn't apply to already existing modules like ThisOutlookSession. Thus add it manually at the top of the module yourself.
    With this setting a procedure won't be executed as long as you don't have all variables declared. The setting is useful even for experienced programmers as it will prevent you from errors that would be difficult to find.
  • Auto Syntax check: You don't want this setting be checked else you'd get a prompt everytime you try to move the cursor out of an erroneous line of code. You will get the notification anyway when you try to run the code. You can also start the syntax checking manually via Debug/Compile.
Category-Manager Category-Manager
With Category-Manager you can group your Outlook categories, share them with other users, filter a folder by category, automatically categorize new emails, and more. You can use the Addin even for IMAP.

Troubleshooting

If everything else is ok but the macro doesn't do what you expect, then most likely there's a logical error. This sample should print an 'ok' to the debug window if the subject of an email equals 'abc'; however, the macro prints 'error' instead, and you want to find out why. A simple approach is to walk through the code execution step by step and watch what is going on. First set a breakpoint at the beginning of the macro: Place the cursor onto the line of code, then press f9. The entire row will be highlighted red.

Troubleshooting

Breakpoint

Now run the macro. If it's an event that will be raised by Outlook, for instance, when a new email arrives, then send yourself an email so Outlook will run the macro. Otherwise you can usually run it by pressing f5. The code execution will halt automatically at the breakpoint. The row where the execution halts will be highlighted yellow.

Troubleshooting

Code execution halts at the breakpoint

Now you can walk through the code step by step by pressing f8. With the next step the macro compares the subject of the email with the value 'abc':

Troubleshooting

Check condition

Instead of printing the 'ok' the execution jumps to the Else command. Now you know the condition (If ... Then ...) is not achieved.

Troubleshooting

Condition not achieved

Move the cursor over the Subject property, which was checked with the condition. After a delay of about one second the value of the property will be displayed in a tooltip window:

Troubleshooting

Check the value of the property

You see the value is 'Abc' and not 'abc' as was expected. Now you know the error of your macro: You forgot that upper and lower cases matter when you use the = operator. If that should be ignored, the solution is simple: Because you usually don't know beforehand whether the subject will contain upper or lower cases, simply convert all the subject to lower cases by using the LCase function:

Troubleshooting

Fix the error

Reporter Reporter
VBOffice Reporter is an easy to use tool for data analysis and reporting in Outlook. A single click, for instance, allows you to see the number of hours planned for meetings the next month.
◀ Previous article ▲ Top Next article ▶