Avatar of pdvsa
Flag for United States of America asked on

Export Appointments from Access 2010 to Outlook 2010 with Required Attendee


What is the code to export an appointment with required attendees, optional attendees, and meeting organizer from Access 2010 to Outlook 2010?
I have the following fields in a table:  
1.      Subject
2.      Start Date
3.      Start Time
4.      End Date
5.      End Time
6.      All day event (yes, no)
7.      Reminder on/off  (true, false)
8.      Categories
9.      Show time as (free, tentative, busy, out of office, working elsewhere)
10.      Priority (high, low, normal)
11.      Description
12.      Meeting Organizer
13.      Required Attendees
14.      Optional Attendees

15.      Location

Note:  the required, optional attendees, meeting organizer are in email address format.  
I have been looking around EE and there are bits and pieces of what I am after but I am looking for the complete code.  

thank you
Microsoft AccessOutlookExchange

Avatar of undefined
Last Comment

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Nick: thank you sir. The thought did come to mind that it could be a little much to ask.  To answer your query in regards to which calendar, I guess all you need is the name of the calendar.  I do have the standard My Calendar and I do have another one that IT setup for me and that one is called "Compliance", which is the calendar I am interested in exporting to but I think I can simply copy and paste the records from one calendar to another quite easily.   I think the My Calendar default is ok.

Give it a whirl then if the default calendar is fine.

You can't split the start time & date
They need to be hashed full dates ie #25-Nov-14 12:31 PM#
For description -- is that the body of the appointment?
AllDay will be a .busystatus
Reminder with be .ReminderMinutesBeforeStart
I expect 0 may be no reminder
Priority will be .Importance

.Organizer cannot be set, it's read-only


thank you.
Question:  <They need to be hashed full dates ie #25-Nov-14 12:31 PM#
==>The format in Access needs to be like this?  Event with the time?  

<For description -- is that the body of the appointment?
Yes, the part down below the subject line.

I would combine all those pieces of code and put under a button and add references I guess to Outlook?  Which reference would it be?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

You showed
2.      Start Date
 3.      Start Time
 4.      End Date
 5.      End Time
One assumes they are coming from tables (although one should ALWAYS store Date & Time in a single field)
AppointmentItem only has .Start, .End, and .Duration for properties.
So if they are separate fields, you'll need to combine them.

Yes, a reference to the Microsoft Outlook XX.0 (11.0 for 2003, 15.0 for 2013) library is good.
You then get F1 help and Intellisense

Messing with it
.AllDayEvent = false
.Start = "11/25/2014 12:00:00 PM"
.Duration = 120
.End = "11/25/2014 2:00:00 PM"

worked correctly.
The help specified #11/25/2014 2:00:00 PM#


Is Description being mapped into Outlook?  I do not see this field in the code.

The entire code I shall place on an event under a button is as follows (copy of the 3 sections above)

Private Function FireOutlook() As Outlook.Application
On Error Resume Next
Dim objOutlook As Outlook.Application

Set objOutlook = GetObject(, "Outlook.Application")
'MsgBox Err.Number & " " & Err.Description
If Err.Number = 429 Then
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
End If

Set FireOutlook = objOutlook
End Function

Dim objOutlook As Outlook.Application
Dim objOutlookAtmt As Outlook.AppointmentItem
Dim objOutlookExplorers As Outlook.Explorers

Set objOutlook = FireOutlook()
Dim ns As Outlook.Namespace
Dim Folder As Outlook.MAPIFolder
Set ns = objOutlook.GetNamespace("MAPI")
Set Folder = ns.GetDefaultFolder(olFolderCalendar)
Set objOutlookExplorers = objOutlook.Explorers
objOutlook.Explorers.Add Folder

' Create the message.
Set objOutlookAtmt = objOutlook.CreateItem(olAppointmentItem)

With objOutlookAtmt
    .Subject = "The Appointment"
    .Start = #11/25/2014 11:59:00 AM#
    .End = #11/25/2014 11:59:00 PM#
    .AllDayEvent = True
'and all the other things you want  
End With

Open in new window


The first snippet is a standalone function that returns an object.
The second and third snippets can be combined under a single button.
.Description does not exist.
.Body does.
Throw your description into .Body
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

With objOutlookAtmt
    .Subject = "The Appointment"
    .Start = #11/25/2014 11:59:00 AM#
    .End = #11/25/2014 11:59:00 PM#

==>the dates are hard coded though?

Where would I put .Body at?

and Category, Priority, Required Attendee?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

Have you add the reference?
You then will get Intellisense when you are a new . (period) in the with block

You said
I have the following fields in a table:  
 1.      Subject
 2.      Start Date
 3.      Start Time
 4.      End Date
 5.      End Time
 6.      All day event (yes, no)
 7.      Reminder on/off  (true, false)
 8.      Categories
 9.      Show time as (free, tentative, busy, out of office, working elsewhere)
 10.      Priority (high, low, normal)
 11.      Description
12.      Meeting Organizer
13.      Required Attendees
 14.      Optional Attendees
 15.      Location

So, I assumed you'd do something like
Dim rs as Recordset
set rs = CurrentDb.OpenRecordset("Select * from TheAppoinmentTable where TheAppoinmentID = " & SomeNumber, dbOpenDynaset, dbSeeChanges)  

The with block then would be
With objOutlookAtmt
    .Subject = rs!Subject
    .Start = format(rs!StartDate, "dd-mmm-yyyy") & " " & format(rs!StartTime, "hh:mm:ss AM/PM")
    .Duration = CSng(rs!EndTime) - CSng(rs!StartTime) *24 * 60 ' to get minutes from fractional day
    .End= format(rs!EndDate, "dd-mmm-yyyy") & " " & format(rs!EndTime, "hh:mm:ss AM/PM")    
    .AllDayEvent = rs!AllDayEvent
    .ReminderMinutesBeforeStart = rs!Reminderminutes
    .Categories = rs!Categories
    .BusyStatus = rs!ShowTime 'olBusy
    .Importance = rs!Priority 'olImportanceHigh
    .body = rs!Description
    .RequiredAttendees = rs!RequiredAttendees
    .OptionalAttendees = rs!OptionalAttendees
    .Location = rs!Location
End With

oh no not the Dim stuff.  I am far from a programmer.  

What is dbSeeChanges?  I dont follow that part of it and if I need to adjust something else or add this dbSeeChanges somewhere else in the db.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

if there are spaces in the field names then I would bracket them I think.  ie: ReminderMinutesBeforeStart = rs![Reminder Minutes Before Start]

Samples in mdb format are always good, you know :)
What is dbSeeChanges?
Optional, but good coding practice.
If you ever move the data to SQL Server, you'll need to have the option dbSeeChanges in place.
It tells Access and SQL Server that they should be watching to see if the record has changed between when you read it and when you go to change it
Going back afterward and finding every set rs = to add that change is tiresome.

Another habit to NEVER have is testing Booleans for -1 or 0 instead of True or False.
SQL Server Booleans are 0 and 1, and that makes for an unhappy bit of scutwork.

What is the code to export... BUT oh no not the Dim stuff.
You can't have one without the other LOL

The recordset is the fundamental building block of MS Access VBA.
You have:
Dim db as database
Dim rs as recordset
Dim SQL as string

Set db = CurrentDb
SQL = "Some select statement you copied straight from the query editor that will have the records you want to use in the code"
Set rs = db.OpenRecordset(SQL,dbOpenDynaset,dbSeeChanges)

from there, its get on your horse and ride.
If your recordset only has a single record in it then you can do stuff like
me.someunboundcontrol.value = rs!somefield
and so on

If you have lots of records you can do

Do until rs.EOF 'end of fields
    'do something with rs!somefield

If you want to add a record
with rs
    !SomeField = SomeValue
End with

if you need to change a record
with rs
    !SomeField = SomeNewValue
End with

It's how you work with data in code.


if there are spaces in the field names then I would bracket them I think.  ie: ReminderMinutesBeforeStart = rs![Reminder Minutes Before Start]

Highly annoying and eternal
That's why you never have spaces in fieldnames or object names.
Then you don't need the brackets
but Nick67 then all the labels I drag on to forms and reports have no spaces in them
That's why in the Table Designer each field has a caption property.
The caption becomes the label text -- unless it's blank in which case the fieldname is the default
Your help has saved me hundreds of hours of internet surfing.

Thank you for your taking the time to explain that.  I appreciate it.  Maybe some day I will be good at this :)

I post a LOT of samples.
Just looking through the history of my activities may provide you with a fair bit of insight about how MS Access VBA works.  Did you really get everything working?

Post a sample mdb!

We can keep at it until you grasp the shape of it.