VB Code - Validate/Verify Conditions are met before allowing Emails to be sent

[wcm69]

Hoping a smarter mind than mine can help out<o></o>
In my spreadsheet I have a dropdown list (Cell I12) with 2options: “Deliver to:” and “Collection”.<o></o>
I would like a VB Code to validate/verify a number of conditions/criteriabefore allowing an email order to be sent. <o></o>
The conditions/criteria that need to be met before allowingan email to be sent are:<o></o>
1) The (Dropbox) option: “Deliver to:” chosen and the address fields below (CellsI13:I17) are filled in.
Or if:
2) The (Dropbox) option: “Collection” chosen and the address fields below (Cells I13:I17) are not filled in / empty.<o></o>
Otherwise - the code should NOT allow an email to besent and exit the sub with a MsgBox -something like.[“Complete address fields”]. It would be great if the cursor goesback to the first blank/empty cell (but not important).<o></o>
I’m using the code below with varying results but haven’t beenable to get what I’m really after as. Unfortunately I’m still picking up VBA. Any help asusual is appreciated.

code:
Sub Emailit()<o></o>
IfRange("N13").Value = "" Then<o></o>
MsgBox "You havenot added a date"<o></o>
Exit Sub<o></o>
End If<o></o>
IfRange("G19").Value = "" Or Range("H19").Value ="" Or Range("I19").Value = "" Then<o></o>
MsgBox "Have youhave not added your order"<o></o>
Exit Sub<o></o>
End If<o></o>
Application.Dialogs(xlDialogSendMail).Show"someone@somewhere.com"<o></o>
Clear_Order<o></o>
End Sub<o></o>
Many thanks in advance <o></o>

 

[DushiPunda]

1) The (Dropbox) option: “Deliver to:” chosen and the address fields below (CellsI13:I17) are filled in.
Or if:
2) The (Dropbox) option: “Collection” chosen and the address fields below (Cells I13:I17) are notfilled in / empty.

Please clarify/provide some amplifying information:

1. If Deliver to is the option selected...then all cells in I13:I17 need to be filled in? Or do you want to check if at least one cell in I13:I17 has data in it?

2. If Collection is the option selected...then all cells in I13:I17 need to be blank?

 

[DushiPunda]

Here's what I came up with:

Sub Emailit()
If Range("N13").Value = "" Then
    MsgBox "You have not added a date."
    GoTo exitHandler
End If

If Range("G19").Value = "" Or Range("H19").Value = "" Or Range("I19").Value = "" Then
    MsgBox "You have not added your order."
    GoTo exitHandler
End If

If Range("I12").Value = "Deliver to:" Then
    If Not IsEmpty(Range("I13")) And Not IsEmpty(Range("I14")) And Not IsEmpty(Range("I15")) And Not IsEmpty(Range("I16")) And Not IsEmpty(Range("I17")) Then
        GoTo sendEmail
    Else
        MsgBox "You need to fill in the address fields."
        GoTo exitHandler
    End If
ElseIf Range("I12").Value = "Collection" Then
    If IsEmpty(Range("I13")) And IsEmpty(Range("I14")) And IsEmpty(Range("I15")) And IsEmpty(Range("I16")) And IsEmpty(Range("I17")) Then
        GoTo sendEmail
    Else
        MsgBox "You have data in the address fields. Please remove this data before continuing."
        GoTo exitHandler
    End If
ElseIf Range("I12").Value = "" Then
    MsgBox "Please make a selection in Cell I12 and proceed accordingly."
    GoTo exitHandler
End If

exitHandler:
Exit Sub

sendEmail:
'code to send email
Application.Dialogs(xlDialogSendMail).Show "someone@somewhere.com"
Clear_Order
Resume exitHandler
End Sub

Specifically regarding the code I added, the only issue I see you encountering is if any of the cells appear to be blank but are not actually blank (i.e. a space entered into the cell), then you could get some unintended results.