# Email Macro in Excel??



## ispy4mi6 (Feb 27, 2007)

I have an Excel model (office 2000) which has a range of cells that contain if/then statements. I'm trying to get Excel to automatically send an email if any of the cells within that specified range were to provide a value that was false. Is this possible? Thanks for any insight.


----------



## Zack Barresse (Jul 25, 2004)

Hi there,

Of course this is possible. You won't get any solutions with the information you've posted though. It is not complete enough for a viable solution. Please post specific information about your data structure, what information goes where, what you have for softare, etc. You can upload a sample file you want (a picture says a thousand words  ).

HTH


----------



## ispy4mi6 (Feb 27, 2007)

Hi, 

Basically, I've attached a file showing the following:

In the Cash and System input worksheets, bond information is updated monthly. These bonds have special triggers associated with them which are on the Triggers worksheet. You will see within the Trigger formula's, there are IF/THEN statements showing if the triggers are ok or have breached their thresholds. What I'd like to happen is that as the information is entered into the Cash and System input worksheets and the Trigger worksheet calcs the IF/THEN statements and it happens to be a breach of a trigger (in this case a TRUE result) then I'd like to have an email automatically sent to notify that there is a potential problem. Someone told me to create a button that checks the trigger page and then send an email, but that would defeat the purpose. At that point, I can easily just click on the triggers tab myself to see if anything has been breached. Since the data entry will be done by someone else, I'm looking for a macro to always be operating in the "background" when the file is open so that in case of a BREACH (TRUE result) the macro would immediately fire off an email. I hope this makes sense and I really hope this is possible. Thanks for any assistance on this. 

Btw, I've just upgraded to Excel 2003 SP2. Embarrasing...I know.


----------



## OBP (Mar 8, 2005)

ispy4mi6, this should be quite straightforward to achieve and I or Andy (Bomb) could do it for you, but Zack is the "Man" on Excel. If he doesn't get back to you I will have a go for you.


----------



## Zack Barresse (Jul 25, 2004)

ispy4mi6,

You can get the email to fire upon calculation, check a specific range for any specific values, you can have an email generated, *but*, having it send on it's own without user authorization is difficult. It is generally best to have _some kind_ of user authorization. Even if it is a yes/no message presented to the user. This is mainly due to Outlook's security measures in sending emails automatically. I'm sure you can imagine the potential spam issues they've dealt with here (the MS Outlook development team).

There are, however, ways around it if you'd like. You can either use CDO, ClickYes (an automatic clicking software to click the Yes button that pops up if you automatically send from Outlook via automation), Outlook Redemption, etc.

So before we start anything, you need to decide how you want to approach this matter as it will affect any solution(s) brought to you.

@OBP: Thanks for the vote of confidence!


----------



## ispy4mi6 (Feb 27, 2007)

Oh ok. I don't mind having user authorization to ask to send an email, but I want to refrain from having to click a macro to review a range of cells and send an email. I basically am looking for an automatic review of the cells with triggers and if they're breached, then an email would be sent (after authorization). Let me know how you think it's best that I approach this problem. Thanks much.


----------



## Rollin_Again (Sep 4, 2003)

The program Zack mentioned earlier (ClickYes) works very well and will allow the macro to run without user input. I've been using it for several years and am quite happy with it. The program is free and it runs silently in the background.

Regards,
Rollin


----------



## Zack Barresse (Jul 25, 2004)

If you want to go forward with this, detail out your 'Triggers' sheet and what range you will need to have checked on calculation. It looks like columns F:I must have *OK*, and that is when you'd like an email dispatched. Correct so far?

If the above is correct, we will need one additional 'helper' column. This column will be a place to mark that that record (row) has had an email dispatched on its behalf, or else you'll be dispatching emails every time you calculate the worksheet! I don't think that is what you want.

Please post back and let us know.


----------



## ispy4mi6 (Feb 27, 2007)

The range I want to check will be D4:F74. Within this range, if any of the cells have a true value, then an email would need to be sent. So, if the result of the cells is "ok," then I don't need an email sent. The "ok" is the false value. The true values are named "BREACH," "Shortfall," "Event of Default," etc. If these values are triggered, I need an email sent. I'm a little unclear as to why the extra column is neccessary? I don't know VBA and just purchased a book on Excel, but not sure how detailed the VBA section is. I appreciate all of your help as I strive to become an excel expert. 

Again, I'm ok with the user authorization to send an email. 

Sorry for the delayed posts, I've been travelling, but am back now and have full time access to a PC.


----------



## Zack Barresse (Jul 25, 2004)

I apologize for _my_ delay, I've been out for the last week in Seattle.

Trust me, you need an extra column. Now I'm now sure about your values. Can you line out what is what?? Give me a couple of examples please.


----------



## ispy4mi6 (Feb 27, 2007)

Ahhh...I see what you mean by the extra column. Yes, I don't want an email sent for a prior breach that has already been identified. 

I'm a little unclear what you mean by line out? The spreadsheet would be updated monthly and if any of the values in the range within that specific month happen to yield a true value, then an email would need to be dispatched. For example, let's use May 07. If the cell D7 happened to produce a true value (in this case the word BREACH would appear), then an email would need to be sent. This would be the case for each month for cells D7 through I7 (May07). June 07, the cells would be D8 through I8. So every month columns D through I would have to be checked to see if a true value had been tripped. If yes, then an email would be sent. 

I'm not sure if I'm being clear. If not, let me know and I'll try to explain what I'm trying to accomplish. Thanks.


----------



## Zack Barresse (Jul 25, 2004)

Okay, so basically, let me see if I get this right..

Send an email:
1) Anytime a "BREACH" is in column D, process email
2) Anytime a "BREACH" is in column E, process email
3) Anytime a "SHORTFALL" is in column F, process email
4) Anytime a "RAPID AM" is in column G, process email
5) Anytime a "EVENT OF DEFAULT" is in column H, process email
6) Anytime a "SHORT" is in column I, process email

Now is it if any ONE of these becomes true that the email should be dispatched? Or if ALL of them are true an email should be dispatched?

_Edit: By the way, when you post a spreadsheet, take off the protection. I won't even look at a project if it is protected. Alternatively you can give us the password. Even though I *can* break it, I find it is a bad habit, so I won't do it._


----------



## ispy4mi6 (Feb 27, 2007)

Ahh...I'm sorry...I didn't clarify. 

Yes, what you have in your post is exactly right! If at any time, any ONE of the values become true, then an email should be dispatched. They are all mutually exclusive events that need to be monitored. 

I apologize for the protection. I forgot it existed. The password is Triggers. 
Thanks, 

Jay


----------



## Zack Barresse (Jul 25, 2004)

You could use something like this in your worksheet module...


```
Option Explicit

Dim CHECKRANGE As String

Private Sub Worksheet_Calculate()
    Call GenerateEmail("D4:I74")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.cell.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("D4:I74")) Is Nothing Then Exit Sub
    Call GenerateEmail(Target.Address)
End Sub

Private Sub GenerateEmail(strRange As String)
    Dim olApp As Object, olMail As Object, blnCreated As Boolean
    Dim c As Range, i As Long
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    blnCreated = False
    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
        blnCreated = True
    End If
    On Error GoTo 0
    For Each c In Me.Range(strRange)
        If Me.Cells(c.Row, "J").Value <> 0 Or Not IsEmpty(Me.Cells(c.Row, "J").Value) Then GoTo SkipRow
        For i = 0 To 5
            If Me.Cells(c.Row, i + 4).Value = Choose(i + 1, "BREACH", "BREACH", "SHORTFALL", "RAPID AM", "EVENT OF DEFAULT", "SHORT") Then
                Set olMail = olApp.CreateItem(0)
                olMail.To = "[email protected]"
                olMail.Cc = "[email protected]"
                olMail.Subject = "Subject goes here"
                olMail.Body = "Add as much body as you want here." & vbCrLf & vbCrLf & "See our double space?"
                'olMail.Attachments.Add strFullPathNameHere
                olMail.Display
                Me.Cells(c.Row, "J").Value = Format(Date + Time, "dd-mmm-yy hh:mm")
                Exit For
            End If
        Next i
SkipRow:
    Next c
    If blnCreated = True Then
        olApp.Quit
    End If
End Sub
```
I can comment it up if you'd like to know what is going on. Let me know if this works.

_Edit: All of the code didn't get copied over; I think it was from an earlier version I was working on. Apologies. Code in post is now updated._

HTH


----------



## ispy4mi6 (Feb 27, 2007)

Wow this works great! The only thing I didn't realize was that as data is entered, it immediately tries to send an email without waiting until all the data is input. I didn't realize, I would want to wait until all the data is input before checking the triggers tab. If I copied and pasted your code into a macro and created a button for the user to hit, do you think that would alleviate the issue? 

At first I thought I wanted a real time check, but operationally I found myself cancelling the email everytime I went to enter a value.


----------



## Zack Barresse (Jul 25, 2004)

Sure, we could do that as well. It's always different when you put it to the 'real world' test. 

This would need to go to into a Standard Module. You should only need to change a couple of lines...


```
Sub GenerateEmail()
    Dim olApp As Object, olMail As Object, blnCreated As Boolean
    Dim c As Range, i As Long, ws As Worksheet
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    blnCreated = False
    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
        blnCreated = True
    End If
    On Error GoTo 0
    Set ws = Sheets("Triggers")
    For Each c In ws.Range("D4:I74")
        If ws.Cells(c.Row, "J").Value <> 0 Or Not IsEmpty(ws.Cells(c.Row, "J").Value) Then GoTo SkipRow
        For i = 0 To 5
            If ws.Cells(c.Row, i + 4).Value = Choose(i + 1, "BREACH", "BREACH", "SHORTFALL", "RAPID AM", "EVENT OF DEFAULT", "SHORT") Then
                Set olMail = olApp.CreateItem(0)
                olMail.To = "[email protected]"
                olMail.Cc = "[email protected]"
                olMail.Subject = "Subject goes here"
                olMail.Body = "Add as much body as you want here." & vbCrLf & vbCrLf & "See our double space?"
                'olMail.Attachments.Add strFullPathNameHere
                olMail.Display
                ws.Unprotect Password:="Triggers"
                ws.Cells(c.Row, "J").Value = Format(Date + Time, "dd-mmm-yy hh:mm")
                ws.Protect Password:="Triggers"
                Exit For
            End If
        Next i
SkipRow:
    Next c
    If blnCreated = True Then
        olApp.Quit
    End If
End Sub
```


----------

