Exporting Excel data to Outlook 2016 Calendar

Status
Not open for further replies.

Ron Legg

New Member
Outlook version
Outlook 2016 64 bit
Email Account
Exchange Server
Hi

I'm building up some VBA based on Diane's examples in
http://www.slipstick.com/developer/create-appointments-spreadsheet-data/ and making good progress. I'm using Excel 2016 and Outlook 2016.

Diane's export VBA from Excel to Outlook works fine with a newly created local calendar under 'My Calendars' and I believe this code identifies the default calendar:
Set CalFolder = olNs.GetDefaultFolder(olFolderCalendar)

I now need to export my Excel data to a Shared Calendar which I have added from the Address Book into my new shared calendar located in 'Other Calendars' and require the 'Set CalFolder' code to identify this.

When I try to run without this reference to the correct shared calendar I get this error:

Microsoft Visual Basic

Run-time error '-2147221233 (8004010f)';
The attempted operation failed. An object could not be found.

and in debug it highlights this line of code:
Set subFolder = CalFolder.Folders(arrCal)

There must be a way of referencing the shared calendar I have added to 'Other Calendars' from the Address Book and any help is much appreciated.

Thanks
 

Attachments

  • upload_2016-7-29_11-59-58.png
    upload_2016-7-29_11-59-58.png
    10.6 KB · Views: 807
  • upload_2016-7-29_12-1-43.png
    upload_2016-7-29_12-1-43.png
    10.4 KB · Views: 777
  • upload_2016-7-29_17-3-50.png
    upload_2016-7-29_17-3-50.png
    10.4 KB · Views: 880
Oh... and this

Run-time error '-2147221233 (8004010f)';
The attempted operation failed. An object could not be found.

and in debug it highlights this line of code:
Set subFolder = CalFolder.Folders(arrCal)

is only needed if you are importing into multiple subfolders of your calendar. To import into one calendar, remove that line and change
Set olAppt = subFolder.Items.Add(olAppointmentItem)
to
Set olAppt = calFolder.Items.Add(olAppointmentItem)


Or just use the second macro at Create Appointments Using Spreadsheet Data

Set CalFolder = olNs.GetDefaultFolder(olFolderCalendar) <== this needs changed to reference the shared calendar
i = 2
Do Until Trim(Cells(i, 1).Value) = ""
Set olAppt = CalFolder.Items.Add(olAppointmentItem)
 
Hi Diane

Thanks very much for getting back to me. I've tried all the suggested ways of connecting to my shared calendar and am currently using the second macro at Create Appointments Using Spreadsheet Data however I'm getting the 'An object could not be found' error when using:

Set Items = Session.GetDefaultFolder(olFolderCalendar).Folders("My Shared Calendar name").Items
Set Items = Session.GetDefaultFolder(olFolderCalendar).Parent.Folders("My Shared Calendar name").Items

upload_2016-8-2_15-18-38.png


When I try - Set CalFolder = olNs.GetDefaultFolder(olFolderCalendar) the macro works but it populates my personal calendar and not the shared calendar.

The shared calendar was set up by our ICT support and I added it from the Address Book. It is located in the same folder as my own personal calendar and the calendar group in named "My Calendars". I've also tried this in other calendar group folders in my profile without success.

Any more ideas please?

Thanks very much.
 
When I try - Set CalFolder = olNs.GetDefaultFolder(olFolderCalendar) the macro works but it populates my personal calendar and not the shared calendar.
That is correct as it references your default calendar.

The shared calendar was set up by our ICT support and I added it from the Address Book. It is located in the same folder as my own personal calendar and the calendar group in named "My Calendars". I've also tried this in other calendar group folders in my profile without success.
if it's a calendar in another mailbox, you need to use the method here - Working with VBA and non-default Outlook Folders and get the calendar's mailbox.

Code:
--snip--
Set objOwner = NS.CreateRecipient("shared-alias")
   objOwner.Resolve
-- snip --
Set newCalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
 
Hi Diane

I tried the new code and received this error:

upload_2016-8-2_21-51-36.png


upload_2016-8-2_21-53-5.png


This is the revised code in my latest attempt:

Public Sub CreateOutlookAppointments()
Sheets("Sheet1").Select
' On Error GoTo Err_Execute

Dim olApp As Outlook.Application
Dim olAppt As Outlook.AppointmentItem
Dim blnCreated As Boolean
'Dim olNs As Outlook.Namespace
'Dim CalFolder As Outlook.MAPIFolder
Dim Items As Outlook.Items

Dim NS As Outlook.Namespace
Dim objOwner As Outlook.Recipient

Dim newCalFolder As Outlook.MAPIFolder

Dim i As Long


Set NS = Application.GetNamespace("MAPI")
Set objOwner = NS.CreateRecipient("My shared calendar")
objOwner.Resolve

If objOwner.Resolved Then
'MsgBox objOwner.Name
Set newCalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
End If



On Error Resume Next
Set olApp = Outlook.Application

If olApp Is Nothing Then
Set olApp = Outlook.Application
blnCreated = True
Err.Clear
Else
blnCreated = False
End If

On Error GoTo 0

i = 2
Do Until Trim(Cells(i, 1).Value) = ""

Set olAppt = newCalFolder.Items.Add(olAppointmentItem)

With olAppt


'Define calendar item properties

.Start = Cells(i, 6) '+ Cells(i, 7) '+ TimeValue("9:00:00")
.End = Cells(i, 7) '+ Cells(i, 9) '+TimeValue("10:00:00")
.Subject = Cells(i, 2)
.Location = Cells(i, 3)
.Body = Cells(i, 4) + Chr(13) + Cells(i, 9)
.BusyStatus = olBusy
'.ReminderMinutesBeforeStart = Cells(i, 10)
.ReminderSet = False
.Categories = Cells(i, 5)
.RequiredAttendees = Cells(i, 11)
'.Importance = Cells(i, 12)
.Save

End With


i = i + 1
Loop
Set olAppt = Nothing
Set olApp = Nothing

Exit Sub

Err_Execute:
MsgBox "An error occurred - Exporting items to Calendar."

End Sub
 
in the VBA editor, did you set a reference to the Microsoft outlook object model under tools, references?
 
Hi

Yes, I did it right at the beginning of this project
 
Try moving this
Set NS = Application.GetNamespace("MAPI")
Set objOwner = NS.CreateRecipient("My shared calendar")
objOwner.Resolve

If objOwner.Resolved Then
'MsgBox objOwner.Name
Set newCalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
End If


to after you set the application and change the NS line


If olApp Is Nothing Then
Set olApp = Outlook.Application
blnCreated = True
Err.Clear
Else
blnCreated = False
End If


Set NS = olApp.GetNamespace("MAPI")
Set objOwner = NS.CreateRecipient("My shared calendar")
objOwner.Resolve

If objOwner.Resolved Then
'MsgBox objOwner.Name
Set newCalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
End If

 
Hi

Changed as suggested - getting closer I think.
Just getting this now:

upload_2016-8-3_0-21-46.png


upload_2016-8-3_0-22-58.png


Latest code


Public Sub CreateOutlookAppointments()
Sheets("Sheet1").Select

On Error GoTo Err_Execute

Dim olApp As Outlook.Application
Dim olAppt As Outlook.AppointmentItem
Dim blnCreated As Boolean
Dim NS As Outlook.Namespace
Dim objOwner As Outlook.Recipient
Dim newCalFolder As Outlook.MAPIFolder
Dim Items As Outlook.Items
Dim i As Long

On Error Resume Next
Set olApp = Outlook.Application

If olApp Is Nothing Then
Set olApp = Outlook.Application
blnCreated = True
Err.Clear
Else
blnCreated = False
End If

Set NS = olApp.GetNamespace("MAPI")
Set objOwner = NS.CreateRecipient("My shared calendar")
objOwner.Resolve

If objOwner.Resolved Then
MsgBox objOwner.Name
Set newCalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
End If


On Error GoTo 0

i = 2
Do Until Trim(Cells(i, 1).Value) = ""

Set olAppt = newCalFolder.Items.Add(olAppointmentItem)

With olAppt



'Define calendar item properties

.Start = Cells(i, 6) '+ Cells(i, 7) '+ TimeValue("9:00:00")
.End = Cells(i, 7) '+ Cells(i, 9) '+TimeValue("10:00:00")
.Subject = Cells(i, 2)
.Location = Cells(i, 3)
.Body = Cells(i, 4) + Chr(13) + Cells(i, 9)
.BusyStatus = olBusy
'.ReminderMinutesBeforeStart = Cells(i, 10)
.ReminderSet = False
.Categories = Cells(i, 5)
.RequiredAttendees = Cells(i, 11)
'.Importance = Cells(i, 12)
.Save

End With
Cells(i, 10) = "Exported"

i = i + 1
Loop
Set olAppt = Nothing
Set olApp = Nothing

Exit Sub

Err_Execute:
MsgBox "An error occurred - Exporting items to Calendar."

End Sub
 
Did the correct mailbox name come up when you set the owner?
MsgBox objOwner.Name

if a message box didn't come up then the name wasn't resolved.

Try adding a msgbox to let you know if it failed:

If objOwner.Resolved Then
MsgBox objOwner.Name
Set newCalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
else
msgbox "Failed!"
End If


I'm guessing it will fail.
 
Hi Diane

Yes your prediction is correct, it did fail and didn't resolve and therefore I'm thinking that maybe it's the way our ICT support set up the shared calendar. It looks like the code would work but the calendar is in some way badly configured. Are there any rules we need to be aware of when setting these up?

thanks.
 
None of the lines are red are they?
 
i think object names are not matching up -
Dim olApp As Outlook.Application
Dim olAppt As Outlook.AppointmentItem
Dim blnCreated As Boolean
'Dim olNs As Outlook.Namespace

then the namespace is set again - Set NS = olApp.GetNamespace("MAPI")

 
I'm still getting errors even after fixing the object names. I'm pretty sure its just something unique to calling outlook from excel, not anything specific to your system.
 
This is working for me - i created the resources in a test tenant earlier today and had to force the address book to sync before it would resolve objowner but it's working now.

Code:
Option Explicit
Public Sub CreateOutlookAppointments()
Sheets("Sheet1").Select
On Error GoTo Err_Execute

Dim olApp As Outlook.Application
Dim olAppt As Outlook.AppointmentItem
Dim blnCreated As Boolean
Dim olNs As Outlook.Namespace
Dim CalFolder As Outlook.MAPIFolder
Dim objOwner As Outlook.Recipient

Dim i As Long

On Error Resume Next
Set olApp = Outlook.Application

If olApp Is Nothing Then
Set olApp = Outlook.Application
blnCreated = True
Err.Clear
Else
blnCreated = False
End If

On Error GoTo 0
Set olNs = olApp.GetNamespace("MAPI")

Set objOwner = olNs.CreateRecipient("blue")
objOwner.Resolve

If objOwner.Resolved Then
'MsgBox objOwner.Name
Set CalFolder = olNs.GetSharedDefaultFolder(objOwner, olFolderCalendar)
Else
Exit Sub
End If

i = 2
Do Until Trim(Cells(i, 1).Value) = ""

Set olAppt = CalFolder.Items.Add(olAppointmentItem)

With olAppt

'Define calendar item properties
.Start = Cells(i, 5) + Cells(i, 6) '+ TimeValue("9:00:00")
.End = Cells(i, 7) + Cells(i, 8) '+TimeValue("10:00:00")
.Subject = Cells(i, 1)
.Location = Cells(i, 2)
.Body = Cells(i, 3)
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = Cells(i, 9)
.ReminderSet = True
.Categories = Cells(i, 4)
.Save
' For meetings or Group Calendars
' .Send
End With

i = i + 1
Loop
Set olAppt = Nothing
Set olApp = Nothing

Exit Sub

Err_Execute:
MsgBox "An error occurred - Exporting items to Calendar."

End Sub
 
Many thanks Diane

There were no red entries and I will ask my ICT guys to sync my address book as the code should now work following this.
 
You should be able to do it yourself - go to Send & Receive tab then Send and Receive Groups, Download Offline Address book. That is how i fixed it at least.
 
Hi, I followed that advice and downloaded the address book via the Send/Receive Groups option.

The VBA doesn't return any errors now however the message box indicates that the shared calendar from the address book still isn't resolved. I may be wrong but this issue could be the way that the shared calendar has been set up or maybe a permissions issue. I can certainly add diary entries manually within outlook however the VBA won't populate the diary from Excel. That being said, the original VBA does work on locally created calendars.
 
Resolving doesn't require permissions. Adding to the calendar does.

The only thing that would affect resolving (besides the entry not synced to the offline gal) is if it is visible. If its hidden, it won't resolve (and won't be in the offline gal).

How the calendar is added to outlook won't affect this code - it works with both mailboxes and shared folders.

Try using the default smtp address in this line
Set objOwner = olNs.CreateRecipient("blue")

I use the alias, but it should work with the display name too.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
L Error when exporting Sent Mail to Excel Outlook VBA and Custom Forms 6
B vBA for exporting excel file from outlook 2016 Outlook VBA and Custom Forms 3
D Reoccuring appointment issues when exporting Calendar to Excel Using Outlook 3
M Error Messages when exporting Outlook 2010 Contacts to a CSV (DOS) or Excel Using Outlook 2
H Exporting Outlook Forms to Access or Excel Outlook VBA and Custom Forms 2
D Exporting reports to Excel gives wrong numbers BCM (Business Contact Manager) 1
H Error in values when exporting report to Excel BCM (Business Contact Manager) 1
HarvMan Exporting IMAP OST file to PST Using Outlook 5
O Carriage Return - Line Feeds - exporting Calendar events Using Outlook 0
GregS Exporting contacts Using Outlook 1
J OLADD.FAE Error When Exporting Contacts Using Outlook 6
R Exporting recovered Deleted Items outside the Inbox Using Outlook 1
N Exporting IM Address field Using Outlook 2
E Exporting email (.pst) Using Outlook 8
T exporting contacts names reversed in CSV file Using Outlook 8
D EXPORTING MS EXCHANGE EMAILS to another platform Exchange Server Administration 10
R Exporting contacts from 2010 to 2013 Using Outlook 1
Connie Boyer Exporting Outlook calendar/find next repeating meeting Using Outlook 3
A Exporting email in outlook 2013 with Office 365 Using Outlook 1
Diane Poremsky Fix the Outlook Folder Type after Exporting an IMAP Account Using Outlook 0
V Exporting RSS Feed to OPML file Using Outlook 8
B BCM 2010: Exporting Notes to .CSV with Business Contacts? BCM (Business Contact Manager) 3
Commodore Exporting all contacts to VCF files possible? Using Outlook 4
C Security Question - How to prevent users exporting .iaf files Using Outlook 5
C How to get SMTP out of email address when exporting contacts? Using Outlook 2
P Exporting group members from Outlook address group Using Outlook 11
R Exporting and importing contacts in Outlook 2007 Using Outlook 1
C extra characters added to email when exporting Using Outlook 2
F Outlook 2007 BCM Exporting Files BCM (Business Contact Manager) 2
T Automating Exporting to pst file Outlook VBA and Custom Forms 3
G exporting files from BCM BCM (Business Contact Manager) 1
G exporting from BCM BCM (Business Contact Manager) 1
P OT: Need website like this one, but for Excel Using Outlook 0
kburrows Outlook Email Body Text Disappears/Overlaps, Folders Switch Around when You Hover, Excel Opens Randomly and Runs in the Background - Profile Corrupt? Using Outlook 0
M using excel to sort outlook appointment items Outlook VBA and Custom Forms 4
W Outlook 365 I am getting the "Either there is no default mail client" error when I try to send an email on excel Office 365 Using Outlook 1
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
F VBA to move email from Non Default folder to Sub folders as per details given in excel file Outlook VBA and Custom Forms 11
D Cannot populate certain UserProperties in Outlook from Excel Outlook VBA and Custom Forms 2
F Excel VBA to move mails for outlook 365 on secondary mail account Outlook VBA and Custom Forms 1
S Excel vba code to manage outlook web app Using Outlook 10
H Information from user defined field into Excel Outlook VBA and Custom Forms 7
S Excel VBA and shared calendar issue Outlook VBA and Custom Forms 3
N Extract Outlook emails to excel Outlook VBA and Custom Forms 2
M Extract all links from Outlook email, send to Excel Using Outlook 2
O Email not leaving Outbox when using Excel VBA to sync Outlook account Outlook VBA and Custom Forms 4
C Pull Outlook shared calendars items from Excel Outlook VBA and Custom Forms 4
S How to export urls from email to excel and check the status of the url ? Using Outlook 5
S save email from excel Outlook VBA and Custom Forms 1
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0

Similar threads

Back
Top