Export Appointments from Access 2010 to Outlook 2010 with Required Attendee

experts,

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
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
LOL
Not asking for much are we? :)

Ok,  One required element that is missing here is what Calendar do you want to knock the appointment into?
So post that up.

Now,
First you need to either get Outlook, or fire it up.  That's done nicely this way
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
    Err.Clear
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
End If

Set FireOutlook = objOutlook
End Function

Open in new window


By self-containing the On Error within a small function, you keep it from causing grief elsewhere
The function passes back an Outlook.Application object to whatever called it.
Going forward, we'll need to set up the pre-req's for creating an appointment
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
Folder.Display

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

Open in new window


From here, it's just adding properties
With objOutlookAtmt
    .Display
    .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


Note that .Organizer is read-only.
Presumably, it is whoever fired up the code
Hope this helps

Nick67
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
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.
0
Nick67Commented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

pdvsaProject financeAuthor Commented:
Nick,

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?
0
Nick67Commented:
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#
0
pdvsaProject financeAuthor Commented:
Nick,

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
    Err.Clear
    ' 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
Folder.Display

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

With objOutlookAtmt
    .Display
    .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

0
Nick67Commented:
No.
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
0
pdvsaProject financeAuthor Commented:
With objOutlookAtmt
    .Display
    .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?
0
pdvsaProject financeAuthor Commented:
Where would I put .Body at?
0
pdvsaProject financeAuthor Commented:
and Category, Priority, Required Attendee?
0
Nick67Commented:
Sigh.
Have you add the reference?
You then will get Intellisense when you are a new . (period) in the with block
0
Nick67Commented:
Ok,
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
    .Display
    .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
0
pdvsaProject financeAuthor Commented:
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.
0
pdvsaProject financeAuthor Commented:
if there are spaces in the field names then I would bracket them I think.  ie: ReminderMinutesBeforeStart = rs![Reminder Minutes Before Start]
0
Nick67Commented:
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
    rs.movenext
loop


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


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


It's how you work with data in code.

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

Yes.
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
0
pdvsaProject financeAuthor Commented:
Thank you for your taking the time to explain that.  I appreciate it.  Maybe some day I will be good at this :)
0
Nick67Commented:
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.

Nick67
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.