Sending an Outlook appointment from Access using VBA

Question

I send emails to engineers. I am trying to get Access to set a reminder in their Outlook calendar.

I found from https://access-programmers.co.uk/forums/showthread.php?t=209552 something that looked correct. I cut out the code which I didn't require and this is what is left.

Option Compare Database

Sub Outlook()
Dim obj0App As Object
Dim objAppt As Object
Set obj0App = CreateObject("outlook.Application")
Set objAppt = obj0App.CreateItem(olAppointmentItem)

With objAppt
.requiredattendees = EmailAddy.Value
.optionalattendees = ASMail.Value
.subject = "Training booked for " & " " & QualificationEmail.Value
.Importance = 2 ' high
.Start = STdate.Value & "Starting at" & " " & StTime.Value
.End = Edate.Value
.Location = Location.Value
.Reminderminutesbeforestart = 20160 'reminder set for two weeks     before     the event
.Body = "Training for" & " " & [QualificationEmail] & "." & vbNewLine &     "Any changes to this arrangement will be emailed to you. You will     recieve any confirmation for bookings nearer the time."
.Meetingstatus = 1
.responserequested = True
.Save
.display
.send
MsgBox "Appointment sent"
End With

End Sub

When I test the code .requiredattendee is causing a

run-time error 424 Object Required.

Why is VBA not recognising required, and optional attendees?

Note:
The parts which are declaring values which are; EmailAddy, ASMail, QualificationsEmail, STdate, StTime, Edate & Location all link to an Access database form, using Dlookup's on text boxes like the examples below.

=DLookUp("[Engineer Email]","[EngTrainForm]","'[Windows ID]=" & [Windoze] &     "'")
=[Forms]![Training_Admin]![Windows ID]
=DLookUp("[Area Of Work]","[EngTrainForm]","'[Windows ID]=" & [Windoze] &     "'")
=DLookUp("[ASM Email]","[EngTrainForm]","'[Area]=" & [Area] & "'")
=DLookUp("[OutlookMSG]![Qualification]","[OutlookMSG]","' [EngTrain]!    [Training Date Booked] =" & [EngDate] & "'")

When I step through, the olAppointmentItem = Empty and the code halts at .requiredattendees = EmailAddy.Value bringing up

Run-Time error 424, Object required.

If I add an on error resume next, and run through the code I get an email message with the Importance working as the Body details (accept the qualificationEmail).

The .requiredattendees = EmailAddy.Value on the watch list is saying that the Expression is not defined in context, and the context is OutlookCalander,Outlook.

Can you add into the question the part of your code which is declaring, and assigning a value to, the EmailAddy and ASMail objects. (And, I guess, the QualificationEmail, STdate, StTime, Edate, and Location objects.) — YowE3K
Please read this: Debugging VBA Code -- step through your code, inspect the variables. — Andre
And I strongly suggest to put Option Explicit at the top of each module. It enforces variable declaration and reports undeclared or misspelled variables/constants at compile time. To have this automatically in new modules, set the Require Variable Declaration option in the VBA Editor. This is really a must have for VBA development. — Andre

Kyoujin Kyoujin · Accepted Answer · 2017-06-01T09:58:23

Well after more trial and error, through the code I found that it wanted more declaring in Access, compared to Excel. In case anyone wants more info here is the code below:

Sub Outlook()
Dim obj0App As Object
Dim objAppt As Object
Dim EmailAddy As Object
Dim ASMail As Object
Dim QualificationEmail As Object
Dim STdate As Object
Dim StTime As Object
Dim Edate As Object
Dim Location As Object



Set obj0App = CreateObject("outlook.Application")
Set objAppt = obj0App.CreateItem(1) 'olAppointmentItem


With objAppt

.requiredattendees = Forms("EngTraining").EmailAddy.Value
.optionalattendees = Forms("EngTraining").ASMail.Value
.subject = "Training booked for " & " " &     Forms("EngTraining").QualificationEmail.Value
.Importance = 2 'high
.Start = Forms("EngTraining").STdate.Value & " " &     Forms("EngTraining").StTime.Value
.End = Forms("Engtraining").EngTrainsubform.EndDate.Value
'.Location = Location.Value
.ReminderMinutesBeforeStart = 20160 'reminder set for two weeks before     the event
.Body = "Training for" & " " &     Forms("EngTraining").QualificationEmail.Value     & "." & vbNewLine & "Any changes to this arrangement will be emailed to you.     You will recieve any confirmation for bookings nearer the time."
.Meetingstatus = 1
.responserequested = True
.Save
.display
.send
MsgBox "Appointment sent"
End With

End Sub

The only thing that hasn't worked yet is location, so I need to look a little more into this, but the bulk of it works now.

Sending an Outlook appointment from Access using VBA

1 Answers