Send E-mail based on Non-contiguous cells using SUB Button Clicl()

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    [INDENT]I have the following working code, I only need one E-mail if any or all of those cells are >0, instead of the 7 if all the cells are >0...I have tried to compile this code




    [/INDENT]

    Code
    If Worksheets("Tool Room List").Range("G546") > 0 Then ActiveWorkbook.SendMail Recipients:="jsmith@xxxxxxxx", Subject:="Tool Request", ReturnReceipt:=False   
   If Worksheets("Tool Room List").Range("G591") > 0 Then ActiveWorkbook.SendMail Recipients:="jsmith@xxxxxxxx", Subject:="Tool Request", ReturnReceipt:=False
   If Worksheets("Tool Room List").Range("G593") > 0 Then ActiveWorkbook.SendMail Recipients:="jsmith@xxxxxxxxxx", Subject:="Tool Request", ReturnReceipt:=False
   If Worksheets("Tool Room List").Range("G594") > 0 Then ActiveWorkbook.SendMail Recipients:="jsmith@xxxxxxxxxx", Subject:="Tool Request", ReturnReceipt:=False
   If Worksheets("Tool Room List").Range("G595") > 0 Then ActiveWorkbook.SendMail Recipients:="jsmith@xxxxxxxxxx", Subject:="Tool Request", ReturnReceipt:=False
   If Worksheets("Tool Room List").Range("G630") > 0 Then ActiveWorkbook.SendMail Recipients:="jsmith@xxxxxxxxxx", Subject:="Tool Request", ReturnReceipt:=False
   If Worksheets("Tool Room List").Range("G631") > 0 Then ActiveWorkbook.SendMail Recipients:="jsmith@xxxxxxxxxx", Subject:="Tool Request", ReturnReceipt:=False


    [INDENT]
    into this..


    Code
    If Worksheets("Tool Room List").Range("G546,G591:G595,G630:G631") > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False



    But as you experts know, it doesn't work,


    Thanks in advance for your assistance,


    Joe[/INDENT]

    Re: Send E-mail based on Non-contiguous cells using SUB Button Clicl()


    Close enough... give this a try:



    Code
    If WorksheetFunction.Sum(Worksheets("Tool Room List").Range("G546,G591:G595,G630:G631")) > 0 Then ActiveWorkbook.SendMail Recipients:="[email protected]", Subject:="Tool Request", ReturnReceipt:=False

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login