0

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
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
G. Koen
  • 33
  • 6
  • on which line do you get the error? – ashleedawg Oct 16 '18 at 06:54
  • 1
    Then Sheets("Sheet1") does not contain attribute `CheckBox1`. You should surely look to https://stackoverflow.com/questions/11741836/checking-if-a-worksheet-based-checkbox-is-checked – PilouPili Oct 16 '18 at 07:21
  • Check this answer as well for checkbox condition https://stackoverflow.com/questions/11991308/how-do-i-use-checkboxes-in-an-if-then-statement-in-excel-vba-2010 –  Oct 16 '18 at 07:52
  • Possible duplicate of [How do I use checkboxes in an IF-THEN statement in Excel VBA 2010?](https://stackoverflow.com/questions/11991308/how-do-i-use-checkboxes-in-an-if-then-statement-in-excel-vba-2010) –  Oct 16 '18 at 07:53

1 Answers1

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

Evanzheng
  • 191
  • 4