CODEWITHSUNDEEP
vbaexcel
Ed Briscoe
Ed Briscoe

Reputation: 117

Code isn't checking for empty string

I am unsure as to why my code isn't outputting my message even though the spreadsheet I have has an empty field. If I message box the value it shows it as being empty so not sure why it isn't picking it up in the IsEmpty function.

Sub Button1_Click()

    Dim Cell As Range
    Dim name As Range
    Dim objDate As Date
    Dim myName As String


    For Each Cell In Range("P3:P4").Cells

        myName = Cell.Offset(0, -14).Value

        If Not IsDate(Cell.Value) Then

            MsgBox "Please make sure all dates are in a valid form: DD.MM.YYYY"

        ElseIf Cell.Value <= Date + 30 Then

        **ElseIf IsEmpty(myName) Then

            MsgBox "Please enter a name"**

        Else

            Dim appOutlook As Outlook.Application
            Dim mitOutlookMsg As Outlook.MailItem
            Dim recOutlookRecip As Outlook.Recipient

' Step 1: Initialize an Outlook session.
            Set appOutlook = CreateObject("Outlook.Application")
' Step 2: Create a new message.
            Set mitOutlookMsg = appOutlook.CreateItem(olMailItem)
            With mitOutlookMsg
' Step3: Add the To recipient(s) to message.
                Set recOutlookRecip = .Recipients.Add(myName)
                recOutlookRecip.Type = olTo
'Set valid properties like Subject, Body, and Importance of the message.
                .Subject = "Test123"
'.Body = "Test"
                .BodyFormat = olFormatHTML
                .HTMLBody = "Dear " & myName & " TEST EMAIL "
                .Importance = olImportanceHigh 'High importance
' Resolve every Recipient's name
                For Each recOutlookRecip In .Recipients
                    recOutlookRecip.Resolve
                    If Not recOutlookRecip.Resolve Then
                        mitOutlookMsg.Display
                    End If
                Next
                .Send
            End With
            Set mitOutlookMsg = Nothing
            Set appOutlook = Nothing

            MsgBox "Emails have been sent"

        End If
        Next Cell


    End Sub

Upvotes: 3

Views: 78

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Cell.Value <= Date + 30 will always return TRUE when the cell is blank.

Move ElseIf IsEmpty(myName) Then before ElseIf Cell.Value <= Date + 30 Then

For example

If IsEmpty(myName) Then
    MsgBox "Please enter a name"
ElseIf Not IsDate(Cell.Value) Then
    MsgBox "Please make sure all dates are in a valid form: DD.MM.YYYY"
ElseIf Cell.Value <= Date + 30 Then

Else

EDIT: The section below answers your question in the comment.

If you do not want to run the code even if one cell is empty then you can use this code. This will check if count of cells = number of cells filled. If they are not the same then it means that one or more cell is empty.

Sub Sample()
    Dim rng As Range

    Set rng = Range("P3:P4").Cells

    If rng.Count <> Application.WorksheetFunction.CountA(rng) Then
        MsgBox "Please ensure that all cells are filled with dates in the range"
        Exit Sub
    End If

    '
    '~~> Rest of the code
    '
End Sub

Upvotes: 5

Related Questions