Required Field

[tbrynard01]

I have the following code in a button and would like to add the code to check for required fields on the spreadsheet before it sends the email and to not send unless all the required fields are required, but not sure where to put it in this code - Thank you!:

Sub Email2()


' Keyboard Shortcut: Ctrl+Shift+S

'

    Range("A3").Select

    Selection.End(xlDown).Select

    ActiveWorkbook.Save

 

Dim outlook As Object

Dim newEmail As Object

Dim xInspect As Object

Dim pageEditor As Object


Set outlook = CreateObject("Outlook.Application")

Set newEmail = outlook.CreateItem(0)


With newEmail

    .To = Sheet8.Range("B21").Text

    .CC = Sheet8.Range("G21").Text

    .BCC = ""

    .Subject = "MHR Request"

    .Body = ""

    .display

 

    Set xInspect = newEmail.GetInspector

    Set pageEditor = xInspect.WordEditor

 

    Sheet8.Range("A1:F20").Copy

 

    pageEditor.Application.Selection.Start = Len(.Body)

    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start

    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)

    .display

    .Send

    Set pageEditor = Nothing

End With


Set newEmail = Nothing

Set outlook = Nothing

  

 

Call ClearForm


End Sub

 

[mumps]

What are the required fields?

 

[tbrynard01]

What are the required fields?

I have several of then C3, C4, C5

Thank you!

 

[JoeMo]

I'm assuming the required cells are on the sheet the macro is run from. Immediately after the Sub line add this block.

Dim reqdRng
Set reqdRng = Range("C3:C5")
If Application.CountA(reqdRng) <> reqdRng.Count Then
    MsgBox "All cells in range: " & reqdRng.Address(0, 0) & " must have entries. Please comply then run this macro again."
    Exit Sub
End If

 

[tbrynard01]

Thank you so much

 

[JoeMo]

You are welcome - thanks for the reply.