More Outlook Resource Sites

Microsoft Developer Network (MSDN)

FAQs and other general resources

share code 23-Apr-2019 02:57

Looking for help with Outlook programming projects — VSTO, add-ins, VBA, custom Outlook forms, etc.? You′ve come to the right place!

NEW! >> Subscribe to this site via RSS. For more RSS options, see the complete list of feeds on our main news page.

Code level: intermediate    Code area: Basic Outlook Printer Friendly Version
Title: Import Appointments FROM Excel
Description: This Example illustrates how to import appointment items from EXCEL file. Assuming that XLS file is in following format: Subject, Contact, Categories, Start_Date, End_Date, Start_Time, End_Time, Reminder. Possible Values for Reminder Field is :'No Reminder','0 Minutes','1 Day','2 Days', '1 Week' Yes you can modify the code according to your requirements,
Date: 25-Feb-2005  20:01
Code level: intermediate
Code area: Basic Outlook
Posted by: Sheetal Soni
This message is displayed as VB.NET
 Sub ImportAppointments()
    Dim exlApp As Excel.Application
    Dim exlWkb As Workbook
    Dim exlSht As Worksheet
    Dim rng As Range
    
    Dim itmAppt As Outlook.AppointmentItem
    Dim aptPtrn As Outlook.RecurrencePattern
    
    
    
    Dim fso As FileSystemObject
    Dim fl As File
    
    Set exlApp = New Excel.Application
    
    strFilepath = exlApp.GetOpenFilename
    If strFilepath = False Then
        exlApp.Quit
        Set exlApp = Nothing
        Exit Sub
    End If
    
    
    
    
    Set exlSht = Excel.Application.Workbooks.Open(strFilepath).Worksheets(1)
    Dim iRow As Integer
    Dim iCol As Integer
    
    Dim tmpItm As Outlook.Link
    Dim mpiFolder As MAPIFolder
    Dim oNs As NameSpace
    
    Set oNs = Outlook.GetNamespace("MAPI")
    
    Set mpiFolder = oNs.GetDefaultFolder(olFolderContacts)
    
    iRow = 2
    iCol = 1
    
    While exlSht.Cells(iRow, 1) <> ""
        Dim cnct As ContactItem
        Set itmAppt = Outlook.CreateItem(olAppointmentItem)
        itmAppt.Subject = exlSht.Cells(iRow, 1)
        Set cnct = mpiFolder.Items.Find("[FullName] = " & exlSht.Cells(iRow, 2))
        If cnct Is Nothing Then
            Set cnct = Outlook.CreateItem(olContactItem)
            cnct.FullName = exlSht.Cells(iRow, 2)
            cnct.Save
        End If
        itmAppt.Categories = exlSht.Cells(iRow, 3)
        itmAppt.Start = exlSht.Cells(iRow, 4)
        itmAppt.AllDayEvent = True
        
        itmAppt.Links.Add cnct
        Set aptPtrn = itmAppt.GetRecurrencePattern
        aptPtrn.StartTime = exlSht.Cells(iRow, 5)
        aptPtrn.EndTime = exlSht.Cells(iRow, 6)
        aptPtrn.RecurrenceType = olRecursYearly
        aptPtrn.NoEndDate = True
        If aptPtrn.Duration > 1440 Then aptPtrn.Duration = aptPtrn.Duration - 1440
        Select Case exlSht.Cells(iRow, 7)
        Case "No Reminder"
            itmAppt.ReminderSet = False
        Case "0 minutes"
            itmAppt.ReminderSet = True
            itmAppt.ReminderMinutesBeforeStart = 0
        Case "1 day"
            itmAppt.ReminderSet = True
            itmAppt.ReminderMinutesBeforeStart = 1440
        Case "2 days"
            itmAppt.ReminderSet = True
            itmAppt.ReminderMinutesBeforeStart = 2880
        Case "1 week"
            itmAppt.ReminderSet = True
            itmAppt.ReminderMinutesBeforeStart = 10080
        End Select
        itmAppt.Save
        iRow = iRow + 1
    Wend
    Excel.Application.Workbooks.Close
    exlApp.Quit
    Set exlApp = Nothing


End Sub

All 85comments
Page [ 1 2 3 4 5 6 7 8 9 Next >>  
  23-Mar-2005  09:05   
Interesting... Anybody has code to do the reverse (export appointments into an Excel sheet).
If not, I'll try to write one based on this some day, and publish it.
  24-Mar-2005  07:31   
Mil, see http://www.outlookcode.com/d/customimport.htm
  31-Mar-2005  10:47   
Thanks Sue, that's a nice strating point, especially http://www.helenfeddema.com/CodeSamples.htm#Code58
  02-Apr-2005  23:21   
I created a module in the outlook Visual Basic editor and ran this code. I did set the references that I thought would be required. When I run this code the first Dim statement yields the error "user defined type not defined". What other set up operations do I need to perform for this code to work? I will appreciate any help on this. Thanks.
 
  03-Apr-2005  13:44   
JCV, what references did you set. This code requires the Microsoft Excel library, Scripting Runtime, and of course Microsoft Outlook.
  04-Apr-2005  13:30   
Sue M.,Thank you very much! I did not have the correct references set. Once I set the references you suggested, it fixed the problem. This is a really great little module. I just have to change it around a little to do exactly what I need. Thanks again for your help.
  16-Apr-2005  12:22   
I am looking for the Property/Method that I need to use with the "itmAppt." to set the "Label" for the appointment item. From what I can tell, the "Label" can't be set programatically in the same simple way as the other parameters of the appointment item. I appreciate any help. Thanks.
  16-Apr-2005  17:06   
JCV, see http://www.outlookcode.com/codedetail.aspx?id=139 for sample code. CDO or Redemption is required.
  20-Apr-2005  05:58   
Can this be adapted to import form a csv opposed to an xls file? I have NO VBA experience but a book is on order! If the question sounds remidial, it's because I know nothing. thanks!
  20-Apr-2005  07:01   
skinfreak, yes, it could be adapted. You'd use FileSystemObject methods to read the .csv file line by line. See http://www.outlookcode.com/d/vbscript.htm for FSO references and http://www.outlookcode.com/d/customimport.htm for more import samples that should help you get on the right track.
Page [ 1 2 3 4 5 6 7 8 9 Next >>