UtterAccess.com
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    
 
   
DHillier
post Jun 14 2019, 07:58 AM
Post#1



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.
Else
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

.Save
.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

Add_Err:
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"
Else
Set olkSes = olkApp.Session
End If
Set olkFolder = olkSes.GetDefaultFolder(olFolderCalendar)
olkFolder.Display
Set olkApp = Nothing
Set olkSes = Nothing
Set olkFolder = Nothing
=====

Your assistance on this would be greatly appreciated.

Kind Regards
D
Go to the top of the page
 
DHillier
post Jun 14 2019, 08:18 AM
Post#2



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]
D
Go to the top of the page
 
DanielPineault
post Jun 14 2019, 09:00 AM
Post#3


UtterAccess VIP
Posts: 6,710
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
 
ADezii
post Jun 14 2019, 09:41 AM
Post#4



Posts: 2,457
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.
    CODE
    objAppt.Display
  3. Do NOT Quit Outlook and set the Object Variable to nothing withing the Local Procedure:
    CODE
    'appOut.Quit
    'Set appOut = Nothing

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



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


On your objAppt line it varies from mine that works which is
CODE
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
 
ADezii
post Jun 14 2019, 01:02 PM
Post#6



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


QUOTE
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
 
DHillier
post Jun 19 2019, 03:54 PM
Post#7



Posts: 9
Joined: 9-November 17



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

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


Custom Search


RSSSearch   Top   Lo-Fi    17th July 2019 - 03:54 AM