✨ OfficeTricks

How to Add a Calendar in Outlook? - Excel VBA Outlook Calendar

📅 Published: 2014-01-11 📁 Category: Excel VBA Macro, Outlook Integration

Excel VBA Outlook Calendar - Bulk Upload Tasks

Easy trick on how to add a Calendar Item in Outlook from list of reminders/appointments in Excel.

Manually creating big list of calendar appointments in Outlook is a time consuming task. This article explains about automating this task with a simple vba code.

Lets see how to Export and Import tasks between Outlook and Excel.

Add Appointments To Outlook Calendar - From Excel

Create a new Excel workbook with below header in Sheet1 & add some sample appointments. Columns 1 - 5 : "Subject", "Location", "Start Date/Time", "End Date/Time", "Reminder (in Seconds)".
  1. Enter list of appointments in sheet for above fields.
  2. Press Alt+F11 to get VB editor.
  3. Include 'Microsoft Outlook Object Library' from Tools -> References.
  4. Copy paste the below code.
  5. Execute the code by pressing F5.
Private Sub Add_Appointments_To_Outlook_Calendar()

    'Include Microsoft Outlook nn.nn Object Library from Tools -> References
    Dim oAppt As AppointmentItem, olApp As Object, iRow As Double, sSubj As String

    iRow = 2
    Set olApp = Outlook.Application
    sSubj = ThisWorkbook.Sheets(1).Cells(iRow, 1)
        
    'Loop through entire list of Reminders to be added
    While subj <> ""
        Set oAppt = olApp.CreateItem(olAppointmentItem)
        
        With oAppt
            .Subject = sSubj
            .Location = ThisWorkbook.Sheets(1).Cells(iRow, 2)
            .Start = ThisWorkbook.Sheets(1).Cells(iRow, 3)
            .End = ThisWorkbook.Sheets(1).Cells(iRow, 4)
            .ReminderMinutesBeforeStart = ThisWorkbook.Sheets(1).Cells(iRow, 5)
            .AllDayEvent = True
            .Save
        End With

        iRow = iRow + 1
        sSubj = ThisWorkbook.Sheets(1).Cells(iRow, 1)
    Wend
    
    MsgBox "Reminder(s) Added To Outlook Calendar"
End Sub
Once the code is executed, the reminders will be added to the Outlook Calendar. Open Outlook calendar & verify that the entries are added correctly. If you have a very big list of items to be added to Calendar, then this automation will be very useful.

How to Add Calendar Item to Outlook?

  1. Open Calendar in Outlook
  2. Choose the date to add appointment or task.
  3. Enter Subject, Location, Start/End Time, Description.
  4. Click Invite Attendees to send Meeting Invite to recipients.
  5. Adjust recurrence detail.
  6. Click Save/Send to finish.
If you have added any invitees, then Outlook will send the Meeting Invite to all recipients. If it is a reminder item for self, this will be saved in your Calendar.

Download Calendar Appointments to Excel

To fetch Appointment details from Outlook Calendar to Excel, there is two methods. You can use either the built in Export Option or using macro. Both the tricks are explained in this article. Topics Related to Excel Calendar Option: