+ Reply to Thread
Results 1 to 3 of 3

Confirmation Box when sending order or not allowed box depending on cell value

  1. #1
    Registered User
    Join Date
    06-13-2012
    Location
    Leicester, England
    MS-Off Ver
    MS Office 365
    Posts
    61

    Confirmation Box when sending order or not allowed box depending on cell value

    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

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Confirmation Box when sending order or not allowed box depending on cell value

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-13-2012
    Location
    Leicester, England
    MS-Off Ver
    MS Office 365
    Posts
    61

    Re: Confirmation Box when sending order or not allowed box depending on cell value

    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.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1