Results 1 to 7 of 7

Thread: vb script in excel

  1. #1
    Member
    Join Date
    Feb 2014
    Posts
    73
    vCash
    500
    Points
    181,106
    Bank
    0
    Total Points
    181,106
    Donate

    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

  2. #2
    Member
    Join Date
    Feb 2014
    Posts
    73
    vCash
    500
    Points
    181,106
    Bank
    0
    Total Points
    181,106
    Donate
    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 :-)

  3. #3
    Senior Member CeeBee's Avatar
    Join Date
    Jan 2014
    Posts
    1,677
    vCash
    1792
    Points
    141,497
    Bank
    0
    Total Points
    141,497
    Donate
    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!"

  4. #4
    Member
    Join Date
    Feb 2014
    Posts
    73
    vCash
    500
    Points
    181,106
    Bank
    0
    Total Points
    181,106
    Donate
    Quote Originally Posted by CeeBee View Post
    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 :-)

  5. #5
    Member
    Join Date
    Feb 2014
    Posts
    73
    vCash
    500
    Points
    181,106
    Bank
    0
    Total Points
    181,106
    Donate
    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!!!

  6. #6
    Member Deity's Avatar
    Join Date
    Jan 2014
    Posts
    92
    vCash
    500
    Points
    60,505
    Bank
    0
    Total Points
    60,505
    Donate
    Quote Originally Posted by corturbra View Post
    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

  7. #7
    Member
    Join Date
    Feb 2014
    Posts
    73
    vCash
    500
    Points
    181,106
    Bank
    0
    Total Points
    181,106
    Donate
    Quote Originally Posted by Deity View Post
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •