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!

 

[StephenCrump]

I am guessing you want:

strTo = strTo & Cells(cel.Row, "j").Value & "; " with Cells referring by default to the ActiveSheet

rather than

strTo = strTo & .Cells(cel.Row, "j").Value & "; " with .Cells referring to the ListObject, and throwing an error because a ListObject doesn't have a .Cells property?

 

[Auri]

Okay so I replaced that but now I have this

i = i + 1

as an error for Overflow.

 

[Auri]

strTo = strTo & Cells(cel.Row, "j").Value & "; "
    i = i + 1
    Loop Until IsEmpty(Cells(cel.Row, "j"))
    
       '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

FYI, I have changed this part to something like this and my indicator column is not being indicated.

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

My indicator column. Thanks!

 

[StephenCrump]

I am not sure why you are looping again inside a For Each cel In Selection loop?

The overflow problem is to do with this line: Loop Until IsEmpty(.Cells(j, 1)) which I am guessing you may have replaced with Loop Until IsEmpty(Cells(1,"j")?

The test isn't changing with each loop, so eventually i will overflow when it exceeds the maximum integer size 32,767.

(As a general rule, you're better off declaring integers as Long, rather than Integer, but that won't fix your problem here)

 

[Auri]

I am looping since if the cell had more than 1 email inside. I am not sure if I doing it right. Thanks for the help!

 

[StephenCrump]

Let's say the Selection is C10:C12. Does that mean you want three reminders:

Reminder 1: Item in C10, deadline in F10, e-mail address(es) in J10, K10, L10 ... until empty
Reminder 2: Item in C11, deadline in F11, e-mail address(es) in J11, K11, L11 ... until empty
Reminder 3: Item in C12, deadline in F12, e-mail address(es) in J11, K12, L12 ... until empty

Do you need to refer to Table1?

 

[Auri]

nope. Just one reminder will do. And yes, I need to refer to table1 since my data is all inside.

 

[StephenCrump]

My post #7 was a guess. I'm not going to keep guessing.

If the Selection is a single cell, C10, say, what do you want to put in your reminder?

If the Selection is multiple cells, C10:C12, say, what do you want to put in your reminder?

Presumably your answer will relate somehow to Table1?

 

[Auri]

The reminder part I already have it done, I am stuck at the part where it doesn't take the emails from the cell.

This reminder must also be sending to the emails in the cell as an appointment. Thanks!