Run-time error '424': Object required, email on button click with selected cell values

[mikura]

Please Help!
I included a code for send emails on button click with multiple cell values which comes in column A and B. These column are selected using dynamically generated checkboxes.
I’m able to create dynamic checkboxes but I failed to retrieve the cell values to a variable.
While I’m running the code debugger is giving a message "Run-time error '424': Object required".
I’m a beginner in excel vba. Please help me.

Sub previewmails()
Dim counter As Integer
Dim strSku As Long
Dim Mail As New Message
Dim Config As Configuration
Set Config = Mail.Configuration
If chkbx.Value = 1 Then
    counter = 1
For r = 1 To counter
        If Cells(r, 1).Top = chkbx.Top Then
                counter = counter + 1
       
                        strSku = Cells(Target.row, "B").Value & " (" & Cells(Target.row, "A").Value & "), "
                    Exit For
                End If
              
            Next r
        End If
   
                Config(cdoSendUsingMethod) = cdoSendUsingPort
                Config(cdoSMTPServer) = "smtp.gmail.com"
                Config(cdoSMTPServerPort) = 465
                Config(cdoSMTPAuthenticate) = cdoBasic
                Config(cdoSMTPUseSSL) = True
                Config(cdoSendUserName) = "test@mail.com"
                Config(cdoSendPassword) = "password@123"
                Config.Fields.Update
               
                Mail.To = "test@mail.com"
                Mail.From = Config(cdoSendUserName)
                Mail.Subject = "Email Subject"
                Mail.HTMLBody = "<b>" & strSku & "</b>"
               
               
                On Error Resume Next
               
                Mail.Send
               
                If Err.Number <> 0 Then
                MsgBox Err.Description, vbCritical, "There was an error"
                Exit Sub
                End If
                MsgBox "Your email has been sent!", vbInformation, "Sent"
                     
 
                End Sub

Any help much appreciated as ever!

Thanks

 

[wigi]

And where in the code do you fill that variable called Chkbx ?

 

[mikura]

I included "Dim chkbx As CheckBox" but now debugger is giving a message "Run-time error '91' :Object variable or block variable not set" and highlighting "If chkbx.Value = 1 Then".

 

[wigi]

Declaring is not enough. With declaring, your reserve a little bit of memory for that variable.
But you do not fill that variable. You do not assign that variable a valid checkbox. So it's null and you cannot work with it in your code (it gives an error because it's not assigned a checkbox object).

 

[mikura]

Ok. Now I modified the code with a "for each" function

Dim counter As IntegerDim strSku As Long


Dim Mail As New Message
Dim Config As Configuration
Set Config = Mail.Configuration


For Each chkbx In ActiveSheet.CheckBoxes
If chkbx.Value = 1 Then
counter = 1
For r = 1 To counter
If Cells(r, 1).Top = chkbx.Top Then
counter = counter + 1

strSku = Cells(Target.row, "B").Value & " (" & Cells(Target.row, "A").Value & "), "
Exit For
End If

Next r
End If
Next

And I tried to send "strsku" in the body of the email. But I received just "0".

 

[wigi]

Your counter variable is 1, so your inner loop will only be done for r = 1.
Is that correct?

 

[mikura]

Yes you are right.

I modified my for loop by "For r = 1 To Rows.Count". Now it is again showing Run-time error '424' and highlighting "strSku = Cells(Target.row, "B").Value & " (" & Cells(Target.row, "A").Value & "), ""

 

[wigi]

Mikura

And where do you define / fill the variable Target?

Again, you cannot use variables if you do not fill them...

Man, this is getting frustrated...