Import Excel Text into Outlook Calender

Status
Not open for further replies.

BryanG

Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
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
 

Attachments

  • Import.PNG
    Import.PNG
    53.7 KB · Views: 551
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.
 
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!
 

Attachments

  • Error1.PNG
    Error1.PNG
    54.5 KB · Views: 595
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.
 
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
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
D ISOmacro to extract active mail senders name and email, CC, Subject line, and filename of attachments and import them into premade excel spread sheet Outlook VBA and Custom Forms 2
T Outlook Calendar 2016 import Excel Using Outlook 1
ogodt Change Default contact form and import from Excel 2010 Using Outlook 1
B Problem with import Excel BCM (Business Contact Manager) 1
K Excel Import Option: Need Help Updating Existing Records BCM (Business Contact Manager) 0
C Way to import contacts from Excel into BCM Contacts? BCM (Business Contact Manager) 1
C New pc, new outlook, is it possible to import auto-complete emailaddress Using Outlook 4
T How to Export & Import GMAIL Contacts into Outlook 2021 ? Using Outlook 4
W Outlook 365 File access denied attempting to import .pst Using Outlook 6
S Import PST to Office 365 Using Outlook 1
C How to import Outlook calendar? Using Outlook 1
Z Import Tasks from Access Using VBA including User Defined Fields Outlook VBA and Custom Forms 0
J How to import many msg into different public folders in Outlook Outlook VBA and Custom Forms 7
K Disabling import/export button to restrict PST creation Using Outlook 3
I Outlook 365 - import/attach PST file that used POP3 Using Outlook.com accounts in Outlook 0
M Export-Import .pst file problems Using Outlook 2
N .pst archive from work will not open/import on Microsoft 365 Exchange Server Administration 0
M PST import from Outlook 2007 to 2010 - Address Book contacts all in 1 group Using Outlook 4
CWM550 Import Exchange Server Administration 1
R PST->Outlook.com (Import vs Drag-n-Drop methods Using Outlook.com accounts in Outlook 2
DoctorJellybean Import accounts\files Using Outlook 1
S Import contacts to a shared mailbox Outlook VBA and Custom Forms 2
P Import Categories from Outlook 2003 Using Outlook 8
avant-guvnor Import csv problem Using Outlook 7
T Outlook 2016 CSV Translator Import Error Using Outlook 6
Rupert Dragwater How to import contact list Using Outlook 15
C Import Outlook 2016 contacts into to: field Using Outlook 1
P Import an .ics file to a specific calendar Using Outlook 4
L Need to import multiple Outlook files to Office 365 Account Using Outlook 4
R ost file import Using Outlook 2
I Import Office theme .thmx Using Outlook 4
Diane Poremsky Batch Import Photos into Outlook Contacts Using Outlook 0
Diane Poremsky Can't import CSV or move Outlook items into EAS Accounts Using Outlook 0
Diane Poremsky How to Import Appointments into a Group Calendar Using Outlook 0
M How to Import YES/NO Checkboxes? BCM (Business Contact Manager) 0
J Converted .ost to .pst: Want to Import and Reconnect with IMAP Email Account Using Outlook 2
GregS Import from Outlook.com .ost to IMAP .pst? Using Outlook 3
Q Outlook 2016\365 export specific rules to import in another system Exchange Server Administration 1
Diane Poremsky Macro to Bulk Import Contacts and vCards into Outlook Using Outlook 0
Diane Poremsky Import Images into the Active Directory Using Outlook 0
M convert/import a customized record into the default "Account" record BCM (Business Contact Manager) 0
Diane Poremsky Macro to Bulk Import vCards into Outlook Using Outlook 0
e_a_g_l_e_p_i question about saving my .pst so I can import it to my Outlook after I build a new system Using Outlook 10
V Import from Outlook 2013 ost file? Using Outlook 2
E Want to Import Outlook 2003 pst files to later version Using Outlook 6
A Does Outlook import Gmail Archive? Using Outlook 1
E PDF Import Using Outlook 1
bhogesh How to import .htm saved mail to outlook Using Outlook 3
J Easy way to re-import Gmail folders/labels into Outlook? Using Outlook 9
J Opening/Archiving/Import/Export PST files Using Outlook 4

Similar threads

Back
Top