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 = "
.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


--
tqdinh22
------------------------------------------------------------------------
tqdinh22's Profile: http://www.excelforum.com/member.php...o&userid=36453
View this thread: http://www.excelforum.com/showthread...hreadid=573184

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"tqdinh22" wrote in message
...

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 = "
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


--
tqdinh22
------------------------------------------------------------------------
tqdinh22's Profile: http://www.excelforum.com/member.php...o&userid=36453
View this thread: http://www.excelforum.com/showthread...hreadid=573184

Hi,

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

Thx


--
tqdinh22
------------------------------------------------------------------------
tqdinh22's Profile: http://www.excelforum.com/member.php...o&userid=36453
View this thread: http://www.excelforum.com/showthread...hreadid=573184

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 = "
.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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"tqdinh22" wrote in message
...

Hi,

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

Thx


--
tqdinh22
------------------------------------------------------------------------
tqdinh22's Profile: http://www.excelforum.com/member.php...o&userid=36453
View this thread: http://www.excelforum.com/showthread...hreadid=573184