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?

 

[wallyxl]

Can't belive I missed that.
Cheers JP.

 

[cnestg8r]

JP,
Your reference was very good. Thanks. However, it seems a little ambiguous about the intended set values for Start(End)TimeZone and Start(End)inTimeZone. For example I am trying to appointments of international flights:
26 Aug 2011 ATL-LAX dep 17:45 EST(-5+1) Arr 19:30 PST(-8+1).
26 Aug 2011 LAX-MEL departing 23:30 PST arr 28 Aug 2011 7:30 AEST(+10).

Any examples to mimic?

 

[brokian42]

I am using the following code to create repeating appointments based on a start date and time but avoiding School Holidays however I get the following error when executing the code

Run-time Error 13
Type MisMatch

The error appears to be with this line

.Start = CDate(Format(cell.Value, "dd/mm/yyyy") & " " & Format(cell.Offset(0, 1).Value, "hh:mm:ss am/pm"))


Any help gratefully accepted.

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

[/QUOTE]

 

[Tripp Mcnealy]

Sorry guys to dig up an old thread but this is exactly what i am looking for. My question is what program are you writing this code with? and where do you enter this code? I dont know of an area in outlook to enter this code..

 

[wallyxl]

Tripp,
You are exporting from Excel to Outlook, so the code needs to be entered into Excel Visual Basic.
Open the woorkbook with the data to be exported in.
On the top tabs you should see DEVELOPER, click on this.
On the left hand side you will see a list of worksheets, click on the one you have the data in, then copy and paste the code.
BIG HINT:
Open a new profile in Outlook and send some test data to this as you may need to tweek the code to suit your spread sheet.
Best of luck.

 

[Tripp Mcnealy]

thanks wallyxl.. going to give it a shot this morning.