vb script in excel

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

I've tried setting and using the strBody but nothing appears in the message text..... ARGH!!!! It may as well all be in double dutch with lashings of German for all I understand of this :-)

It seems you want to use Excel as a full accounting suite, which it is not.
I suggest you look into GNUCash, it's a very flexible and powerful accounting program.
Now if you choose to go with Excel, make sure you break *ALL* functionality into small, dedicated, manageable functions or you'll be unable to figure out what is going on.
Ex a function that builds the subject, one that builds the body, one that selects users to be mailed...
And here is a sample of a mailing code:

Code:

---

Sub SendEmail(toAddress As String, subject As String, body As String)
    Dim outlook As Object
    Dim email As Object
    Set outlook = CreateObject("Outlook.Application")
    Set email = outlook.CreateItem(0)
    email.to = toAddress
    email.subject = subject
    email.body = body
    email.send
    Set email = Nothing
    Set outlook = Nothing
End Sub

'usage
SendEmail "user@domain", "Payment notification", "Dear person, pay now!"

---

Quote:

---

Originally Posted by CeeBee

It seems you want to use Excel as a full accounting suite, which it is not.
I suggest you look into GNUCash, it's a very flexible and powerful accounting program.

---

Not quite a full accounting suite, although not far off now you've put it that way :-) just had a look at GNUCash, wayyyyyyyyyyyyyy too much for what is needed here. Just want to set up so a button can be clicked that e-mails non-payers at the end of the night, the rest of the accounts are already done properly on-line with balance sheets etc so using another package is not going to sit well with boss1 and boss2. I just think we need to do away with all the paperwork, post-its etc and have a better way of managing the repeat offenders who say things like "wasn't told" "didn't know" etc. If there is an e-mail in the sent items with no bounce back, at least we can say we sent notification.

Current process just doesn't work and ends up in a lot of unpaid subs & fees that a small non-profit company can ill afford to lose.

Thanks for the code example, will have a play around with it :-)

strURL = "mailto:" & strEmail & "?subject=" & strSubject & "?body=" & strBody any ideas what is wrong with this statement? it will only complete two of the commands in this line. So it I set strBody as second it works but won't add a subject, etc etc. How do I get it to complete all 3??? Again. ARGRGRGGRGRGRHHHHRHHRHRHHRHRHHHGGGGGGGHHHH!!!

Quote:

---

Originally Posted by corturbra

strURL = "mailto:" & strEmail & "?subject=" & strSubject & "?body=" & strBody any ideas what is wrong with this statement? it will only complete two of the commands in this line. So it I set strBody as second it works but won't add a subject, etc etc. How do I get it to complete all 3??? Again. ARGRGRGGRGRGRHHHHRHHRHRHHRHRHHHGGGGGGGHHHH!!!

---

Use an ampersand between the arguments, not a question mark. The ? is used to delimit the start of the query string, while an & is used to delimit individual items in the string:

Code:

---

strURL = "mailto:" & strEmail & "?subject=" & strSubject & "&body=" & strBody

---

Quote:

---

Originally Posted by Deity

Use an ampersand between the arguments, not a question mark. The ? is used to delimit the start of the query string, while an & is used to delimit individual items in the string:

Code:

---

strURL = "mailto:" & strEmail & "?subject=" & strSubject & "&body=" & strBody

---

---

Deity - I could kiss you right now!!! THANK YOU!!!!! Finally it works!