Outlook VBA

Click the dropdown to view articles in the Outlook VBA section:

Microsoft Outlook VBA Code Samples

Here are links to my Outlook VBA code samples. There are many more on my blog. I also have some generic Outlook VBA routines below.

Click here to find out how to assign a macro to a toolbar button.

Lunch Special

A procedure (that works in Excel as well) that pops up a MsgBox with a random msg. In this case, it's a fun app that helps me choose what to eat for lunch. I can see many other uses for this code. I use the same code in the Application_Startup event to pop up a random msg when Outlook starts.

Sub PickLunchForMe()
Dim X As Integer
X = Int((5 - 1) * Rnd + 1)

Select Case X
    Case 1
        MsgBox "Deli"
    Case 2
        MsgBox "Spanish food"
    Case 3
        MsgBox "Chinese food"
    Case 4
        MsgBox "Diner (American food)"
    Case 5
        MsgBox "Tex Mex"
    Case Else
End Select

End Sub

I attach this code to a toolbar button to run it on demand. If you have a different number of options, simply change the "5" in the code to whatever number you want. For example, if you had 10 options, the code would read X = Int((10 – 1) * Rnd + 1). Then you would need to add additional "Case" statements as appropriate. Hopefully your meal choices are better than mine.

Create Excel Workbook (From Outlook)

This is a simple routine that generates a blank Excel workbook from Outlook. Optional: set a reference to the Excel application library from the Outlook VBIDE. You don't have to set the workbook Visible property to True if you want to write anything to it. At this point you can you use any Excel VBA techniques on the workbook — they work the same as if we were operating in the Excel VBIDE.

Sub CreateXLWorkbook()
Dim xlApp As Object ' Excel.Application
Dim xlWkb As Object ' Excel.Workbook

Set xlApp = CreateObject("Excel.Application") ' New Excel.Application

Set xlWkb = xlApp.Workbooks.Add

xlApp.Visible = True

Set xlApp = Nothing
Set xlWkb = Nothing

End Sub

Lunch Reminder

Speaking of lunch, here's a way for all you cardpunchers out there to keep track of when you need to punch in after lunch.

Sub LunchReminder()

Dim tsk As Outlook.TaskItem
Dim currentDate As Date
Dim currentTime As Date
Dim futureTime As Date

  Set tsk = Outlook.CreateItem(olTaskItem)
  currentDate = Date
  currentTime = Time

  ' add slightly under 1 hour to calculate return time
  futureTime = currentTime + (1 / 27)

  ' for 30 minute lunch, use:
  ' currentTime + (1 / 55)

  With tsk
    .subject = "Punch In From Lunch"
    .DueDate = currentDate
    .StartDate = currentDate
    .ReminderSet = True
    .ReminderTime = currentDate & " " & futureTime
  End With

  MsgBox "Be back by " & futureTime

End Sub

This code creates a Task reminder for slightly less than an hour after you run the macro. So if you leave for lunch at 12pm, run the code and a Task will appear around 12:55, reminding you that you are just another office drone (like me) that needs to punch in to get paid.

If you want to add this to a toolbar button, run the following code:

Sub maketoolbarbutton()

  Call AddToolbarButton("Lunch Reminder", "Set up Lunch Reminder", _
      "LunchReminder", , 33, msoButtonIconAndCaption)

End Sub

Function AddToolbarButton(Caption As String, toolTip As String, macroName As String, _
                          Optional toolbarName As String = "Standard", _
                          Optional FaceID As Long = 325, _
                          Optional buttonStyle As MsoButtonStyle = msoButtonIconAndCaption)
Dim objBar As Office.CommandBar
Dim objButton As Office.CommandBarButton

  Set objBar = ActiveExplorer.CommandBars(toolbarName)
  Set objButton = objBar.Controls.Add(msoControlButton)

  With objButton
    .Caption = Caption
    .OnAction = macroName
    .TooltipText = toolTip
    .FaceID = FaceID
    .Style = buttonStyle
    .BeginGroup = True
  End With

End Function

For an explanation of how AddToolbarButton works, visit Create Outlook toolbar buttons using VBA.

Site last updated: August 20, 2014

Peltier Tech Charting Utilities for Excel