Morning all
I'm after some vb script help to use excel and e-mail (probably outlook) to send messages automatically based on the value of a cell.
Background - Wife (Melissa) runs a theatre company, and her sister (Rebecca) is the treasurer and looks after the weekly subs/insurance/costume fees etc etc and does this by printing out shedloads of paper with complicated columns/post-it notes etc and tries to keep track of who pays what on a weekly basis, it's a mess basically. I've mentioned since the company started 6 years ago that there are better ways than shed loads of paper and manual adding up that takes Rebecca hours to sort sometimes incorrectly and Melissa then gets involved with her own notes and cash and well I don't think the books have ever been 100% correct and on the odd occasion I've tried to help out I spend more time trying to work out who owes what, who has paid etc.
What I'm trying to achieve is a spreadsheet with members name and e-mail addresses in that can then sit with Rebecca at the entrance and a simple y or n (or 1/0) can be entered in a cell that should it be n or 0 that an e-mail is automatically generated to the e-mail address stating that they have missed this weeks subs and to remind them to bring next week. I've managed to adapt a script I found on the net that does this, however I'd like to have a default message setup for 1 week, 2 weeks, etc. that would include the date, amount owed and members name.
I know it's possible to include body text in an e-mail but have very limited knowledge in this area and understand it even less!!
Below is the code I currently have, and works on the 1's and 0's for the value, but I'd like it to be a y or n instead as this will make more sense to Rebecca or whoever is taking the subs. Help is mucho appreciated!!
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim lngResponse As Long
Dim URL As String, strEmail As String, strSubject As String
If Left(Target.Address, 2) = "$D" Then
If Target.Value < Range("$E" & Right(Target.Address, 2)).Value Then
lngResponse = MsgBox("This user has not paid subs this week. Would you like to remind them?", vbYesNo)
If lngResponse = vbYes Then
strEmail = Range("$H" & Right(Target.Address, 2)).Value
strSubject = "Unpaid Subs for " & Range("$F" & Right(Target.Address, 2)).Value & " '" & Range("$B" & Right(Target.Address, 2)).Value & "'"
strSubject = Application.WorksheetFunction.Substitute(strSubjec t, " ", "%20")
strURL = "mailto:" & strEmail & "?subject=" & strSubject
ShellExecute 0&, vbNullString, strURL, vbNullString, vbNullString, vbNormalFocus
End If
End If
End If
End Sub