Outlook Technologies

Outlook Forms

Languages

Samples

More Outlook Resource Sites

Microsoft Developer Network (MSDN)

FAQs and other general resources

share code 02-Sep-2010 22:21

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
Body: 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 >> ]
	mll 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.
	Sue Mosher 24-Mar-2005 07:31 Mil, see http://www.outlookcode.com/d/customimport.htm
	mll 31-Mar-2005 10:47 Thanks Sue, that's a nice strating point, especially http://www.helenfeddema.com/CodeSamples.htm#Code58
	JCV 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.
	Sue Mosher 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.
	JCV 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.
	JCV 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.
	Sue Mosher 16-Apr-2005 17:06 JCV, see http://www.outlookcode.com/codedetail.aspx?id=139 for sample code. CDO or Redemption is required.
	skinfreak 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!
	Sue Mosher 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 >> ]

name email