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