Import data from excel to Outlook Calender

[wallyxl]

Hi,
I'm trying to import data from Excel into Outlook Calender.

<TABLE style="WIDTH: 309pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=412><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 92pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20 width=123>Start Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 107pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=142>Start Time</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 110pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=147>Game</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>18-Aug-11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>8:30:00 AM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Kansas City</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>18-Aug-11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>6:30:00 AM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> Fluminense </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>18-Aug-11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>8:50:00 AM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> Internacional </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>19-Aug-11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>8:00:00 AM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> Flamengo </TD></TR></TBODY></TABLE>

Start Date is A1.
Game is the message subject and I would like to set it to remind me one hour before the start time.
Sometimes there may be one row of information and others ten rows or more. I will clear the data once it is imported and repopulate before running it again.

Anyone able to help please?

 

[JP2112]

I can't resist helping a fellow soccer fan.

Try this code. It creates a task (w/ reminder) for each row. These are not calendar items; those are for appointments and meetings.

Sub CreateTasksInBulk()
Dim ol As Object ' Outlook.Application
Dim task As Object ' Outlook.TaskItem
Dim cell As Excel.Range
' get Outlook
Set ol = CreateObject("Outlook.Application")
If Not ol Is Nothing Then
' loop through column A
For Each cell In Range(Cells(2, 1), Cells(Range("A1").End(xlDown).Row, 1))
  Set task = ol.CreateItem(3) ' olTaskItem
  With task
    .DueDate = cell.Value
    .ReminderSet = True
    .ReminderTime = CDate(Format(cell.Value, "mm/dd/yyyy") & " " & Format(cell.Offset(0, 1).Value, "hh:mm:ss am/pm")) + 1 / 24
    .Subject = cell.Offset(0, 2).Value
    .Close 0 ' olSave
  End With
Next cell
End If
End Sub

 

[wallyxl]

JP,
Absolute brilliance. Thank you so much, you have just made an Irishman in Australia very happy.
One small problem, the reminder is set for one hour after, I just changed the +1 to -1.
I really do appreciate this, once again THANK YOU VERY MUCH.
Wally.

 

[JP2112]

Oops, that's what I meant. You're welcome!

 

[cnestg8r]

If one wanted to import a similar table from XL 2007 to Outlook 2007 calendar with a 3 hour appointment duration, how would you modify the above code?

 

[wallyxl]

Hi JP,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Good old Dane Richards, keeping the Red Bulls hopes alive.<o></o>
One small problem which I have been struggling with for the last few hours. Everything works fine, when I need to change the reminder time I just change the +1 to -168 for a week. All good, EXCEPT, when it is a single digit date, i.e. 03-Sep-11.<o></o>
It wants to read this as March 9th and set the reminder from then. The task pane is right, Due in 13 days, just the reminder.<o></o>
I have tried different date formats but still the same.<o></o>
Any ideas?<o></o>
Wally.<o></o>

 

[JP2112]

If one wanted to import a similar table from XL 2007 to Outlook 2007 calendar with a 3 hour appointment duration, how would you modify the above code?

Something like this:

Sub CreateapptsInBulk()
  Dim ol As Object ' Outlook.Application
  Dim appt As Object ' Outlook.AppointmentItem
  Dim cell As Excel.Range
  ' get Outlook
  Set ol = CreateObject("Outlook.Application")
  If Not ol Is Nothing Then
    ' loop through column A
    For Each cell In Range(Cells(2, 1), Cells(Range("A1").End(xlDown).Row, 1))
      Set appt = ol.CreateItem(1)  ' olappointmentitem
      With appt
        .Start = CDate(Format(cell.Value, "mm/dd/yyyy") & " " & Format(cell.Offset(0, 1).Value, "hh:mm:ss am/pm"))
        .Duration = 180 ' 3 hours
        .Subject = cell.Offset(0, 2).Value
        .ReminderSet = True
        .ReminderMinutesBeforeStart = 15
        .Subject = cell.Offset(0, 2).Value
        .Close 0  ' olSave
      End With
    Next cell
  End If
End Sub

 

[JP2112]

Hi JP,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Good old Dane Richards, keeping the Red Bulls hopes alive.<o></o>
One small problem which I have been struggling with for the last few hours. Everything works fine, when I need to change the reminder time I just change the +1 to -168 for a week. All good, EXCEPT, when it is a single digit date, i.e. 03-Sep-11.<o></o>
It wants to read this as March 9th and set the reminder from then. The task pane is right, Due in 13 days, just the reminder.<o></o>
I have tried different date formats but still the same.<o></o>
Any ideas?<o></o>
Wally.<o></o>

Change "mm/dd/yyyy" to "dd/mm/yyyy" that should fix the reminder date.

 

[cnestg8r]

Many thanks JP. Where can I look online to see all of the options and key words used in:

"With appt
.xxxx = zzz "
?

 

[JP2112]

Depends on your version of Outlook.

2003: http://msdn.microsoft.com/en-us/library/aa210899(office.11).aspx
2007: http://msdn.microsoft.com/en-us/library/bb208261(v=office.12).aspx
2010: http://msdn.microsoft.com/en-us/library/ff870661.aspx