CODEWITHSUNDEEP
vbaexcel
krib
krib

Reputation: 569

failes to find "true" in mail to range macro

Got a macro from Ron de Bruin which I've been using, but now I can't get it to find the "TRUE" in the reference column. The "TRUE" is generated from checkboxes, it works if I write "yes" I've used the original code from his page, shown here:

Sub Test1()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2016
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "?*@?*.?*" And _ 'right value is being shown
       LCase(Cells(cell.Row, "D").Value) = "TRUE" Then 'suddenly skips this phase. 
                                            'Shows the right row but nothing happens anymore

        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
        With OutMail
            .To = cell.Value
            .Subject = "Reminder"
            .Body = "Dear " & Cells(cell.Row, "A").Value _
                  & vbNewLine & vbNewLine & _
                    "Please contact us to discuss bringing " & _
                    "your account up to date"
            'You can add files also like this
            '.Attachments.Add ("C:\test.txt")
            .Display  'Or use Display
        End With
        On Error GoTo 0
        Set OutMail = Nothing
    End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

If I remove And _ LCase(Cells(cell.Row, "D").Value) = "TRUE" it works like a charm. I hope someone can help me out with this.

Upvotes: 0

Views: 44

Answers (2)

user3598756
user3598756

Reputation: 29421

since the cell whose value you're querying is linked to a (ActiveX) checkbox its value is of a Boolean type

so you have to check it as such, that is:

If Cells(cell.Row, "D").Value Then  'means IF Cells(cell.Row, "D").Value = True

Upvotes: 1

3-14159265358979323846264
3-14159265358979323846264

Reputation: 3290

The following line is incorrect

LCase(Cells(cell.Row, "D").Value) = "TRUE" Then

you are converting the value to lowercase using the LCase function (eg true) ... you should change it to

UCase(Cells(cell.Row, "D").Value) = "TRUE" Then

Upvotes: 1

Related Questions