Skip to main content

How to create appointment from Excel data?

Author: Xiaoyang Last Modified: 2018-08-31

Supposing, you have a table of appointment data in an Excel worksheet as following screenshot shown, now, you want to import these data into the Outlook calendar. How could you deal with this job quickly?

doc export excel data to appointment 1

Create appointments from Excel data with VBA code

Create appointments from Excel data with VBA code

To create appointments from Excel data, you can apply the following VBA code, please do as this:

1. Launch Outlook and hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Import excel data to appointment:

Public Sub CreateOutlookApptz()
    Dim xAppointmentItem As Outlook.AppointmentItem
    Dim xNameSpace As Outlook.NameSpace
    Dim xCalendarFld As Outlook.MAPIFolder, xSubFolder As Outlook.MAPIFolder
    Dim xCalendarStr As String
    Dim I As Long
    Dim xFileDialog As FileDialog
    Dim xFilePath As String
    Dim xExcelApp As Excel.Application
    Dim xWb As Workbook
    Dim xWs As Worksheet
    On Error GoTo Err_Execute
    Set xExcelApp = New Excel.Application
    Set xFileDialog = xExcelApp.FileDialog(msoFileDialogFilePicker)
    With xFileDialog
        .Title = "Select a file"
        .Filters.Add "Microsoft Excel", "*.xlsx"
    End With
    If xFileDialog.Show = 0 Then Exit Sub
    xFilePath = xFileDialog.SelectedItems(1)
    Set xWb = xExcelApp.Workbooks.Open(xFilePath)
    Set xNameSpace = Outlook.Application.Session
    Set xCalendarFld = xNameSpace.GetDefaultFolder(olFolderCalendar)
    I = 2
    Set xWs = xWb.Worksheets.Item(1)
    xCalendarStr = xWb.Name
    If FolderExist(xCalendarFld, xCalendarStr) = False Then
        Set xSubFolder = xCalendarFld.Folders.Add(xCalendarStr, olFolderCalendar)
        Set xSubFolder = xCalendarFld.Folders(xCalendarStr)
    End If
    Do Until Trim(xWs.Cells(I, 1).Value) = ""
        Set xAppointmentItem = xSubFolder.Items.Add(olAppointmentItem)
        With xAppointmentItem
            .Start = xWs.Cells(I, 5) + xWs.Cells(I, 6)
            .End = xWs.Cells(I, 7) + xWs.Cells(I, 8)
            .Subject = xWs.Cells(I, 1)
            .Location = xWs.Cells(I, 2)
            .Body = xWs.Cells(I, 3)
            .BusyStatus = olBusy
            .ReminderMinutesBeforeStart = xWs.Cells(I, 9)
            .ReminderSet = True
            .Categories = xWs.Cells(I, 4)
        End With
        I = I + 1
    Set xAppointmentItem = Nothing
    Set olApp = Nothing
    Set xExcelApp = Nothing
    MsgBox "Import successfully!", vbInformation, "Kutools for Outlook"
    Exit Sub
    MsgBox "An error occurred - Exporting items to Calendar.", vbInformation, "Kutools for Outlook"
End Sub
Function FolderExist(CalFolder As Folder, FolderName As String) As Boolean
    Dim I As Integer
    Dim xSubFolder As Folder
    For I = 1 To CalFolder.Folders.Count
        Set xSubFolder = CalFolder.Folders.Item(I)
        If xSubFolder.Name = FolderName Then
            FolderExist = True
            Exit Function
        End If
    Next I
End Function

3. Still in the Microsoft Visual Basic for Applications window, click Tools > References to go to the References-Project1 dialog box, and check Microsoft Excel Object Library option from the Available References list box, see screenshot:

doc export excel data to appointment 2

4. Then click OK button, now, press F5 key to run this code, and a Select a file window is displayed, please select the excel file you want to import to Outlook, see screenshot:

doc export excel data to appointment 3

5. And then click OK, a prompt box is popped out as follows:

doc export excel data to appointment 4

6. Then click OK, the Excel data has been imported into the calendar as following screenshot shown:

doc export excel data to appointment 5

Best Office Productivity Tools

Breaking News: Kutools for Outlook Launches Free Version!

Experience the all-new Kutools for Outlook FREE version with 70+ incredible features, yours to use FOREVER! Click to download now!

🤖 Kutools AI : Instant pro emails with AI magic--one-click to genius replies, perfect tone, multilingual mastery. Transform emailing effortlessly! ...

📧 Email Automation: Auto Reply (Available for POP and IMAP)  /  Schedule Send Emails  /  Auto CC/BCC by Rules When Sending Email  /  Auto Forward (Advanced Rules)   /  Auto Add Greeting   /  Automatically Split Multi-Recipient Emails into Individual Messages ...

📨 Email Management: Recall Emails  /  Block Scam Emails by Subjects and Others  /  Delete Duplicate Emails  /  Advanced Search  /  Consolidate Folders ...

📁 Attachments ProBatch Save  /  Batch Detach  /  Batch Compress  /  Auto Save   /  Auto Detach  /  Auto Compress ...

🌟 Interface Magic: 😊More Pretty and Cool Emojis   /  Remind you when important emails come  /  Minimize Outlook Instead of Closing ...

👍 One-click Wonders: Reply All with Incoming Attachments  /   Anti-Phishing Emails  /  🕘Show Sender's Time Zone ...

👩🏼‍🤝‍👩🏻 Contacts & Calendar: Batch Add Contacts From Selected Emails  /  Split a Contact Group to Individual Groups  /  Remove Birthday Reminders ...

Instantly unlock Kutools for Outlook with a single click—permanently free. Don't wait, download now and boost your efficiency!




Comments (3)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Returns "Compile error: User-defined type not defined"
This comment was minimized by the moderator on the site

Thank you so much but the code is not executing successfully when creating appointments in a shared calendar. Could you please help
This comment was minimized by the moderator on the site
super cool il ya une erreur je la i rectifier mai apparament mes items colle pas mince

olApp = Nothing ?
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations