Triggered Message Box to run code

[Watchdawg]

Sorry if this has been addressed, I've seen things that come close but don't hit the mark.
I have a spreadsheet, column J indicates a Yes or No via formula to determine if a pump requires charging. What I need is prior to the worksheet closing, a message box should appear (if any cells in column J indicate "Yes") asking if you would like to send the email. I have code written to copy the appropriate information into an outlook email and the user hits the "Send" button on outlook.
How do I get this to happen prior to closing the workbook.

 

[My Aswer Is This]

First you said:

What I need is prior to the ​

worksheet​

closing

Then you said:

How do I get this to happen prior to closing the ​

​

workbook​

​

.

So do you mean Worksheet or Workbook?

And worksheets do not open or close.
They are active or not active
​

​

 

[Fluff]

How about

Private Sub Workbook_BeforeClose(Cancel As Boolean)

   With Worksheets("[COLOR=#ff0000]Sheet1[/COLOR]")
      If WorksheetFunction.CountIf(.Range("J:J"), "yes") > 0 Then
         If MsgBox("Do you want to send email?", vbYesNo) = vbYes Then Call [COLOR=#ff0000]emailMacro[/COLOR]
      End If
   End With
End Sub

Change values in red to suit

 

[Watchdawg]

Apologies, what I meant was Workbook

 

[JackDanIce]

Seems like you need a workbook event, copy following into your workbook object, adjust the code as fits your sheet:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Dim x           As Long
    Dim str(1 To 2) As String
    str(1) = "Pump(s) require changing, send mail?"
    str(2) = "Send Mail for Changing Pumps?"
    
    With Sheets("Sheet1")
        x = .Cells(.Rows.Count, 10).End(xlUp).Row
        If Application.CountIf(.Cells(1, 10).Resize(10), "Yes") And MsgBox(str(1), vbYesNoCancel, str(2)) = vbYes Then
            'code to send email
        End If
        Erase str
    End With

End Sub

 

[Watchdawg]

OK, I think I've been playing with this too long, I am now getting a lot of bugs in the email portion as well. I think I have muddied the waters sufficiently enough that I can't see daylight. Going to start clean on this one, you guys always offer up ideas that are clean and I'm able to learn from them...

Column I (1st Recharge Date) uses a formula to determine X number of weeks from the valid date in column G. If that date is past due for charge, column J indicates "Yes", if not then "No".
If any line in column J indicates "Yes", I need a message box to pop up indicating that there are pumps that require charging. And do you wish to send the update now?
If "Yes", then the information in Column C in line with the Column J "Yes" is copied and pasted into an outlook email body for distribution.
If there are no pumps requiring charging, the pop up does not appear.
Unfortunately, it doesn't appear that I can attach a sample spreadsheet for visibility, so I'll cut/paste

Thanks and thanks for your patience!

 

[JackDanIce]

What is your code to generate the email? I'm guessing you haven't got it linked to the loop that's testing for Yes/No so that each email generated is specific to that row.