E-mail macro - Field Validation

[tqdinh22]

All,

I currently have a requisition form used in order to provide
information to our admin person to log in and have purchase req's
approved. I am using a button assigned with a macro to have it
automatically send the sheet as an e-mail attachment (using outlook) to
the admin person. Is there a way to have certain fields or cells
required to be filled in before the macro works, perhaps giving an
error message of some sort?

Thanks,

FOLLOWING IS THE MACRO CURRENTLY ASSIGNED:

Sub Mail_ActiveSheet_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
..SaveAs "SubK PO Req.xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
..To = "(e-mail address removed)"
..CC = ""
..BCC = ""
..Subject = "New SubK PO Requisition"
..Body = "Please find attached PO Requisition form. Thank
you and have a nice day."
..Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
..Send 'or use .Display
End With
..ChangeFileAccess xlReadOnly
Kill .FullName
..Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Ron de Bruin]

You can use the Counta function in VBA to test if all cells have a value in the range

Example for A1, A3 and A5

If Application.WorksheetFunction.CountA(Range("A1,A3,A5")) = 3 Then
'mail code here
Else
MsgBox "Sorry"
End If

 

[tqdinh22]

Hi,

I tried the code you suggested however it now returns a message sayin
"invalid outside procedure," I don't know if I am entering it wrong...
am a beginner w/ VB self learning. Can you show how you woul
incorporate it into my code above?

Th

 

[Ron de Bruin]

Try this


Sub Mail_ActiveSheet_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String

If Application.WorksheetFunction.CountA(Range("A1,A3,A5")) = 3 Then

strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False

ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "Sorry"
End If

End Sub