Solved

Export Appointments from Access 2010 to Outlook 2010 with Required Attendee

Posted on 2014-11-25
18
256 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
  • 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you don't know how to downgrade, my instructions below should be helpful.
Learn to move / copy / export exchange contacts to iPhone without using any software. Also see the issues in configuration of exchange with iPhone to migrate contacts.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now