Solved

Export Appointments from Access 2010 to Outlook 2010 with Required Attendee

Posted on 2014-11-25
18
268 Views
Last Modified: 2014-11-25
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
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
18 Comments
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40465118
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
 

Author Comment

by:pdvsa
ID: 40465160
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40465181
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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:pdvsa
ID: 40465263
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40465297
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
 

Author Comment

by:pdvsa
ID: 40465317
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40465330
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
 

Author Comment

by:pdvsa
ID: 40465331
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
 

Author Comment

by:pdvsa
ID: 40465335
Where would I put .Body at?
0
 

Author Comment

by:pdvsa
ID: 40465341
and Category, Priority, Required Attendee?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40465349
Sigh.
Have you add the reference?
You then will get Intellisense when you are a new . (period) in the with block
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40465362
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
 

Author Comment

by:pdvsa
ID: 40465626
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
 

Author Comment

by:pdvsa
ID: 40465641
if there are spaces in the field names then I would bracket them I think.  ie: ReminderMinutesBeforeStart = rs![Reminder Minutes Before Start]
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40465886
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40465890
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
 

Author Closing Comment

by:pdvsa
ID: 40466121
Thank you for your taking the time to explain that.  I appreciate it.  Maybe some day I will be good at this :)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40466169
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

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This video demonstrates how to sync Microsoft Exchange Public Folders with smartphones using CodeTwo Exchange Sync and Exchange ActiveSync. To learn more about CodeTwo Exchange Sync and download the free trial, go to: http://www.codetwo.com/excha…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question