Link to home
Start Free TrialLog in
Avatar of pdvsa
pdvsaFlag 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
Avatar of Nick67
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pdvsa


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
Avatar of pdvsa



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?
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#
Avatar of pdvsa



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
Avatar of pdvsa


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?
Avatar of pdvsa


Where would I put .Body at?
Avatar of pdvsa


and Category, Priority, Required Attendee?
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
Avatar of pdvsa


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.
Avatar of pdvsa


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
Avatar of pdvsa


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.