Hi All
I have a code I'm using in Excel to send orders via e-mail at the simple push of a button.
Problem is I can order sheets which have a zero stock to order and it looks pretty dumb to suppliers.
My main concern in when I accidentally place orders for wood based products. Is there a way to add to the code below an option where is B21 is "NO", the you get a message box to say this supplier is not required oe something?
Please help.
Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
Dim AWorksheet As Worksheet
Dim Sendrng As Range
Dim rng As Range
On Error GoTo StopMacro
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If MsgBox("Are you sure you want to sent the Wood Supplier order?", vbYesNo + vbQuestion) = vbNo Then GoTo StopMacro
'Fill in the Worksheet/range you want to mail
'Note: if you use one cell it will send the whole worksheet
Set Sendrng = Worksheets("Wood Supplier").Range("A1:F27")
'Remember the activesheet
Set AWorksheet = ActiveSheet
'Create the mail and send it
With Sendrng
' Select the worksheet with the range you want to send
.Parent.Select
'Remember the ActiveCell on that worksheet
Set rng = ActiveCell
'Select the range you want to mail
.Select
' Create the mail and send it
ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope
' Set the optional introduction field thats adds
' some header text to the email body.
.Introduction = Range("Q1").Value
With .Item
.To = ""
.CC = ""
.Subject = Range("Q2").Value
.Send
End With
End With
'select the original ActiveCell
rng.Select
End With
'Activate the sheet that was active before you run the macro
AWorksheet.Select
StopMacro:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False
End Sub
Bookmarks