Outlook Appointment

[cdt540]

Let me start by saying I am not a VB programmer but have developed an Access database to track some info for our company. I have used info off this site in the past , creating access control for DB for example, but I have to keep it pretty simple in order to continue to support the app going forward.

What I would like to do is create a button on a form that reads the date from "Due Date" on the form and opens up a appointment in Outlook for that date at 8:00 AM so the user can complete the appointment by setting reminder, inviting individuals, then saving it in Outlook. It would be nice if after saving the appointment in Outlook the screen returned to Access and the opened form but not required. I have read a lot of posts on this site where the entire process is automated by VB code but that is more than I need or could support. Any help would be greatly appreciated.

Windows 7
Outlook 2010
Access 2010

Thanks

Chris

 

[Trevor G]

Welcome to the Forum, you would need VBA to do this. Indicated below is an example. On your Access Form go into Design View add a Command Button and then stop the Wizard 9Press Escape), open the Properties of the Command Button and change the following:

Name change to cmdOutApp
Caption change to Create Outlook Appointment

Next select the Events Tab and On Click Event, and select Event Procedure.

Add this code:

On Error GoTo StartError
Dim objOutlook As Outlook.Application
Dim objItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objItem = objOutlook.CreateItem(olAppointmentItem)
With objItem
.Subject = ContactName
.Start = StartTime 'Change this to Due_Date
End With
objItem.Display
Set objOutlook = Nothing
Exit Sub
StartError:
MsgBox "Error: " & Err & " " & Error
Exit Sub

This will then display the Outlook Appointment once completed and closed it returns you to the Access Form.

 

[cdt540]

I got the following error. It did not come thru on the snippet but line three the "objOutlook As Outlook.Application" is highlighted in blue. Thanks for your help with this.

Chris

 

[Trevor G]

In the VBA screen (Use Alt + F11) select the Tools Menu and References and then search down for Microsoft Outlook 12.Object Library and click the box save your changes and it should work for you.

 

[cdt540]

Thanks almost there. It now opens up the calendar appointment but is not reading field "Response_Due_Date" on form "Master4" and opening the appt for that date. It opens to "12/30/1899". The button running the code is also on form "Master4". The date field is in table "Master" if it makes more sense to look there. My code follows, what am I doing wrong?


Private Sub cmdOutApp_Click()
On Error GoTo StartError
Dim objOutlook As Outlook.Application
Dim objItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objItem = objOutlook.CreateItem(olAppointmentItem)
With objItem
.Subject = ContactName
.Start = StartTime 'Response_Due_Date
End With
objItem.Display
Set objOutlook = Nothing
Exit Sub
StartError:
MsgBox "Error: " & Err & " " & Error
End Sub


Thanks again for the help!

 

[cdt540]

I figured it out...thanks so much for your help! It now sets the appt for the "Due Date", inserts the "Complainant Name" in the subject line and sets the reminder for one day before.


Private Sub cmdOutApp_Click()
On Error GoTo StartError
Dim objOutlook As Outlook.Application
Dim objItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objItem = objOutlook.CreateItem(olAppointmentItem)
With objItem
.Subject = (Complaintant_Name)
.Start = (Response_Due_Date) + TimeValue("9:00:00")
.ReminderMinutesBeforeStart = 1440
End With
objItem.Display
Set objOutlook = Nothing
Exit Sub
StartError:
MsgBox "Error: " & Err & " " & Error
End Sub

 

[Trevor G]

Happy to help and thank you for letting me know you have a working solution.