Running a macro is simple: Select Tools | Macros, choose Macros from the resulting submenu, select the macro you want to run, and click Run. Simple, yes. Efficient, no. Frankly, given the purpose of a macro, five clicks seems extreme. You could complete some tasks quicker than you could execute the macro!
When running a macro takes more work than just doing the job yourself, it makes sense to create a toolbar button to run the macro. Here's how.
Note: This information is also available as a PDF download.First, you'll need a macro. If you already have one, just use it and skip this section. Otherwise, follow the next few steps to create the simple macro (subprocedure) in Listing A:
- Launch the Visual Basic Editor (VBE) by pressing Alt+F11.
- Choose Module from the Insert menu to open a new module.
- Enter the subprocedure shown in Listing A.
- Save the module and close the VBE.
Sub GetItemCount()Run the macro by selecting Tools | Macros, choosing Macros, selecting GetItemCount in the Macros dialog box shown in Figure A, and then clicking Run. The PickFolder method displays the Select Folder dialog box. Select a folder, as shown in Figure B, and click OK. (This macro has no error handling, so if you click Cancel, you'll generate an error.) Figure C shows the macro's results — the number of items in the folder you selected. Click OK to clear the message box.
'Return the number of messages in selected folder.
'The resulting count doesn't include subfolders.
Dim ns As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim intCount As Integer
Dim strMessage As String
Set ns = GetNamespace("MAPI")
Set objFolder = ns.PickFolder
intCount = objFolder.Items.Count
strMessage = "The folder " _
& objFolder.Name _
& " has " _
& intCount _
& " item(s)."
MsgBox strMessage, vbOKOnly, "Information"End Sub
Figure A: Select a macro from the Macros dialog box.
Figure B: The macro displays the Select Folder dialog box.
Figure C: The GetItemCount macro displays the number of items in the selected folder.
You might feel like you could count the folders faster than you could run this macro. So to speed things up, we'll add a custom button to the menu bar or the toolbar as follows:
- Right-click the menu bar or a toolbar and select Customize. Alternately, choose View | Toolbars and select Customize.
- In the Customize dialog box, click the Commands tab.
- Select Macros from the Categories list to update the Commands list, as shown in Figure D.
- From the Commands list, drag and drop the GetItemCount macro onto a toolbar or onto the menu bar, as shown in Figure E.
- Close the Customize dialog box.
- To try it out, click the new Project1.GetItemCount button to execute the macro.
Figure D: The Commands list displays all the macros in the current project.
Figure E: Drag a macro to one of the built-in bar objects. More than likely, you'll want to change the button's caption. To do so, reopen the Customize dialog box. Then, right-click on the button (on the toolbar or menu bar) to access its properties. In the Name field, enter Get Item Count, as shown in Figure F. You can also change the button's image or style. Close the Customize dialog box to commit the change.
Figure F: Change the button's caption.
Creating a custom toolbarIf you don't want to add a button to one of the built-in bars, create a new one. First, open the Customize dialog box by choosing View | Toolbars and selecting Customize. Click the Toolbars tab and click the New button. Enter a name for the new toolbar, as shown in Figure G. When you click OK, Outlook displays a small floating toolbar, which you can dock or move as you like. You might have to move the Customize dialog box to find it.
Figure G: Give the custom toolbar a name. Using the same method outlined above, drag commands or macros to the new toolbar. Figure H shows the GetItemCount macro added to a custom toolbar named Macros, docked between the built-in menu bar and the Standard toolbar.
Figure H: Add macros to a custom toolbar.
Quick access to macros
Macros should make things easier, not slow you down. Don't bother with the Macros menu to execute a macro. Doing so requires too many clicks. Instead, drop a custom button onto the menu bar or a toolbar. Your macro will then be just one click away.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at email@example.com.