It's not unusual for Outlook users to treat the calendar as a diary or journal, using it to store notes about a meeting. Brian wanted to know how to automate it - with a start and end button to record the time.
I'm looking for a macro that would have a start and stop button; when pressing the start button the macro would record what time it is; when pressing the stop button it would record what time it is again; then create a new appointment item on the calendar, push both of those fields (start time and end time) to a new appointment item on the calendar, and allow me to record the subject and notes and save the item.
The journal has a timer built into it, but we don't generally recommend using the journal because it is deprecated and may eventually be removed from Outlook.
It takes just a few lines of code to do this in an appointment. The first macro is one line and captures the current time, storing in it a global variable. The second macro creates the appointment form, using the stored variable as the Start time and Now as the End time. Create buttons on the main Outlook ribbon or Quick Access Toolbar (QAT) for each macro and it's ready to use.
When you add buttons to the QAT, they are available in all Outlook modules. When you customize the ribbon, the button is available only when working in that folder type.
Dim dStart As Date Sub startTime() dStart = Now End Sub Sub endTime() Dim oAppt As AppointmentItem Set oAppt = Application.CreateItem(olAppointmentItem) With oAppt .Start = dStart .End = Now .Display End With End Sub
Set the end time before closing
What if you want to take notes during the meeting or phone call? In that case, you can open the new appointment form first then use a macro to set the end time after the meeting ends. Add the Start button to the main Outlook ribbon or QAT and the End Time macro to the Appointment form's ribbon or QAT.
You don't actually even need a macro to do this, although it does save a few steps over the manual method:
- Open Appointment form
- Type Now in Start time field then tab out.
- When meeting ends, type Now in End time field then tab out.
Dim oAppt As AppointmentItem Sub ApptStart() Set oAppt = Application.CreateItem(olAppointmentItem) With oAppt .Start = Now .End = Now .Display End With End Sub Sub ApptEnd() oAppt.End = Now End Sub
Use a non-default calendar folder
This macro saves the diary events in a subfolder of the calendar. To use it with the selected calendar, change the Set otherFolder line to
Set otherFolder = Ns.Application.ActiveExplorer.CurrentFolder
The same method can be used with the first macro to save the events in a different folder.
Sub ApptStart() Dim Ns As Outlook.NameSpace Dim otherFolder As Outlook.Folder Set Ns = Application.GetNamespace("MAPI") 'Use a specifc folder or the current folder Set otherFolder = Ns.GetDefaultFolder(olFolderCalendar).Folders("Test") Set oAppt = otherFolder.Items.Add(olAppointmentItem) With oAppt .Start = Now .End = Now .Display End With End Sub Sub apptEnd() oAppt.End = Now End Sub
How to use macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor