If stmt e-mail (Excel 97)

Topic

New Reply

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.

Reply | Quote

Replies

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:

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

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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(Recipients, Subject, ReturnReceipt)

but that may not suit the oversight control you are looking for.

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

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

Reply | Quote

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.

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

Reply | Quote

With Legare’s help, this is working for me (assuming you substitute a valid e-mail address fro “recipient@company.com”, and correct Workbook name for “wChange.xls”).

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
Workbooks.Add
ActiveWorkbook.SaveAs FileName:=Application.DefaultFilePath & “OverLimitReport.xls”
Workbooks(“wChange.xls”).Activate
Worksheets(“Period”).Activate
Target.EntireRow.Copy
Workbooks(“OverLimitReport.xls”).Worksheets(1).Paste
Application.CutCopyMode = False
Workbooks(“OverLimitReport.xls”).SendMail “recipient@company.com”, _
“OverLimitReport from ” & Application.UserName
Workbooks(“OverLimitReport.xls”).Close SaveChanges:=False
Kill Application.DefaultFilePath & “OverLimitReport.xls”
End If
End If
End If
End Sub

Probably needs more error handling, but I leave that to you. Good luck, I’m on my way to the dentist!

Reply | Quote

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

Reply | Quote

If I understand you, you want to send the contents of a cell as an email if it exceeds a certain value. If that is so the following code should help. However you will need a reference to the Microsoft Outlook Object Library 8.0 (or whatever version youu have).

Copy the following code, right click on the “Period” sheet tab, and paste the all the code. (if you have a worksheet_change event already there, delete it). then go Tools, References (still in VB environment) and scroll down the list until you find the reference mentioned above.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, ActiveSheet.Range(“F:F”)) Is Nothing Then
If Target.Value >= 1000 Then
Call eMailData(Target.Value)
End If
End If
End Sub

Private Sub eMailData(strData As String)
Dim objOLook As New Outlook.Application
Dim objOMail As MailItem
Set objOLook = New Outlook.Application
Set objOMail = objOLook.CreateItem(olMailItem)
With objOMail
.To = “ruthe@company.com”
.Subject = “Subject”
.Body = “Ruth, ” & vbCr & “The quote value is now ” & strData
.Send
End With
Set objOMail = Nothing
Set objOLook = Nothing
End Sub

Both those procedures can go in the Woskshhet module for the sheetname “Period”.

Hope that helps.

Andrew C

Reply | Quote

This code worked!! At least, I was finally able to get the e-mail message to go through. Is there any way that I can get all of the target row information (columns A thru H) on the message instead of just the target.value?

Also – and to further complicate things – is there any way that I can specify that I want the e-mail sent to the address appearing in column I of the target row? They want the e-mail message to go to the appropriate sales person and I’ve used a lookup table to pull the appropriate sales person’s e-mail address into each row, now I just need to know how to reference it using code.

P.S. I’d appreciate anyone’s input that might have an idea of why the .sendmail command would not work on my system (I could not get a message sent using the code that John provided).

Reply | Quote

What you want should be easy enough to achieve, but the best method would depend on whether the target cell is always in the same column, and oif so which one. Also usefule to know would be nature of teh data in thge other cells. You could use the offset property to get the value of the cells, and then concatenate the values in order to compose the body text of the message. The email address could be picked up also by usee of offset.

If you need more help , just clarify the exactly the column(s) that trigger the email, and if possible the nature of the adjacent data.

I don’t know really know why the sendmail method wuld not work for you, if you have an email client installed and functional. Did you try JohnBF’s sample workbook ?

Andrew

Reply | Quote

The e-mail address that I need to send to is always in column I. Attached is a sample of my spreadsheet so you can see the setup that I’m working with as well as the code that I’m running.

I copied JohnBF’s code into my spreadsheet and made the needed changes, but could not get his code to work either. I can send spreadsheets directly from Excel using the File-Send To command, so I’m not sure why I couldn’t do it using code.

Reply | Quote

Edited by Andrew Cronnolly on 11-Jul-01 20:01.

I have amended the code as follows :

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim strData As String, strAddr As String
    strData = "CUSTOMER    DATE RECD   DATE COMP   DATE DUE OnTime _
 			VALUE Items COMMENTS" & vbCrLf
        If Not Intersect(Target, ActiveSheet.Range("F:F")) Is Nothing Then
                If Target.Value >= 1000 Then
                    For i = -5 To 2 Step 1
                        strData = strData & Target.Offset(0, i).Value & vbTab
                    Next
                    strAddr = Target.Offset(0, 3).Value
                    Call eMailData(strData, strAddr)
                End If
        End If
End Sub
	
Private Sub eMailData(strData As String, strAddr As String)
        Dim objOLook As New Outlook.Application
        Dim objOMail As MailItem
        Set objOLook = New Outlook.Application
        Set objOMail = objOLook.CreateItem(olMailItem)
        With objOMail
                .To = strAddr
                .Subject = "Subject"
                .Body = strData
                .Send
        End With
        Set objOMail = Nothing
        Set objOLook = Nothing
End Sub

This should insert the required contents into the email message, and also pick up the email address.

I cannot see why you should have problem with JohnBF’s code, as it should work. If any occurs I will let yoy know. I am attaching th e workbook with the amended code.

Andrew C

Reply | Quote

Can I ask for help one more time please? Using Andrews code, I finally got the spreadsheet to e-mail properly. Since the e-mail is originating in a program other than Excel, we have to manual tell our system that yes, we do want the message sent. However, if someone says anything other than “Yes” when asked if they want to send the e-mail, the code is erring out. How can I troubleshoot this so that the code will stop running if the send command is not executed?

Reply | Quote

I thought that the whole idea was to have Excel originate the message, I am a little confused now. Which program starts the process ?, and which program requires the confirmation to send ?

Much of what happens once the command .send is processed is dependant on the settings you have set up for Outlook. You may need to consult with a system adminstrator to sort out any problems you are having as they can be caused by firewalls and/or anti virus software.

Andrew

Reply | Quote

Andrew: The way I read his note, what he needs is an On Error statement to catch the error from .Send when the user clicks on No and exit from the routine.

Reply | Quote

Thanks Legare, and sorry I was not in a position to respond.

Hopefully thanks to your intervention the problem is solved.

Andrew

Reply | Quote

The message does originate in Excel. I think the problems that I’ve run into with the .sendmail command are due to some of the filtering systems that we have in place with our version of Outlook 98. Here’s what I think is happening:

With your code I’m actually creating a new Outlook e-mail message, placing the information that I want into the message and then sending that message using the standard Outlook e-mail setup. After the message is created but before it’s sent a Virus Warning screen appears notifying the user that “A program is automatically trying to send an e-mail on your behalf” and the user has to say Yes, they are aware of the message, before it is sent. If they answer Yes, the message is sent without any problems and control returns to Excel. If they answer No, the message is not sent and Excel get’s a “Run-time Error 287: Application-defined or Object-defined” screen appears. Selecting End will return control to the user and they are able to continue working on the spreadsheet.

I think the .sendmail command actually takes the workbook and sends it using Outlook’s mail system but not as an Outlook e-mail message. Our system is not set up to allow POP3 mail which might be how the .sendmail command executes the mail message. That would explain why I was not able to get the .sendmail command to work for me.

As far as the .send command goes, what I’m looking for is a way to tell the system that if the .send command was not executed (the message was not sent) then the code should stop running.

Reply | Quote

I was hoping that Andrew would respond since he understands the email code better than I do. However, since he has not, I will post the code below which I think will solve your problem:

Private Sub eMailData(strData As String, strAddr As String)
Dim objOLook As New Outlook.Application
Dim objOMail As MailItem
    On Error Resume Next
    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)
    With objOMail
        .To = strAddr
        .Subject = "Subject"
        .Body = strData
        .Send
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    On Error GoTo 0
End Sub

Reply | Quote

The spreadsheet works!!!!! Thanks to everyone for all of the help you gave me.

Reply | Quote

Here’s a copy of my spreadsheet using the code from JohnBF. This code will work up to the .sendmail command. At this point I get the “Run-time error 1004” “General Mail Failure” error. Does anyone see anything that I’m overlooking in the code?

Reply | Quote

Paula, I d/l’d your workbook; I encountered one error:
Workbooks(“2001 Quote Spreadsheet-a.xls”).Activate
gets a subscript out-of-range error, because it should be:
Workbooks(“Quotespreadsheet-a.xls”).Activate
due to the actual spreadsheet name.

After this correction the code works for me at my workstation. (For testing I modified the “Sales Rep’s” Sheet Column C e-mail addresses so they were all mine, to send only to me, except for one that I’m sure you are getting about 1.00 pm US MST!). Be sure that, since the code is blowing up before the Kill line, you manually delete any orphaned instances of “EMail.xls”. For consistency reasons, I also recommend you change to
Application.DefaultFilePath & “EMail.xls”
for the lines shown below. FWIW, also added code to direct to the Sales Rep’s E-Mail address, as follows:

Option Explicit
Private Sub Worksheet_change(ByVal Target As Excel.Range)
Dim strSRepEMAddr As String
If ActiveSheet.Name = “Period” Then
If Not Intersect(Target, Worksheets(“Period”).Range(“F:F”)) Is Nothing Then
If Target.Value >= 1000 Then
Target.Activate
strSRepEMAddr = Target.Offset(0, 3).Value
Workbooks.Add
ActiveWorkbook.SaveAs FileName:=Application.DefaultFilePath _
& “EMail.xls”
Workbooks(“Quotespreadsheet-a.xls”).Activate
Worksheets(“Period”).Activate
Target.EntireRow.Copy
Workbooks(“EMail.xls”).Worksheets(1).Paste
Application.CutCopyMode = False
Workbooks(“EMail.xls”).SendMail strSRepEMAddr, _
“Quote Notification from ” & Application.UserName
Workbooks(“EMail.xls”).Close SaveChanges:=False
Kill Application.DefaultFilePath & “EMail.xls”
End If
End If
End If
End Sub

I feel bad this isn’t working for you.

Reply | Quote

I may have figured out why I can’t send a message using your code. When I run Andrew’s code, a warning box appears before the e-mail is actually sent. The message says

“A program is trying to automatically send e-mail on your behalf. Do you want to allow this? If this is unexpected it may be a virus and you should choose No.”

I think we have a virus filter of some kind on our system that’s stopping the .sendmail command from being executed.

Reply | Quote

You need to talk to your system administrator about that one. For your workstation only, may (?) help set your Outlook virus “sensitivity” better; see also WOW 5-25, & WOW 5-28. Slipstick may have some advice, and you could ask about this problem in the Outlook forum . Good luck.

Reply | Quote

That error means “Application-defined or object-defined error.” That is a little cryptic, but it probably means that you are missing a reference. Go to the VBA Editor and select References from the Tools menu. Scan down the list and see if any are marked missing.

Reply | Quote

How would I know if something’s missing? I checked through the list and everything seems to have a valid location and language. Could there be something that needs to be checked on before I can use the .sendmail command that I don’t currently have checked? The five items that are currently checked are: 1) Visual Basic For Applications; 2) Microsoft Excel 8.0 Object Library; 3) OLE Automation; 4) Microsoft Forms 2.0 Object Library; 5) Microsoft Office 8.0 Object Library.

Reply | Quote

I have never used this, so I don’t really know exactly what you should look for. I know that if an AddIn is missing, the list usually shows it as missing. I don’t really know what happens with references. You might want to search the list for a reference to Outlook or anything to do with MAPI. Maybe someone else will jump in with a better knowledge of what to look for.

Reply | Quote

Just in case it helps: If you rename a library file and then open a project that requires it, it inserts “MISSING:” in front of the name you would normally see there. The box remains checked.

HTH

Brooke

Reply | Quote

Those 5 are all I have checked in my References list for the code that I worked on. I’d guess you’ll need at least MS Outlook Object Model checked for Andrew’s solution.

Reply | Quote

If you have outlook set up on your system, or any othet Mapi email client the sendmail should not cause any problems with the references you have.

Run the following little macro and see if you get a ‘True’ response, if not you have problems with you email installation.

Sub IsMapi()
    MsgBox Application.MailSystem = xlMAPI
End Sub

Andrew

Reply | Quote

That worked. I guess the problem is with my code, not my e-mail system.

Reply | Quote

Could you post a copy of the workbook, just with the VBA code and any sheets/ranges your macro addresses. You can delete any info that may be sensitive

Andrew

Reply | Quote

FWIW attached is the one which works for me. (Do me the favor of changing the e-mail addressee!)

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

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

Reply | Quote