type mismatch 13

[LMjdm]

Hey Guys,

Im having a slight issue with a type mismatch

Do While placeHolder <> 0
                
                    placeHolder = placeHolder - 1
                    
                    [COLOR=#ff0000]Set CAMX = Sheets("Distribution").Range("D" + contactCounter)[/COLOR] 'declared as a variant.. 'if there is a better type please let me know
                    
                    contactCounter = countactCounter + 1
                    
                    Application.ScreenUpdating = False
                
                    If camWelcomeMessage = 0 Then
                        message = "CAMs, the following is a reminder for the upcoming business rhythm:"
                        Set lync = Messenger.InstantMessage(CAMX)
                        lync.SendText (message)
                    End If

                    message = dayPlan.Offset(0, 2)
                    Set lync = Messenger.InstantMessage(CAMX)
                    lync.SendText (message)
                
                    If dayPlan.Offset(0, 3) = 0 Then
                        message = "As of: Today, " & asOf
                    ElseIf dayPlan.Offset(0, 3) = -1 Then
                        message = "As of: Yesterday, " & asOf
                    Else
                        message = "As of: " & asOf
                    End If
                
                    lync.SendText (message)
                
                    If dayPlan.Offset(0, 4) = 1 Then
                        message = "Due: Close of business, " & dueDay
                    ElseIf dayPlan.Offset(0, 4) = 2 Then
                        message = "Due: Tomorrow, " & dueDay
                    Else
                        message = "Due: " & dueDay
                    End If
                
                lync.SendText (message)
                
                On Error Resume Next
                lync.Close
                Application.ScreenUpdating = True
                
              Loop

Basically what the code is doing is referring to a contact distribution list(emails), and everytime it uses one it wants to go to the one under it, then use that one . It will repeat this process as many times as there are contacts using a separate counter(within the distribution sheet, it basically just counts how many emails are in the list and then feeds it into the code).

I was wondering if the syntax for the statement in red was incorrect. I can include more code if necessary however I wanted to keep it to the point.

Many thanks,

LMjdm

 

[AlphaFrog]

You could declare CAMX as type Range, but that won't fix your problem.

What is the value of contactCounter when it errors? If it's zero, that might throw the error.

 

[LMjdm]

it starts at 6 and awesome I know variant is not always the best use of memory because it it general

 

[AlphaFrog]

Does it throw an error on D6 or somewhere after? What is the value of contactCounter when it errors?

 

[LMjdm]

It never makes it through the loop. It immediately errors when it hits the highlighted line. So I set it to begin at 6 because D6 is where the first contact name would appear. The idea is to get it to cycle though a loop a send the same message to ever user in the contact list.

So to answer your second question, I set contactCounter to 6 and when stepping through the program does seem to recognize this.

The value in D6 of the Distribution sheet is (for sensitivity purposes) xxxxxxx.x.xxxxxxx@xxx.com

 

[Rick Rothstein]

What is the variable "countactCounter" set to before the loop starts?

 

[Andrew Poulsom]

Why are you using the Set keyword. That's for objects.

 

[LMjdm]

@rick rothstein it is set to be an integer

@andrew poulsom good point, I was confused with the variant type ive been looking into it more, but I appreciate the tip. Ill remove the set.

 

[AlphaFrog]

Try this...

Set CAMX = Sheets("Distribution").Range("D" & contactCounter)

 

[LMjdm]

@alphafrog bingo! that did the trick. I removed the set function since it is a variant. That wont affect anything will it? Could I set CAMX as a range if the it is suppose to hold an email address as a value?