Search code examples
excelvbacalendaremail-attachmentsappointment

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 = "[email protected]"
     .OptionalAttendees = "[email protected]"
     .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 = "[email protected]"
        .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 = "[email protected]"
            .OptionalAttendees = "[email protected]"
            .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