Microsoft Excel Macro: Set Outlook Reminders from Excel
This is my second post on Excel Macros. I am not a coder but a script-kiddie. Macros that I develop are usually to help me manage routine tasks better. This one is something I have developed to help me in my present assignment. This assignment has several reports which are supposed to come at different frequencies. It’s difficult to keep a track the deadlines of reports when you are the recipient of reports. (It’s easier when you have to submit reports!)
Using this macro you can set Calendar reminders (Appointments) in your Microsoft Outlook right from your Excel. This is going to be a great help for projects when you have many deliverables lined up at regular intervals.
——–Start of Macro——-
Sub Outlook_Appointment()
Dim olApp As Outlook.Application
Dim olAppItem As Outlook.AppointmentItem
Set olApp = GetObject("", "Outlook.Application")
Set olAppItem = olApp.CreateItem(olAppointmentItem)
With olAppItem
.Start = Range("'Out_App'!A6").Value
.Subject = Range("'Out_App'!B6").Value
.Duration = 1
.ReminderSet = True
.Save
End With
End Sub
——–End of Macro——-
Here, .Start should point to the Cell Range where Date of the Appointment is present. & Subject is the Text in another Cell Range which is associated with the Date. Here Out_App is the name of the tab in the Worksheet.
By default, the reminder is set for 00:00 hours for a particular day. You may change it to suit your requirements.
Ability to excel instigates the ability to automate!
Known problems:
- When you run the macro, you may get a compilation error stating ‘User-defined type not defined’. To overcome this, simply go to Tools>Reference in Visual Editor and enable the libraries related to Outlook.
