I have an Excel sheet that has a script attached to a button that will email
the form via outlook once the button is pressed. Anyway, is there any way to
make certain fields required before the email is sent and produce an error
message if those fields are left blank? Cells D13 and D14 would be the ones
in question. I have my code below. Thanks

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "LOA Notice - " & Sheets("STD-LOA").Range("d8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
if Range("D13") = "" or Range("D14") = "" then
msgbox "Missing Data, quitting"
exit sub
end if
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "LOA Notice - " & Sheets("STD-LOA").Range("d8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"Nash13" wrote:

I have an Excel sheet that has a script attached to a button that will email
the form via outlook once the button is pressed. Anyway, is there any way to
make certain fields required before the email is sent and produce an error
message if those fields are left blank? Cells D13 and D14 would be the ones
in question. I have my code below. Thanks

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "LOA Notice - " & Sheets("STD-LOA").Range("d8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Nash,

Add the following code at the beginning of your code

if Sheets("EmailAddresses").range("D13").value = "" or
Sheets("EmailAddresses").range("D14").value = "" then
msgbox "Can not sent, please enter all required field",vbcritical,"Error"
exit sub
end if

"Nash13" wrote:

I have an Excel sheet that has a script attached to a button that will email
the form via outlook once the button is pressed. Anyway, is there any way to
make certain fields required before the email is sent and produce an error
message if those fields are left blank? Cells D13 and D14 would be the ones
in question. I have my code below. Thanks

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "LOA Notice - " & Sheets("STD-LOA").Range("d8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Great. Thank you both. I took those examples and made D14 dependent on D13.
Awesome.

"Brotha Lee" wrote:

Nash,

Add the following code at the beginning of your code

if Sheets("EmailAddresses").range("D13").value = "" or
Sheets("EmailAddresses").range("D14").value = "" then
msgbox "Can not sent, please enter all required field",vbcritical,"Error"
exit sub
end if

"Nash13" wrote:

I have an Excel sheet that has a script attached to a button that will email
the form via outlook once the button is pressed. Anyway, is there any way to
make certain fields required before the email is sent and produce an error
message if those fields are left blank? Cells D13 and D14 would be the ones
in question. I have my code below. Thanks

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "LOA Notice - " & Sheets("STD-LOA").Range("d8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub