Adding conditions before a form can be e-mailed

[Godot60]

Hi,

I have written a VBA macro that will mail a form to an address. see below. I want to add a statement that will prevent the form form being emailed and give an error message if certain fields are not complete - A4, B6, C12. Any suggestions?

Sub Mail_small_Text_Outlook()


Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Title: " & Sheet1.Range("A4") & vbNewLine & _
"Department: " & Sheet1.Range("C4") & vbNewLine & _
"SBU: " & Sheet1.Range("B5") & vbNewLine & _
"Level: " & Sheet1.Range("B6") & vbNewLine & _
"Manager: " & Sheet1.Range("B144")


On Error Resume Next
With OutMail
.to = "name@emailaddress.edu"
.cc = ""
.BCC = ""
.Subject = "Successful Submission: " & Sheet1.Range("a4")
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Roxxien]

How about creating a validation section like this?

'Validation
If (A4 = "") And (B6 = "") And (C12 = "") Then
    MsgBox "please fill correctly before sending it"
    Exit Sub
End If

 

[Godot60]

When I add this it pops up the message even if the fields are complete. Does it matter that these fields are populated with drop down lists?

 

[Roxxien]

This was only an idea of how you could do it.

As I didn't know what kind of value you had, I couldn't do the proper test. I am just realizing that it should be "Or" instead of "And" in my previous reply.

I tested with some drop down list and if we select an option in the list it doesn't equal to "". So I don't understand why you get the message box if everything is filled.

 

[Engineer Joe]

Try this:

'Validation
If Range("A4").Value = "" Or  Range("B6").Value = "" Or Range("C12").Value = "" Then
    MsgBox "please fill correctly before sending it"
    Exit Sub
End If

Also, if you used the user's code above, you should have gotten an error message because I don't think you can refer to cells like this "A5". You have to say something like this: Range("A5"). I bet you didn't get an error because you did it between On Error Resume Next and On Error Goto 0.

 

[Roxxien]

Well, yeah I didn't test it myself.


Thanks Engineer Joe for pointing the missing Range("A4")........

 

[Godot60]

You nailed it Engineer Joe. It works perfectly now. Thanks to all for your help! Much appreciated.