Importing to the correct calendar from Excel 2010 to Outlook 2010

Status
Not open for further replies.

Jin

New Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Thank you for looking at my question. I am fairly new to VBA. I am trying to import appointments from
Excel 2010 to Outlook 2010 using the code below. Running the code does not generate any errors but the appointments go into my default calendar rather than the calendar named "Smith".
Please let me know how to populate the correct calendar. The calendar "Smith" is not yet shared and I do not have (or want) a contact for it. I work in an office with 13 people. Each person has a worksheet in a spreadsheet that they update every 2 weeks. From there I import what they are working on as appointments into a calendar so the boss can see (month by month) what project each person is working on. This way when he gets a call requesting someone do something he can look at the calendar and see what impact that will have. He can also look at or overlap people's calendars to determine if someone else might have a better schedule to accommodate the request.
I started by modifying the code in the following link.
http://www.outlookcode.com/threads_print.aspx?forumid=1&messageid 775

Thank you for your help and time. - Jin
Code:
Dim exlWkb As Workbook
Dim exlSht As Worksheet
Dim rng As Range
Dim itmAppt As Outlook.AppointmentItem
Dim aptPtrn As Outlook.RecurrencePattern
Dim strFilePath As String
Dim iRow As Integer
Dim iCol As Integer
Dim tmpItm As Outlook.Link
Dim mpiFolder As MAPIFolder
Dim oNs As Outlook.Namespace
Dim Ldate As Date
Dim LTime As Date
Dim k As Integer
Dim Rsrc As Long
Dim n As Integer
Dim sStr As String
Dim j As Integer
n = 0
j = 7
k = 0
Set exlWkb = ThisWorkbook
Set exlSht = exlWkb.Worksheets(27)
Sheets(27).Select
Sheets(27).Activate ' make the sheet active
ActiveSheet.Range("A3").Select
Selection.CurrentRegion.Select  ' Selects the current data area.
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Select  ' Selects the current data area without the top two rows.
Rsrc = Rsrc + Selection.Rows.Count
  sStr = ""
  Set oNs = Outlook.GetNamespace("MAPI")
iRow = 3
For n = 1 To Rsrc
  Set mpiFolder = oNs.GetDefaultFolder(olFolderCalendar).Folders("Smith")
  Set itmAppt = Outlook.CreateItem(olAppointmentItem)
  Set aptPtrn = itmAppt.GetRecurrencePattern
  If IsNull(exlSht.Cells(iRow, 6)) Or exlSht.Cells(iRow, 6) = "" Then
  ' no start date provided
  Else
  With itmAppt
  Ldate = exlSht.Cells(iRow, 6)
  If n = 1 Then
  LTime = TimeSerial(j, k, 0)
  Else
  LTime = LTime + TimeValue("00:15")
  End If
  .Start = Ldate & " " & LTime
  sStr = ""
  If IsNull(exlSht.Cells(iRow, 8)) Or exlSht.Cells(iRow, 8) = "" Then
  sStr = "no end date"
  .NoEndDate = True
  ElseIf exlSht.Cells(iRow, 6) = exlSht.Cells(iRow, 8) Then
  .AllDayEvent = True
  Else
  ' .EndTime = exlSht.Cells(iRow, 7) ' H Due Date
  .End = exlSht.Cells(iRow, 8)
  End If
  If IsNull(exlSht.Cells(iRow, 12)) Or exlSht.Cells(iRow, 12) = "" Then
  If sStr = "no end date" Then
  .Subject = "no task name; no end date"
  Else
  .Subject = "no task name"
  End If
  Else
  If sStr = "no end date" Then
  .Subject = exlSht.Cells(iRow, 12) & " - " & sStr
  .Subject = exlSht.Cells(iRow, 12) 'L
  Else
  .Subject = exlSht.Cells(iRow, 12)
  End If
  End If
  If Not IsNull(exlSht.Cells(iRow, 5)) And exlSht.Cells(iRow, 5) <> "" Then
  If exlSht.Cells(iRow, 5) = "continuous" Then
  aptPtrn.RecurrenceType = olRecursDaily
  ElseIf exlSht.Cells(iRow, 5) = "Weekly" Then
  aptPtrn.RecurrenceType = olRecursWeekly
  ElseIf exlSht.Cells(iRow, 5) = "Monthly" Then
  aptPtrn.RecurrenceType = olRecursMonthly
  '  ElseIf exlSht.Cells(iRow, 5) = "bimonthly" Or exlSht.Cells(iRow, 5) = "bi-monthly" Then
  'apparently not an option
  ElseIf exlSht.Cells(iRow, 5) = "Annual" Or exlSht.Cells(iRow, 5) = "Annually" Or exlSht.Cells(iRow, 5) = "Yearly" Then
  aptPtrn.RecurrenceType = olRecursYearly
  End If
  End If
  .ReminderSet = False
  End With
  itmAppt.Save
  End If
  iRow = iRow + 1
Next n
Set exlSht = Nothing
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
The CreateItem function automatically adds the new item to the default folder. Instead, as itmAppt already points to the "Smith" calendar, call its Items.Add function.
 

Jin

New Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Thank you so much Michael! I was on the wrong road to solving the problem; I didn't understand it.
The Items.Add function works perfectly! Thank you so much. I appreciate your help, explanation, and time. :]
Kind regards,
jin
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
F Correct email dates corrupted during importing from Outlook Express Using Outlook 1
J Outlook 2010 Changing events in Outlook calendar via opening file, importing CSV Using Outlook 0
O Importing Mbox - anyone out there having experience with this tool... Using Outlook 3
D Importing Outlook Categories from another domain (Exchange 2016/Outlook 2016) Using Outlook 4
B Importing business card into Outlook for SIgnature Using Outlook 1
S Importing Ribbons Not Saved Using Outlook 7
V importing appointments to non-default calendar? Using Outlook 1
Diane Poremsky Importing Lists from Excel to Outlook New Slipstick.com Articles 0
B Importing Text from Excel to Outlook 2013 Calender Using Outlook 0
Christopher M Importing from Eudora Update Using Outlook 5
Christopher M Importing from Eudora Using Outlook 7
N Importing Google contacts from CSV file removes recipient names in autocomplete list Using Outlook 0
S Importing emails to contacts Using Outlook 3
Z Importing PST files from Outlook 07 to Outlook mac Preview Using Outlook 1
Z how to make OL (2007) to use custom contact form when importing / opening .VCF? Using Outlook 1
P Importing Thunderbird email to outlook Using Outlook 3
K Importing appointment body from excel in outlook 2010 Using Outlook 1
A Importing too many emails from Gmail Using Outlook 2
S Possible to link to ics file without importing? Using Outlook 4
L Importing Opportunities in to BCM 2013 BCM (Business Contact Manager) 0
K New to BCM - trouble importing contacts & accounts BCM (Business Contact Manager) 3
LarryH Importing csv file to custom form/fields? Using Outlook 3
T Importing Excel 2010 data into Outlook Calendar 2010 Using Outlook 12
W Importing .pst from Outlook 2010 (Win7) to Outlook 2013 (Win8.1) Using Outlook 2
V Importing Rules in Outlook 2013 Using Outlook 2
D Importing custom fields into custom form in BCM BCM (Business Contact Manager) 1
V Problem not having Contacts folder after importing emails from Yahoo. Using Outlook 0
O Importing contacts from CSV, comma delimited Using Outlook 7
B importing Outlook from a backup on an external hard drive Using Outlook 5
M Trouble Importing folders from Outlook 2007 to Outlook 2013 Using Outlook 12
wallisellener BCM 2013 importing contacts from CSV file BCM (Business Contact Manager) 8
T BCM not importing address BCM (Business Contact Manager) 1
T Importing OE6 e-mail to Outlook via Store Folder Using Outlook 9
A Trouble importing data to radio buttons Business Contact Manager 2010 (BCM) Using Outlook 2
H Importing Windows Live Mail Contact into Outlook 2010 Using Outlook 0
T Limit on importing Inbox mail from OE6 Using Outlook 16
Commodore Importing RSS on another computer (and feed locations) Using Outlook 8
M importing email address list from excel Using Outlook 6
O Importing pst, archive pst and address not working Using Outlook 17
N lose conditional formatting when importing to Outlook 2010 Using Outlook 2
C dates missing when importing emails Using Outlook 5
I Importing Outlook form into calendar folder? Using Outlook 1
R Exporting and importing contacts in Outlook 2007 Using Outlook 1
K Importing CSV contacts- doesnt allow me to map or move forward V 2007 Using Outlook 1
G Importing Excel contact data base into Outlook Using Outlook 1
V Importing .pst file Using Outlook 1
L Importing multiple record from .vcf (vCard) file Using Outlook 5
M There was a problem importing your contacts. (Online with Hotmail) Using Outlook 1
S Importing outlook express backup into outlook2010... how to do it? Is it possible? Using Outlook 2
S Importing a PST file into Office 2003-OPEN WITH using Microsoft 2007!! Using Outlook 1
Similar threads


















































Top