Email based on checkbox - If function

Question

I'm trying to send an automated mail based on whether a checkbox is checked.

The code works perfectly without the If function. But with it, I get:

Error 438: Object doesn't support this property or method.

I'd rather keep the If function so the mail only gets sent by checking the box. Without the If function, the mail gets sent when unchecking as well.

Sub Checkbox1_Click()
    Dim OutLookApp As Object
    Dim Mail As Object
    Dim subject_ As String
    Dim body_ As String
    subject_ = "Something"
    body_ = "Something else"
    If Sheets("Sheet1").CheckBox1.Value = True Then
        Set OutLookApp = CreateObject("Outlook.Application")
        Set Mail = OutLookApp.CreateItem(0)
        Application.DisplayAlerts = False
        With Mail
            .Subject = subject_
            .Body = body_
            .To = "email"
            .CC = "otheremail"
            .Importance = 2
            .Send
        End With
        Application.DisplayAlerts = True
    End If
End Sub

Then Sheets("Sheet1") does not contain attribute CheckBox1. You should surely look to stackoverflow.com/questions/11741836/… — PilouPili
– PilouPili, Commented Oct 16, 2018 at 7:21
Check this answer as well for checkbox condition stackoverflow.com/questions/11991308/… — user9167318
– user9167318, Commented Oct 16, 2018 at 7:52
Possible duplicate of How do I use checkboxes in an IF-THEN statement in Excel VBA 2010? — user9167318
– user9167318, Commented Oct 16, 2018 at 7:53

Evanzheng · Accepted Answer · 2018-10-17 09:04:17Z

0

You can try using the ActiveSheet.OLEObjects ("CheckBox1"). Object.Value> 0 as condition to check it. For more information, please see the following links:

Using Control Names with the Shapes and OLEObjects Collections

Checking if a worksheet-based checkbox is checked

edited Oct 17, 2018 at 9:04

answered Oct 17, 2018 at 3:15

Evanzheng

2111 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Email based on checkbox - If function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related