Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If stmt e-mail (Excel 97)

    I'm a novice VBA user and have been asked to write a macro that will automatically send an Outlook 98 e-mail message to a specific inside sales rep if a quote amount exceeds a specific dollar amount. I'm not sure how to proceed with writing this macro. Would it make more sense to:

    1 - have a hidden IF statement in each row that, if the quote amount criteria is met or exceeded, would launch the macro to send the e-mail message (if this is possible, what command would launch the macro?); or
    2 - have the macro run each time a new row is entered or information in a row is changed that would compare the quote amount to the criteria and, if the criteria is met or exceeded, have the e-mail message sent.

    Any help you can give me on how to write this macro would be greatly appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If stmt e-mail (Excel 97)

    I don't think using an If statement on the worksheet will work for what you need to do. I would recommend using the Worksheet Change event. If the quote amounts are in column C on a sheet named Quotes, then the code would look something like this:

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Worksheets("Quotes").Range("C:C")) Is Nothing Then
    If Target.Value > 10 Then
    'Send the email here
    End If
    End If
    End Sub
    </pre>


    That code has to go in the module behind the Worksheet object, not in a general module.
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: If stmt e-mail (Excel 97)

    I'm pretty much a novice who learned this stuff recently, using XL97 and OL98, so to help you further with Legare's code, here's the function to send the e-mail:

    expression.SendMail(Recipients, Subject, ReturnReceipt)

    An example from my application:

    On Error Resume Next
    ThisWorkbook.SendMail "recipient@company.com", _
    strFileName & " completed by " & strSalesPersonName
    If Err <> 0 Then MsgBox "E-Mail Not Sent"

    Substitute your own error handling code, including how to handle an e-mail failure, for the "E-Mail Not Sent" line.

    You could also use the following approach, which lets the user mess with the recipients, heading and text:

    Application.Dialogs(xlDialogSendMail).Show(Recipie nts, Subject, ReturnReceipt)

    but that may not suit the oversight control you are looking for.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If stmt e-mail (Excel 97)

    I'm trying to fit my information into your code and have a question on one of your lines of code. Can you tell me what the "If Not Intersect..." line is determining (I've never heard of Not Intersect and can't find anything about it in any of the VBA books that I'm using)? Also, I'm not sure what the "Target" is supposed to be (is it my criteria?) and if I'm supposed to substitute anything for "Is Nothing ".

    Thanks for any clarification you can give me.

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If stmt e-mail (Excel 97)

    Any idea on how to handle the code if they want only the row containing the changed (or new) data that meets the criteria to be e-mailed?

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: If stmt e-mail (Excel 97)

    If you mean to copy the row range contents to a text only e-mail, that is way way way beyond my depth; look for threads in Excel, Access and VBA about addressing OL through the MAPI interface.

    Would it be acceptable to copy the row to a new sheet and send that sheet? Or would selecting and highlighting the row in question and e-mailing the entire sheet be feasible?
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If stmt e-mail (Excel 97)

    Copying the information into a new sheet was another alternative being considered. I'm having some trouble pictures how this would all work, though. Wouldn't I want the copy process to be dependent upon an IF statement at the end of each row? I know that Legare said that that's not the way it should be done, but how else can I tell the system that I want only the row that was just entered or changed and meets my criteria to be copied onto the new sheet and from there have the information e-mailed. I don't want all information that meets the criteria to be included in the e-mail.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If stmt e-mail (Excel 97)

    This is an interesting challenge.

    First, I tried Legare's code, but I made a few modifications so I could see it give me some feedback.

    Go to the VBA editor and select in the VBA project window, under Microsoft Excel Objects: Sheetn
    (Quotes)

    Copy and paste the following code:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Worksheets("Quotes").Range("C:C")) Is Nothing Then
    If Target.Value > 10 Then
    Target.Font.Bold = True
    Else
    Target.Font.Bold = False
    'Send the email here
    End If
    End If
    End Sub

    This code is for a worksheet Change event. So each time you select a cell and change it, the event code kicks in. To watch it perform, set a breakpoint at the beginning of the Sub.

    In this code , you should see Bold in the selected cell go on or off depending on the cell's value.

    Once you understand how this piece works, you still have the task of sending the quote value by email. Which is our next challenge.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: If stmt e-mail (Excel 97)

    Legare's Workbook_Change code is the way to go because it's the best way to trap a change. When a change has occured, his code includes the VBA equivalent of =IF(). I'm not familiar with Intersect, so here's the VBA Help:

    Intersect Method
    Returns a Range object that represents the rectangular intersection of two or more ranges.
    Syntax: expression.Intersect(Arg1, Arg2, ...)
    expression Optional. An expression that returns an Application object.
    Arg1, Arg2, ... Required Range. The intersecting ranges. At least two Range objects must be specified.

    (Legares' code is always worthy of study.) It looks as if Legare is testing any change that occurs in Column C, and ignoring any change in any other column. With the following code, the entire row for the last value changed whose value exceeds 10 will be selected. I avoided the code blowing up if a change is made on another sheet, but the code does blow up if you select an area larger than but including a section of column C in Sheet "Quotes", and make any change. I don't know how to prevent or handle that problem.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    If ActiveSheet.Name = "Quotes" Then
    If Not Intersect(Target, Worksheets("Quotes").Range("C:C")) Is Nothing Then
    If Target.Value > 10 Then
    Target.Activate
    ActiveCell.EntireRow.Select
    End If
    End If
    End If
    End Sub

    The selected row can then be manipulated. HTH,
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If stmt e-mail (Excel 97)

    First, let me define what "Target" is. This code is run in the Worksheet Change event. When Excel detects that you have made a change on a worksheet, it calls the Worksheet Change event routine if you have included one. When it calls this routine, it passes a range variable to the routine. That range variable contains the cell that was changed to cause the routine to be called.

    The Worksheet Change routine will be called when any cell on the sheet is changed. So, the first thing that you need to determine is if the change was in one of the Quote amount cells. Remember, I said that the code assumed that the quote amounts were in column C. The Intersect method returns a range that consists of all the cells that are common to all of the ranges passed as parameters. My code passes two ranges to the method. The first is the single cell on the worksheet that was changed to cause the event routine to be called. The second range is all of the cells in column C. If the Target cell in column C, then the only cell that is common to Target and all of the cells in column C is the Target cell, and that is the range that will be returned. If the Target cell is not in column C, then there are no cells in the intersection and the range that is returned is the null range (Nothing). Therefore, the Intersect(Target, Worksheets("Quotes").Range("C:C") Is Nothing will return true if the Target cell is not in column C and will be False if it is in Column C. The Not in front of it makes the test True if the Target is in column C and False if it is not. The only things you should need to change in that statement is the name of the sheet where the quotes are stored, and the range where they are located.
    Legare Coleman

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If stmt e-mail (Excel 97)

    If you are interested, I just completed a project for Emailing a spreadsheet and would be happy to send you the workbook so you could better understand what is going on.

    Of course some modifications to the code would have to be made on your part ... (see below). Once you see the code, you will know what modifications will have to be made.

    What it does: Workbook Name=EMailP
    The user makes any updates to the worksheet. Once complete, user will click on the Email button and the code detects what recipient will recieve the email (modification), then copies the updated worksheet (modification) to a another workbook, EMailS, (modification). The code inserts a new worksheet (modification) then sends to recipient(s). It then deletes the new worksheet that it previously inserted, closes and saves EMailS and returns to EMailP.

    I choose this method because I didn't want my code to be emailed along with the worksheet.

    If you would like me to send you this workbook, please let me know, and I would be more than happy to.....

    Good Luck....



    p.s. I can't take all the credit for the coding in this ... the major coding was provided by Andrew Cronnelly...
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If stmt e-mail (Excel 97)

    I would appreciate the opportunity to see what your workbook code looks like and how it works. Thank you.

  13. #13
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If stmt e-mail (Excel 97)

    When I use Private Sub Worksheet_Change... as the first line in my code, I can get the row selected and copied and I'm able to select a new sheet to paste into, but cannot get the row to paste into the new sheet. (Using the same code as noted below except for the first line)

    When I use Private Sub Workbook_SheetChange... I can't get the code to run at all. Here is the basic code that I'm trying to get to run. (I have the "Period" sheet active when I'm trying to run the code.)

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    If ActiveSheet.Name = "Period" Then
    If Not Intersect(Target, Worksheets("Period").Range("F:F")) Is Nothing Then
    If Target.Value >= 1000 Then
    Target.Activate
    ActiveCell.EntireRow.Select
    Sheets("MailInfo").Select
    End If
    End If
    End If
    End Sub

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If stmt e-mail (Excel 97)

    First, you used the Workbook Sheet Change event, not the Worksheet Change event. The event you used will only be triggered when you change from one worksheet to another worksheet, not when you make a change to a cell in a worksheet. The worksheet change event code goes in the module behind the sheet where you want to look at changes (the Period sheet in your case).

    The following code when put into the module behind the Period worksheet will copy the row that was changed if the amount in column F is greater than 1000 and will paste that row into the MailInfo sheet in row 1.

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Worksheets("Period").Range("F:F")) Is Nothing Then
    If Target.Value >= 1000 Then
    Target.EntireRow.Copy
    Worksheets("MailInfo").Paste Destination:=Worksheets("MailInfo").Range("A1")
    Application.CutCopyMode = False
    End If
    End If
    End Sub
    </pre>

    Legare Coleman

  15. #15
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If stmt e-mail (Excel 97)

    I think that I've made all of the changes necessary to get JohnBF's code to work in my application (I'll fine tune after I get it working), but I'm still having problems. The code will run until it reaches the "sendmail" line, then I'm getting a Run Time Error 1004. Is there anything that I need to have active in either Excel or Outlook that could be causing my problem? Here's the code that I'm trying to run.

    Private Sub Worksheet_change(ByVal Target As Excel.Range)
    If ActiveSheet.Name = "Period" Then
    If Not Intersect(Target, Worksheets("Period").Range("F:F")) Is Nothing Then
    If Target.Value >= 1000 Then
    Target.Activate
    Workbooks.Add
    ActiveWorkbook.SaveAs FileName:="EMail.xls"
    Workbooks("20001 Quote Spreadsheet-c.xls").Activate
    Worksheets("Period").Activate
    Target.EntireRow.Copy
    Workbooks("EMail.xls").Worksheets(1).Paste
    Application.CutCopyMode = False
    Workbooks("EMail.xls").SendMail "ruthe@company.com"
    Workbooks("EMail.xls").Close savechanges:=False
    Kill "EMail.xls"
    End If
    End If
    End If
    End Sub

Page 1 of 3 123 LastLast

Posting Permissions

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