Object doesn't support this property or method

[Auri]

Hi, I have encountered a problem whereby this line

strTo = strTo & .Cells(cel.Row, "j").Value & "; "

is causing an error. What I wanted this entire thing to do is when I select an item (column C) from the table, it is supposed to take the expiry date from column F and set it as a reminder, and also send it as an appointment by using the email from column J and send it to them. It should be allowed to take in multiple emails from that cell that will be sending to them.

Sub EnterInCalendar()
Dim xOutApp As Object, cel As Range
Dim olMailItm As Object
Dim iCounter As Integer
Dim strTo As String
Dim i As Integer

strTo = ""
i = 1

If Selection.Columns.Count > 1 Or Selection.Column <> 3 Then
    MsgBox "Select in column C only."
    Exit Sub
End If

Set xOutApp = CreateObject("Outlook.Application")
Set olMailItm = xOutApp.CreateItem(0) 'empty email
'newMail.RecipIents.Add(ToAddress)
'newMail.RecipIents.Add(ToAddress1)

For Each cel In Selection
    With xOutApp.CreateItem(1)
        .Subject = cel.Value
        .Start = cel(1, 4).Value + TimeValue("9:00:00")
        .End = cel(1, 4).Value + TimeValue("9:30:00")
        .ReminderSet = True
        .ReminderMinutesBeforeStart = 10080
        .BusyStatus = 5
        .Save
    End With
    
    'Using the email, add multiple recipients, using a list of addresses in column J.
   With ActiveSheet.ListObjects("Table1") 'change sheet name where list is kept.
    Do
    strTo = strTo & .Cells(cel.Row, "j").Value & "; "
    i = i + 1
    Loop Until IsEmpty(.Cells(j, 1))
    
       'Do additional formatting on the BCC and Subject lines, add the body text from the spreadsheet, and send.
       .To = strTo
       '.CC = test@company.com; test2@company.com
       .Subject = "Test"
       .Body = "Test Test"
       .Display
   End With
       
    Cells(cel.Row, "L") = "c"
Next

Set xOutApp = Nothing
Set olMailItm = Nothing
End Sub

Anyone who knows how to fix this, please help. Thank you!

 

[RoryA]

That's not my point. You don't need both an email item and an appointment item. You add recipients to the appointment item and then send that. You don't need the email at all.

 

[Auri]

So is it something like this?

Sub EnterInCalendar()
Dim xOutApp As Object, cel As Range
Dim strTo As String
Dim TestAttendee As Outlook.Recipient

strTo = ""

If Selection.Columns.Count > 1 Or Selection.Column <> 3 Then
    MsgBox "Select in column C only."
    Exit Sub
End If

Set xOutApp = Application.CreateItem(olAppointmentItem)

For Each cel In Selection

    With xOutApp.CreateItem(olAppointmentItem)

        xOutApp.Subject = cel.Value
        xOutApp.Start = cel(1, 4).Value + TimeValue("9:00:00")
        xOutApp.End = cel(1, 4).Value + TimeValue("9:30:00")
        xOutApp.ReminderSet = True
        xOutApp.ReminderMinutesBeforeStart = 10080
        xOutApp.BusyStatus = 5
        xOutApp.Save
    strTo = strTo & Cells(cel.Row, "j").Value '& "; "
    'Do additional formatting on the BCC and Subject lines, add the body text from the spreadsheet, and send.
    Set TestAttendee = olMailItm.Recipients.Add(strTo)
    TestAttendee.Type = olRequired
        xOutApp.Display
        xOutApp.Send
        
    End With

    Cells(cel.Row, "L") = "c"

Next

End Sub

 

[RoryA]

Your xOutApp line shouldn't change - it should still be:

Set xOutApp = CreateObject("Outlook.Application")

 

[Auri]

I have change it to something like this.

Sub EnterInCalendar()
Dim xOutApp As Object, cel As Range: Set xOutApp = CreateObject("Outlook.Application")
Dim myItem As Object: Set myItem = xOutApp.CreateItem(1)
Dim strTo As String
Dim myAttendee As Outlook.Recipient

strTo = ""

If Selection.Columns.Count > 1 Or Selection.Column <> 3 Then
    MsgBox "Select in column C only."
    Exit Sub
End If

For Each cel In Selection

    With myItem

        .Subject = cel.Value
        .Start = cel(1, 4).Value + TimeValue("9:00:00")
        .End = cel(1, 4).Value + TimeValue("9:30:00")
        .ReminderSet = True
        .ReminderMinutesBeforeStart = 10080
        .BusyStatus = 5
        .Save
        
    strTo = strTo & Cells(cel.Row, "j").Value '& "; "

    Set myAttendee = .Recipients.Add(strTo)
    'myAttendee.Type = olRequired
        
        .Display
        .Send
        
    End With

    Cells(cel.Row, "L") = "c"

Next

Set xOutApp = Nothing
Set myItem = Nothing

End Sub

 

[RoryA]

It looks to me like this line:

Set myItem = xOutApp.CreateItem(1)

should be inside your For loop.

As an aside, it's helpful if you tell us what is/isn't happening as well as posting your code.

 

[Auri]

It looks to me like this line:

Set myItem = xOutApp.CreateItem(1)

should be inside your For loop.

As an aside, it's helpful if you tell us what is/isn't happening as well as posting your code.

I see. Will try it out for me in the morning tomorrow. Currently, the email from the cell is taken in but I got to click invite attendees for the email to appear, and if there are multiple emails in that cell, lets say ex@co.com; ex2@co.com, even after I click invite attendees, it will not read the email till I use the outlook email suggestion “Do you mean by this email thingy”. Thanks for the reply so far, really appreciated!

 

[Auri]

Set myItem = xOutApp.CreateItem(1)

I tried adding into the loop but when I run the code,

.Subject = cel.Value

this line would be highlighted saying "Object variable or With block variable not set". I will try some other way in the meantime while waiting for a response. Thanks for helping a noob like me! ?

 

[Auri]

  myItem.Subject = cel.Value

I put like this in front, it works the same way as before, nothing much has changed.

 

[Auri]

I still got to click this

for this to appear

 

[Auri]

Before the top image appear, I got to select this in order to send it otherwise is not treated as an email, same goes for those multiple emails that I mentioned above