Import Excel Text into Outlook Calender

[BryanG]

Hello,

I am trying to Import data from Excel 2013 into Outlook 2013. I tried the Import function but it is too cumbersome. As I live in Germany the , and ; characters are reversed and I can't Change it in my regional settings at work due to a GPO of some kind so I have to save the .csv file as text and run a replace fucntion to change them around.

Once I have done this, I have to create a temp calendar because the calendar I need the data to go into is not local. After all this, I for some reason still only get about 30% of the data to Import. Strange.

So I Googled further of course, and found that there could be some VBA scripts that can use, but I'm a beginner with VBA which brings me here to ask for your help. I need to import the Subject, Body, Dates and Times of course. Attached is a screenshot of the data that needs to be imported in blue.

Thanks!
Bryan

 

[Diane Poremsky]

This macro imports excel data to outlook Create Appointments Using Spreadsheet Data - it actually imports events to subfolder calendars but can be changed to import into any calendar in the profile.

to use 1 calendar change this line to point ot the correct calendar
Set CalFolder = olNs.GetDefaultFolder(olFolderCalendar)

and change subfolder in this line to calfolder.
Set olAppt = subFolder.Items.Add(olAppointmentItem)


Working with VBA and non-default Outlook Folders shows how to reference other calendars in the profile.

If you are unable to import directly into the correct calendar, an option instead of using a temp calendar is to add a unique category then move everything assigned to that category.

 

[BryanG]

The script isn't working for me. I get an error from the 1st line (User-Type not defined) I think it may have something to do with the commands in VB are in German. I don't know if I will be able to translate all the needed text, nor am I certain which text can stay as is. I've attached the error but don't know if that will be of any help. Also I am not sure which parts of the text to Change to fit my data, columns, rows etc. A real beginner here. Thank you for your patience!

 

[Diane Poremsky]

that error is because you need to set a reference to outlook in excel's vba editor's tools, reference. (This is an excel macro, not an outlook macro)

the fields *should* be ok using the English words.

 

[BryanG]

The reference is now set to "Microsoft Outlook 15.0 Object Library", but I still get the error. I have since did a Little more searching and found the below script. It als is not working for me. Can you see any mistakes?


Sub createAppointments()

On Error Resume Next
Dim sheet As Worksheet, rngStart As Range, rngEnd As Range, cell As Range
Set objOL = CreateObject("Outlook.Application")
Set objCal = objOL.Session.GetDefaultFolder(9)
Set sheet = Worksheets(1)
Set rngStart = sheet.Range("A2")
Set rngEnd = rngStart.End(xlDown)
counter = 0
For Each cell In sheet.Range(rngStart, rngEnd)
Set olApp = objCal.Items.Add(1)
With olApp
strSubject = cell.Text
strTitel = cell.Offset(0, 1).Text
strDescription = cell.Offset(0, 2).Text
strStartDate = cell.Offset(0, 3).Value
strEndDate = cell.Offset(0, 4).Value
strStartTime = cell.Offset(0, 5).Value
strEndTime = cell.Offset(0, 6).Value
.Subject = strSubject
.ReminderSet = False
If strCategory <> "" Then
.Categories = strCategory
End If
If boolAllDay = True Then
.AllDayEvent = True
If IsDate(strStartDate) Then
.Start = DateValue(strStartDate)
.End = DateAdd("d", 1, DateValue(strStartDate))
.Save
counter = counter + 1

Else
MsgBox "Termin mit dem Betreff: '" & strSubject & "' in Zeile " & cell.Row & " hat ungültige oder fehlende Zeitangaben", vbExclamation

End If

Else

.AllDayEvent = False
If IsDate(strStartDate) And IsDate(strEndDate) And IsDate(strStartTime) And IsDate(strEndTime) Then
.Start = DateValue(strStartDate) & " " & TimeValue(strStartTime)
.End = DateValue(strEndDate) & " " & TimeValue(strEndTime)

.Save
counter = counter + 1

Else

MsgBox "Termin mit dem Betreff: '" & strSubject & "' in Zeile " & cell.Row & " hat ungültige oder fehlende Zeitangaben", vbExclamation

End If
End If

End With

Next
Set objOL = Nothing
MsgBox counter & " Termin(e) wurden erstellt!", vbInformation
End Sub