Passing variables between code?

[JackDanIce]

Hi,

I have an event trigger in my workbook (that monitors report delivery deadlines) that when the workbook is opened, it cycles through some defined sheets and alerts the user if a report is due that day.

I'm in the seperate process of creating a module that will email a manager if a report is due and/or overdue (the workbook is opened daily), however, I'm stuck on how to call it correctly against a particular due/overdue report.

Code sort of looks like this:

Sub Check_Reports()
' This is part of the workbook code as an event trigger
Dim i as long, j as long
For i = 1 to 4
  For j = 8 to sheets(i).Range("G" & Rows.Count).End(xlUp).Row
    If sheets(i).Range("G" & j) is past the due date then ' can't be bothered to type this bit of code out, there's some other checks too.
      msgbox Sheets(i).Range("G" & j) & " report is due"
      Call Send_Email
    End If
  Next j
Next i
End Sub

But..

Call Send_Email ()
' Stolen from Ron de Bruin (thank you!)
End Sub

Is a generic bit of code to send a single pre-defined email.

What I'd like is for the event trigger code Check_Reports; when it evaluates the IF condition for TRUE, to send that report into the Send_Email procedure for every report it tests. I'm pretty sure this means passing the value of Sheets(i).Range("G" & j) into Send_Email but I'm stuck on how to do this.

Any suggestions or help please?

Thank you,
Jack

 

[ScottR]

Sub SendMail(repName)

'Stuff goes here

End Sub

and when calling it

Call Send_Email(sheets(i).Range("G" & j).value)

</pre>

 

[JackDanIce]

Thanks Scott, you're a legend. No wait, I should probably test that first before praising you.. but it should work!

Cheers,
Jack