X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Copying From Ms Access To Outlook Calendar Entry, Access 2016    
post Jun 14 2019, 07:58 AM

Posts: 9
Joined: 9-November 17

Good day,

I have managed to create a database program for the tracking of our fleet of vessels. Part of the tracking involves a day to day activity log and 'upcoming programs' log. The latter is for the capturing and tracking of long lead items such as vessel refits, audits, and the like.

It is normal practice to utilize the MS Outlook Calendar [we are in an Exchange environment] to create appointments that would be sent to an email list for distribution to all the members on that list. What I'd like to do is automate this process using a command button.

A user would create the necessary entry [at a minimum start date, start time, subject/data]. Pressing the command button would open the outlook calendar [if it wasn't already] and copy that data into a new calendar entry, and proceed no further. The user would then review the data and make any changes or add any attachments required and then sent it along for distribution. The issue I'm having is that the code I'm currently using creates the entry automatically without opening the outlook calendar.

To be clear I'd like the procedure to be: Command Button -> Open Calendar [if not already open] -> import required fields from form/table to new calendar entry -> Remain open for user to review/add other items if necessary -> User then saves and closes the calendar entry manually

The current code is as follows:

On Error GoTo Add_Err

'Save record first to be sure required fields are filled.
DoCmd.RunCommand acCmdSaveRecord

'Exit the procedure if appointment has been added to Outlook.
If Me!AddedtoOutlook = True Then
MsgBox "You have already added this activity to your Calendar"
Exit Sub
'Add a new appointment.
Dim objOutlook As Outlook.Application
Dim objAppt As Outlook.AppointmentItem
Dim objRecurPattern As Outlook.RecurrencePattern

Set objOutlook = CreateObject("Outlook.Application")
Set objAppt = objOutlook.CreateItem(olAppointmentItem)

With objAppt
.Start = Me!StartDate & " " & Me!Start
.Duration = Me!ApptLength
.Subject = Me!Appt
.End = Me!EndDate & " " & Me!ApptEnd

If Not IsNull(Me!ApptNotes) Then .Body = Me!ApptNotes
If Not IsNull(Me!ApptLocation) Then .Location = Me!ApptLocation
If Me!ApptReminder Then
.ReminderMinutesBeforeStart = Me!ReminderMinutes
.ReminderSet = True
End If

.Close (olSave)
End With
'Release the AppointmentItem object variable.
Set objAppt = Nothing
End If

'Release the Outlook object variable.
Set objOutlook = Nothing

'Set the AddedToOutlook flag, save the record, display a message.
Me!AddedtoOutlook = True
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Activity added to your Calendar"

Exit Sub

MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Exit Sub

I've searched the forums and none of the threads I've seen has code that leaves the calendar entry open. Right now I'm going on the assumption that I need to replace 'acCmdSaveRecord' with something different but I'm not sure just what to replace it with.

I've gotten as far as using the command button to open the calendar, but not the new entry. This code is:

Const olFolderCalendar = 9
Dim olkApp, olkSes, olkFolder
On Error Resume Next
Set olkApp = GetObject(,"Outlook.Application")
If TypeName(olkApp) = "Nothing" Then
Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNameSpace("MAPI")
'Change Outlook on the next line to the name of the default mail profile'
olkSes.Logon "Outlook"
Set olkSes = olkApp.Session
End If
Set olkFolder = olkSes.GetDefaultFolder(olFolderCalendar)
Set olkApp = Nothing
Set olkSes = Nothing
Set olkFolder = Nothing

Your assistance on this would be greatly appreciated.

Kind Regards
Go to the top of the page
post Jun 14 2019, 08:18 AM

Posts: 9
Joined: 9-November 17

On quick add on:

olAppointmentItem appears to be at least part of the solution. Maybe: Me.CreateItem(olAppointmentItem)

...or something [resumes digging]
Go to the top of the page
post Jun 14 2019, 09:00 AM

UtterAccess VIP
Posts: 6,773
Joined: 30-June 11

Take a look at http://www.devhut.net/2017/01/15/vba-add-a...tlook-calendar/ for a starting point.

Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc

All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
post Jun 14 2019, 09:41 AM

Posts: 2,541
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. Declare the Object Variable pointing to the Instance of Outlook in the Form's Code Module, not Locally.
  2. The Outlook Application does not have a Visible Property. You must invoke the Display Method of the AppointmentItem Object to make the Appointment, and subsequently the Outlook Window, visible after all Data has been inserted for the Appointment.
  3. Do NOT Quit Outlook and set the Object Variable to nothing withing the Local Procedure:
    'Set appOut = Nothing

This post has been edited by ADezii: Jun 14 2019, 09:51 AM
Go to the top of the page
post Jun 14 2019, 12:58 PM

Posts: 97
Joined: 19-April 16
From: Guelph, ON, Canada

On your objAppt line it varies from mine that works which is
Set objAppt = obj0App.CreateItem(1)
This could just be an assumed versus explicit syntax, but the rest of your code looks very similar.
Go to the top of the page
post Jun 14 2019, 01:02 PM

Posts: 2,541
Joined: 4-February 07
From: USA, Florida, Delray Beach

On your objAppt line it varies from mine that works which is...

It's only for illustration purposes, make your own substitutions.
Go to the top of the page
post Jun 19 2019, 03:54 PM

Posts: 9
Joined: 9-November 17

Thanks for the suggestions guys. I'll dive in and let you know how things go.

PS. Sorry for the long time between replies.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    21st August 2019 - 07:31 AM