[SOLVED] create a training appointment and attach it in the notification email in outlook using excel vba

create a training appointment and attach it in the notification email in outlook using excel vba

I wrote a program that sends notifications to the trainees and I tried to write a code that creates an appointment (.ics) to remind them of the training course before one day of starting course and then attach the appointment (.ics) to the email so they can add it to their calendar, but the code doesn't work the error message that displays to me is " Object required "

Sub sendMail()


Dim ol As Outlook.Application
Dim olm As Outlook.MailItem

Dim Appoint As Outlook.AppointmentItem   ' create the appointment item

Set ol = New Outlook.Application


Set olm = ol.CreateItem(olMailItem)
Set Appoint = ol.CreateItem(olAppointmentItem)
    
''''''''''' create the appointment
    With olp

     .Subject = "test subject"
     .Location = "test location"
     .Start = "06/01/2020 05:30 PM"
     .End = "06/01/2020 06:30 PM"
     .RequiredAttendees = "testrequired@SPLonline.com.sa"
     .OptionalAttendees = "optional@splonline.com.sa"
     .Body = "test body"
     
     .SaveAs ActiveWorkbook.Path & "\test_subject.oft"
            


    End With
    
    With olp
    
        .Subject = Sheet1.Cells(r, 6).Value
        .Location = Sheet1.Cells(r, 15).Value
        .Start = Sheet1.Cells(r, 8).Value
        .End = Sheet1.Cells(r, 9).Value
        
        .RequiredAttendees = Sheet1.Cells(r, 4).Value
        .ReminderMinutesBeforeStart = 4320
        .OptionalAttendees = "optional@splonline.com.sa"
        .Body = "test body"
         
        .SaveAs ActiveWorkbook.Path & "\test_subject.ics", OlSaveAsType.olICal
         
    
    
       End With
    


    Set olm = Nothing
    

MsgBox "Notifications have been sent successfully", vbOKOnly + vbInformation, "Status"
End Sub

Solution

This worked for me:

Sub sendMail()

    Dim ol As Outlook.Application
    Dim olm As Outlook.MailItem
    Dim Appoint As Outlook.AppointmentItem, fName As String
    
    fName = ThisWorkbook.path & "\appointment.ics"
    
    Set ol = New Outlook.Application
    Set Appoint = ol.CreateItem(olAppointmentItem)
    With Appoint
        .Subject = "test subject"
        .Location = "test location"
        .Start = "06/01/2020 05:30 PM"
        .End = "06/01/2020 06:30 PM"
        .RequiredAttendees = "testrequired@SPLonline.com.sa"
        .OptionalAttendees = "optional@splonline.com.sa"
        .Body = "test body"
        .SaveAs fName, OlSaveAsType.olICal
    End With
    
    Set olm = ol.CreateItem(olMailItem)
    With olm
        .Subject = "subject"
        .Body = "test body"
        .Attachments.Add fName
        .Display
   End With
    
End Sub