VBOffice

VBA Editor: First Steps

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

Last modified: 2015/02/16 | Accessed: 74.998  | #5
◀ Previous article Next article ▶

Content

Category-Manager Category-Manager
Access the master category list in the blink of an eye, share your color categories in a network, get a reminder service, and more.

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, signature and 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, signature and 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
Access the master category list in the blink of an eye, share your color categories in a network, get a reminder service, and more.
◀ Previous article ▲ Top Next article ▶