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
Randomize
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
.Save
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.
