Code doesn't check for empty string
I'm not sure why my code is not outputting my message even though my table has an empty field. If I mailbox the value, it shows it as 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 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
source to share
Cell.Value <= Date + 30
will always return TRUE
when the cell is empty.
Move ElseIf IsEmpty(myName) Then
upElseIf 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: Below is the answer to your question in a comment.
If you don't want to run the code even if one cell is empty, you can use this code. This will check if count of cells = number of cells filled
. If they do not match, it means that one or more cells are 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
source to share