Outlook Appointment not sending

[Nous1970]

I have created a function to place appointments in my outlook calendar and this all works well.
The only issue is that it is not sending an email to the attendees and I can't seem to figure out why.
Even when I open the appointment, created in my calendar, the email address is there.
I have tried .display as well, however this just displayed the appointment but I still had to send it manually.
Any help would be appreciated.

The code is as followed.

CODE IS UPDATED AND IS WORKING CODE NOW!

Public Function UpdateOutlook()

    Dim oRS As DAO.Recordset
    Dim oOL As Outlook.Application
    Dim oAppoint As Outlook.AppointmentItem
    Dim sStart As String
    Dim Port As String
    Dim myRequiredAttendee As Outlook.Recipient
 
 
    Set oRS = CurrentDb.OpenRecordset("ShipVissitReportQ")
    Set oOL = New Outlook.Application
    Set oAppoint = oOL.CreateItem(olAppointmentItem)
 
    oRS.FindFirst "ShipsVisitID=" & CurID
    OutlookSubj = oRS.Fields("VesselName") & " - OCEANIC SERVICES VESSEL VISIT REQUEST. - " & oRS.Fields("PortName")
   
    With oAppoint
 
        If IsNull(oRS.Fields("VisitDate")) Then
            GoTo NoDate
        Else
            sStart = oRS.Fields("VisitDate") & " " & oRS.Fields("VisitTime")
        End If

        .Start = CDate(sStart)
        .Duration = 240
        .subject = OutlookSubj
     
        If Not IsNull(oRS.Fields("Remarks")) Then
            .Body = oRS.Fields("Remarks")
        End If
     
        Set myRequiredAttendee = .Recipients.Add("xxxxxxxx@gmail.com")
        myRequiredAttendee.Type = olRequired
     
        If IsNull(Forms!ShipVisitOverviewF!PortName) Then
            MsgBox "there is no port assigned"
        Else
            MsgBox "there is port assigned!!!!"
            .Location = oRS.Fields("PortName")
                oRS.Edit
                oRS!PortAss = True
                oRS.Update
        End If
     
        .Categories = "Oceanic-Services"
        .MeetingStatus = olMeeting 'this has been changed from olNonMeeting for it to work.
        .Save
     
        oRS.Edit
        oRS!GlobalAppointmentID = .GlobalAppointmentID
        oRS.Update
           
    End With
 
    oAppoint.Send

    GoTo EndFunc
 
NoDate:
    MsgBox "No date entered"

EndFunc:
    oRS.Close
 
    Set oRS = Nothing
    Set oOL = Nothing
    Set oAppoint = Nothing

End Function

 

[Gasman]

Have you tried both? Display and Send as per this example?

AppointmentItem.Send method (Outlook)

Office VBA reference topic

learn.microsoft.com

 

[Nous1970]

Thank you for the reply.

Yes I did, and then it just openen but did not send is.
I still had to send it manually by pressing send

 

[Gasman]

Thank you for the reply.

Yes I did, and then it just openen but did not send is.
I still had to send it manually by pressing send

Are you able to send a simple email?

 

[Nous1970]

Yes that I have in a different function.
That works fine.

 

[Gasman]

Yes that I have in a different function.
That works fine.

I notice that example does not Save?
Try commenting that out, or place after the Send?

Other than that, I am out of ideas then, sorry.

 

[Nous1970]

No worries. Thank you for looking at it.

The .save is in the with statement.
I moved the .send out of the with statement as a last try to make it work.

But so far no luck

 

[Gasman]

You do not appear to have Option Explicit set?

Just tried your code with rs commented out and it fails.
OutlookSubj is not declared?

After fixing that it does not send, but does save.
When I go into the appointment I can save and send, but then it complains 'No recipients'?

I think that is your problem, you are not using the correct properties.?

Edit: Still not sending though.

 

[Nous1970]

Yes I have option explicit set

Option Compare Database
Option Explicit

The saving is not the issue.
The appointment gets into outlook.
Also the email address is in the appointment.
The only thing it doesn't do is the send part.

When I open the appointment and click on the scheduling assistant the email address is there or if I press invite attendees the email is also there as well.

I just doesn't send through the vba.
When I press send in outlook it does send as it should.

 

[Gasman]

So where is OutlookSubj declared?
Is that a Global?

Anyway, even when I got mine to run and use .Recipients, it still did not send, so unable to help, Sorry again.

Edit: FWIW the code from that link, modified to work (names, objects etc) works fine and puts it in the outbox.

Sub CreateAppt()
 Dim myItem As Object
 Dim myRequiredAttendee, myOptionalAttendee, myResourceAttendee As Outlook.Recipient
     Dim oRS As DAO.Recordset
    Dim oOL As Outlook.Application
    Dim oAppoint As Outlook.AppointmentItem
    Dim sStart As String
    Dim Port As String
    'Dim myRequiredAttendee As Outlook.Recipient
    
    
    'Set oRS = CurrentDb.OpenRecordset("ShipVissitReportQ")
    Set oOL = New Outlook.Application
    'Set oAppoint = oOL.CreateItem(olAppointmentItem)

 
 Set myItem = oOL.CreateItem(olAppointmentItem)
 myItem.MeetingStatus = olMeeting
 myItem.Subject = "Strategy Meeting"
 myItem.location = "Conf Rm All Stars"
 myItem.start = Now() + 0.25
 myItem.duration = 90
 Set myRequiredAttendee = myItem.Recipients.Add("xxxxxx@gmail.com")
 myRequiredAttendee.Type = olRequired
' Set myOptionalAttendee = myItem.Recipients.Add("Kevin Kennedy")
' myOptionalAttendee.Type = olOptional
' Set myResourceAttendee = myItem.Recipients.Add("Conf Rm All Stars")
' myResourceAttendee.Type = olResource
 myItem.display
 myItem.send
 
 Set oOL = Nothing
End Sub

 

[Nous1970]

Yes the subj is global

Thanks for the effort.

 

[Gasman]

Just updated my last post.

 

[Nous1970]

Just updated my last post.

Still not the desired result :-(

 

[Gasman]

Just for testing purposes, try the code I posted with a valid email address.
If that fails, try a repair of office.

 

[Nous1970]

I figured out where I went wrong, and it was actually very simple
I only had to change the meeting status.

.MeetingStatus = olNonMeeting

to

.MeetingStatus = olMeeting

and everything worked perfect now

Thanks to your code, I noticed where I went wrong.

 

[Gasman]

Not my code.
I copied that from that link I posted.
TBH I thought you had too as I saw very similar variables.

 

[Nous1970]

Thanks for the help @Gasman