Create a meeting with attendees automatically from Excel-CodePudding

I am trying to create an event with a guest automatically when my macro is executed. I've been trying to create an event with a guest automatically during the execution of my macro, but it doesn't work.

I always get errors and the guest is never "invited".

It's probably a minor issue, but these are the two versions I've done so far.

Do you see an error that I haven't seen? Thanks

Version 1

Sub Reminder_ContactCustomer()

Set xOutApp = CreateObject("Outlook.Application")
myItem = myOlApp.CreateItem(1)
myItem.MeetingStatus = olMeeting
myItem.Subject = "TO DO "   Range("B25").Value   " CONTACT CUSTOMER"
myItem.Location = "OFFICE 1A"
myItem.Start = Range("H25").Value
myItem.Duration = 90

Set myOptionalAttendee = myItem.Recipients.Add("[email protected]")
myOptionalAttendee.Type = olOptional
myItem.Send

End Sub

Version 2

Sub Reminder_ContactCustomerV2()

Dim I As Long
Dim xRg As Range
Dim xOutApp As Object
Dim xOutItem As Object
Set xOutApp = CreateObject("Outlook.Application")
Set xRg = Range("B28:H28")
For I = 1 To xRg.Rows.Count
Set xOutItem = xOutApp.CreateItem(1)
Debug.Print xRg.Cells(I, 1).Value
xOutItem.Subject = "TO DO "   Range("C9").Value   " CONTACT CUSTOMER"
xOutItem.Location = "OFFICE 1A"
xOutItem.Start = xRg.Cells(I, 7).Value
xOutItem.AllDayEvent = True
xOutItem.Duration = 15
xOutItem.BusyStatus = 2
xOutItem.ReminderSet = True
xOutItem.ReminderMinutesBeforeStart = 15
xOutItem.Body = "Auto reminder added by the Excel Checklist"
Set myRequiredAttendee = xOutItem.Recipients.Add("[email protected]")
myRequiredAttendee.Type = olRequired
xOutItem.Sent
xOutItem.Save
Set xOutItem = Nothing
Next
Set xOutApp = Nothing


End Sub

CodePudding user response：

It worked when i tried it myself (using option explicit to uncover mispells etc)

Sub Reminder_ContactCustomer()

Dim myOlApp As Outlook.Application
Dim myItem As Outlook.AppointmentItem
Dim myOptionalAttendee As Outlook.Recipient
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(1)
myItem.MeetingStatus = olMeeting
myItem.Subject = "TO DO "   Range("B25").Value   " CONTACT CUSTOMER"
myItem.Location = "OFFICE 1A"
myItem.start = Range("H25").Value
myItem.Duration = 90

Set myOptionalAttendee = myItem.Recipients.Add("[email protected]")
myOptionalAttendee.Type = olOptional
myItem.Send

End Sub

you need Set in front of myItem = myOlApp.CreateItem(1) and consistent myOlApp (you used xOutApp at the beginning)