Home Tutorials Training Consulting Books Company Contact us


Get more...

Microsoft Outlook Macros. This article describes how to create macros in Visual Basic for the email client Microsoft Outlook.

1. Overview

Microsoft Outlook allow the user to define macros written in Visual Basis to automate certain activities. To create a macro select Tools  Macro  Macros

create10

Type in the name of your desired macro and press "Create".

create20

You can now write your macro.

create30
Sub Hello_Outlook()
    MsgBox ("Hello Outlook")
End Sub

To run your macro, open Tools  Macro  Macros, select your macro and press Run.

2. Outlook Macros

2.1. Create automatically greetings for email receiver

The following macro describes how you can extract information from the email and create a corresponding message in your email text. For example if you are writing to Jim.Test@example.com you can create a text in your email "Hi Jim,…​.Best regards, Lars" and place the cursor in the right place to start typing.

The following macro reads all email addresses in the To part of the email. Then is extracts the part before the first "." assumes that this is the first name and writes Hello firstname1, firstname2,…​

It also puts in "Best regards, Lars" and places the cursor on the right position to reply. The macro allows also to set a defined delay in sending out the email.

Sub Generic(greeting, byebye, myname, includeCC, category, delay, text)

    Dim olApp As Outlook.Application

    Set olApp = Outlook.Application

    If TypeName(Application.ActiveInspector.CurrentItem) = "MailItem" Then
        Call mailreply(greeting, byebye, myname, includeCC, category, delay, text)
    End If

End Sub


Sub mailreply(greeting, byebye, myname, includeCC, category, delay, text)

    Dim objMail As Outlook.MailItem

    Set objMail = Application.ActiveInspector.CurrentItem

    With objMail
       'Set body format to HTML
       Dim arry As String
       Dim help As String

       Dim email, vorname As Variant


       Dim cced As Variant
       Dim anrede As Variant
       Dim size As Integer

       cced = Split(.CC, ";")


       email = Split(.To, ";")
       Dim Anzahl As Integer

       ' Now get the names of the persons which are in the email

       Anzahl = UBound(email)

        first = True
        For I = 0 To Anzahl

            'If Not email(i) = "Vogel, Lars;" Then

             vorname = Split(email(I), ",")

           'If size(vorname()) <> 0 Then

            If UBound(vorname) > 0 Then
            If first Then
            anrede = vorname(1)
            first = False
            Else
            anrede = anrede & " / " & vorname(1)
        End If

        End If
       ' End If



       Next I

       If includeCC = "1" Then

       ' Now get the names of the persons which are cc'ed

       Anzahl = UBound(cced)

        first = True
       For I = 0 To Anzahl
       If Not email(I) = "Vogel, Lars" Then
        vorname = Split(cced(I), ",")

        If UBound(vorname) > 0 Then
        If first Then
        anrede = anrede & " ," & vbNewLine & "cc:" & vorname(1)
        first = False

         Else
        anrede = anrede & " / " & vorname(1)
        End If

        End If
        End If

       Next I

        Else
             anrede = anrede & " ,"
        End If

       .Body = greeting & anrede & vbNewLine & vbNewLine & text & vbNewLine & vbNewLine & byebye & myname & vbNewLine & vbNewLine & "---------------------------------------" & vbNewLine & .Body

       .Display


    End With
    objMail.Categories = category

    'End If
    SendKeys "{DOWN}"
    SendKeys "{DOWN}"

End Sub

Sub Reply_Text_English_Urgent()
    Call Generic("Hi", "Best regards, ", "Lars", 0, "Business", False, "")
End Sub

2.2. Macro for moving email to specified folder

I gave up on sorting manually my email a long time ago. Now I move all my email to a quarterly folder. I search mails is done via a desktop search engine, e.g. Google desktop search.

The following macro will move one or more selected email to a specified folder. This folder must exists.

Sub MoveSelectedMessagesToToDo()

On Error Resume Next
    Dim objFolder As Outlook.MAPIFolder, objInbox As Outlook.MAPIFolder

    Dim objNS As Outlook.NameSpace, objItem As Outlook.MailItem

    Set objNS = Application.GetNamespace("MAPI")

    Set objInbox = objNS.GetDefaultFolder(olFolderInbox)

   ' MUST CHANGE THE OUTPUT FOLDER
   ' Assume this is a mail folder
    Set objFolder = GetFolder("10_Offline\_00_to_do")
    ' In case you would like to move to a subfolder in the inbox
    'Set objFolder = objInbox.Folders.Item("Done")


    If objFolder Is Nothing Then
        MsgBox "This folder doesn't exist!", vbOKOnly + vbExclamation, "INVALID FOLDER"
    End If

    If Application.ActiveExplorer.Selection.Count = 0 Then
        'Require that this procedure be called only when a message is selected
        Exit Sub
    End If


    For Each objItem In Application.ActiveExplorer.Selection
        If objFolder.DefaultItemType = olMailItem Then
            If objItem.Class = olMail Then
                objItem.Move objFolder
            End If
        End If
    Next

    Set objItem = Nothing
    Set objFolder = Nothing
    Set objInbox = Nothing
    Set objNS = Nothing

End Sub



Sub MoveSelectedMessagesToFolder()

On Error Resume Next
    Dim objFolder As Outlook.MAPIFolder, objInbox As Outlook.MAPIFolder

    Dim objNS As Outlook.NameSpace, objItem As Outlook.MailItem

    Set objNS = Application.GetNamespace("MAPI")

    Set objInbox = objNS.GetDefaultFolder(olFolderInbox)

   ' MUST CHANGE THE OUTPUT FOLDER
   ' Assume this is a mail folder
    Set objFolder = GetFolder("2009\Q4")



    If objFolder Is Nothing Then
        MsgBox "This folder doesn't exist!", vbOKOnly + vbExclamation, "INVALID FOLDER"
    End If

    If Application.ActiveExplorer.Selection.Count = 0 Then
         MsgBox "Nothing selected", vbOKOnly + vbExclamation, "No message selected"
        Exit Sub
    End If


    For Each objItem In Application.ActiveExplorer.Selection
        If objFolder.DefaultItemType = olMailItem Then
            If objItem.Class = olMail Then
                objItem.Move objFolder
            End If
        End If
    Next

    Set objItem = Nothing
    Set objFolder = Nothing
    Set objInbox = Nothing
    Set objNS = Nothing

End Sub


Public Function GetFolder(strFolderPath As String) As MAPIFolder
  ' folder path needs to be something like
  '   "Public Folders\All Public Folders\Company\Sales"
  Dim objApp As Outlook.Application
  Dim objNS As Outlook.NameSpace
  Dim colFolders As Outlook.Folders
  Dim objFolder As Outlook.MAPIFolder
  Dim arrFolders() As String
  Dim I As Long
  On Error Resume Next

  strFolderPath = Replace(strFolderPath, "/", "\")
  arrFolders() = Split(strFolderPath, "\")
  Set objApp = CreateObject("Outlook.Application")
  Set objNS = objApp.GetNamespace("MAPI")
  Set objFolder = objNS.Folders.Item(arrFolders(0))
  If Not objFolder Is Nothing Then
    For I = 1 To UBound(arrFolders)
      Set colFolders = objFolder.Folders
      Set objFolder = Nothing
      Set objFolder = colFolders.Item(arrFolders(I))
      If objFolder Is Nothing Then
        Exit For
      End If
    Next
  End If

  Set GetFolder = objFolder
  Set colFolders = Nothing
  Set objNS = Nothing
  Set objApp = Nothing
End Function

3. Define shortcuts for your macros

To define a shortcut for your macro, select View  Toolbars  Customize and select Macros.

button10

Drag the macro into your toolbar. Right-click it to change its properties, e.g. the name. Use & number to assign a hotkey to your macro. For example the following sets the hotkey for the Move macro to Alt+1.

button20

4. Outlook

4.1. Reset the views

If I search in outlook sometimes I loose the Day / Month / Year view in the calendar of outlook. In this case restarting Output with the cleanviews switch helps. Start  Run  outlook /cleanviews.

4.2. Signature for macros

If you want to use macros automatically in Outlook you need to sign them. You can sign your macros directly from the Visual Basic editor via the menu Tools  Digital Signature. The dialog allows you to sign your project.

If you restart Outlook and run your macro you might receive a warning but the macro should run. You might have to change your security settings under Tools  Trust Center on the tab macros.

4.3. A program is trying to access e-mail addresses you have stored in Outlook

If you have a macro which access the outlook email addresses you in some cases receive the following warning: "A program is trying to access e-mail addresses you have stored in Outlook".

Installing the tool Advanced Security for Outlook from MAPILab will solve this problem.

5. Links and Literature