Is there a way to export calendar events from Access to Outlook?

I have a database that tracks information about a project. Among this information is a bid date and a bid time. I would like to write code that would enable me to export this information to Outlook and add it to the calendar.

Any ideas?

Scott

You can link to the Calendar table in Outlook or import it.

File, Get External Data, Select either Import or Link Tables,
Change the Files of Type to Outlook(), then the file should
be under the mailbox folder.

You can then access it like any other Access table.

HTH

Thanks!

Ok, I thought I had it.

I linked the table and it shows in my Access database as a linked table but I am unable to update any of the fields. When I try I get the following error:
"Field '<FieldName>' is based on an expression and cannot be edited."
How can I overcome this?

Below is the code I use

Private Sub Duration_DblClick(CANCEL As Integer)
Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem
Set outobj = CreateObject("outlook.application")
Set outappt = outobj.CreateItem(olAppointmentItem)
With outappt
.Start = Me![Visit Date] & " " & Me![Visit Time]
.Duration = Me!Duration
.Subject = Me!Name
If Not IsNull(Me![visit reason]) Then
.Body = Me![visit reason]


End If
.save
End With

Set outobj = Nothing

End Sub

This is fantasitic and just what I've been trying to find for days now. And on my prototype at home I did get the link and now the calendar table. BUT I'm like too dense. Where are you putting this code and is it moving the data from my database to my calendar or the data from my calendar to the database.

Cicero

ahh comeon guys, I don't know where to paste it and it would make my life grand.

Cicero

Sorry to be so long.

Depends on how your form is structured, but in general you can place it in the "on click" event of a command button or if you are dealing with a continous form, put in behind the "on double click" event of the record you want to add.

For instance, this code comes from a form, where the field [duration] has this code behind the "on double click". I display the form as a datasheet (looks like a table). I pick the appointment I want to send to outlook and double click on it.

If you were to have each appointment displayed on its own form, just put a command button and place this behind the on click event.

BTW, you selected Event procedure and click the ... button to open the VBA editor to paste it.

Daren

Okay, perhaps I'm just not getting this. I've linked my calendar and I've made my button, posted the code. Nothing happened, so I went into my client table assuming I need to match field names from my client table to my calendar table. However, I don't see a filed for appointment dates in my calendar table. The only time/date fields in the calendar are received, created and modified. Did I somehow pick up the wrong table?

Second question, my client table actually contains many dates for each client. So I've made a form that has all hearing dates. named PE1, PE2 etc.
Am I going to run into a problem with so many date fields in converting them from the form to outlook. Do I name each one the same to match the unknown field in the calendar?

Cicero

It might help if you posted a copy of your database.

What fields are you sending to outlook? i.e. what fields will become the start, duration, subject etc. Also check to make sure you have the outlook 9.0 (or 10.0) reference installed.

When I try this, the table is set up as for e-mail messages, but it is a calendar folder in outlook. I am using Access97 and Outlook 9.0.

Any thoughts???

Blair

okay, I have a LName (Last Name) field I would want the Subject and then I have multiple appt dates per client (can't post the DBase as client confid). I guess what I'm wanting to know is do I have to name the field LName ...Subject instead etc. and if that is the case how can I get multiple appt dates to match with endtime etc.

and what is a 9.0 or 10.0 reference.

Cicero

The 9.0 and 10.0 numbers relate to the version of MS Office involved:

8.0 = Office 97 (and thus Access and Outlook and ...)
9.0 = Office 2000
10.0 = Office XP (also sometimes referred to as Office 2002)
11.0 = Office 2003 (so I am told, I don't have a copy of it.)

G.

okay I'll ask again, do I need to rename the fields in my dBase to match the fields in Outlook? Right now I have appt dates set as short dates so there are no time in the dBase. So common sense wise I assume I need to change those to a date/time format? and if I change them to that format then double click on them do I have to have a subject field to feed the data to outlook. I would also like for them to highlight a color set to specific parameters per each client. Is that possible?

No, you do not need to rename fields in your dbase to match the fields in Outlook. But you do need to assign the rst (recordset) with the correct db field name. See where below. You can include a form that prompts for date range or specific date (again, see below). Anyway, its kind of long, but it works. Also, I'm exporting from a query and using a form for date specific-ness, rather than a straight table. And, I have not been able to assign a color set to specific parameters yet, so if you figure it out, please let me know!

Here is the vb code I use to export appointments from Access to the default Outlook calendar:

'created in vb editor as a module
Function ExporttoOutlook()
Dim appOutlook As New Outlook.Application
Dim nms As Outlook.NameSpace
Dim fld As Outlook.MAPIfolder
Dim appt As Outlook.AppointmentItem
Dim itms As Outlook.Items
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim prm As DAO.Parameter
Dim lngCount As Long
Dim strStart As String
Dim strEnd As String

Set nms = appOutlook.GetNamespace("MAPI")
Set fld = nms.GetDefaultFolder(olFolderCalendar)
Set itms = fld.Items

'Get reference to data table.
Set db = CurrentDb

'Create recordset based on a query
Set qdf = db.QueryDefs("qrySchedule")
qdf.Parameters(0) = Forms!frmSchedule!cboSchedule_date

Set rst = qdf.OpenRecordset
rst.MoveLast
rst.MoveFirst
lngCount = rst.RecordCount
MsgBox lngCount & " records to transfer to Outlook"

'Loop through table, exporting each record to Outlook
Do Until rst.EOF
Set itm = fld.Items.Add("IPM.Appointment")
itm.Subject = rst!Address & " " & rst![City] & " " & rst![Builder] & " " & rst![Model]
itm.Location = rst!City
itm.Body = rst![Builder] & " " & rst![Supervisor] & " " & rst![Model] & " " & rst![Closing_Date]
itm.Duration = 1440
itm.Start = rst!Date_Scheduled
itm.Close (olSave)

rst.MoveNext
Loop
rst.Close

I wrote this based on sample code from H Feddema's Access 2002 Inside Out p. 719 and some code I found on the internet. feddema's sample code shows how to concatenate date and time into a single expression for start and end fields, so you may want to take a look and tweak to your specific needs.