Automatically send email from worksht based on an IF formula

[Mulevariant]

Hello

The cells in column "BI" render the text "OVERDUE" if today's date is exactly 10 days later than the date entered in the respective cells in column "S" i.e. if the date in "S1" is 10 days earlier than today's date, "OVERDUE" is written in "OI1"

i need a formula that will email the email address in cell "AJ1" if the value "OVERDUE" is rendered in cell "BI1".

Is this possible? Can anyone help?

I use Windows XP, Outlook 2002, Excel 2002.

Many thanks in advance

 

[Zack Barresse]

Sure. You can utilize late binding such as this example, or use Early binding (preferred by most people).

Put this in your Worksheet Module:


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Range("BI1").Value = "OVERDUE" <SPAN style="color:#00007F">Then</SPAN>
sendBook (Range("AJ1").Text)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>



Put this in a Standard Module:


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> sendBook(theAddy <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)
    <SPAN style="color:#00007F">If</SPAN> MsgBox("Are you sure you want to send the workbook now?", _
        vbYesNo, "Send TKN Report") = vbNo <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook, wasOpen <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    wasOpen = <SPAN style="color:#00007F">True</SPAN>
    Workbooks("temp1.xls").Activate <SPAN style="color:#007F00">'<--Change</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Err <> 0 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> wb = Workbooks.Open("C:\Documents and Settings\Rob\Desktop\temp1.xls") <SPAN style="color:#007F00">'<--Change</SPAN>
        wasOpen = <SPAN style="color:#00007F">False</SPAN>
        Err.Clear
    <SPAN style="color:#00007F">Else</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> wb = ActiveWorkbook
        wb.Save
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> olApp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, olMsg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> olApp = CreateObject("Outlook.Application")
    <SPAN style="color:#00007F">Set</SPAN> olMsg = olApp.CreateItem(0)
    <SPAN style="color:#00007F">With</SPAN> olMsg
        .To = theAddy
        .Cc = "Courtesy Copy here"
        .Bcc = "Blind Courtesy Copy here"
        .Subject = "<SPAN style="color:#00007F">Sub</SPAN>ject Here"
        .Body = "Enter Body Here"
        .Attachments.Add wb.FullName <SPAN style="color:#007F00">'<--Change</SPAN>
        .Display <SPAN style="color:#007F00">'.Send '** Switch if you don't want to preview the msg</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">If</SPAN> wasOpen = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        wb.Close <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> olApp = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> olMsg = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> Sub
</FONT>


This does have the workbook name and path *hardcoded* in the code. Change if desired. We can tweak specifics if need be.


HTH

Edit: code adjustment.

 

[Mulevariant]

Re: Automatically send email from worksht based on an IF for

Thankyou. I cannot code Visual Basic but I am just about keeping up with with you on this one - I can see how it is working. Another question and I think I will be able to implement your solution - if that's ok:

- I know where the worksheet module is but where do i find the standard module to save into?

Thanks

 

[Zack Barresse]

In the VBE (Alt + F11) go to Insert --> Module.

 

[Mulevariant]

Re: Automatically send email from worksht based on an IF for

OK - i have it implemented. Should the code run automatically or do I have to manually activate it?

 

[Mulevariant]

Re: Automatically send email from worksht based on an IF for

And finally, do I need to change the cell/range reference in the piece of code I put in the worksheet module?


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("BI1").Value = "OVERDUE" Then
sendBook (Range("AJ1").Text)
End If
End Sub

 

[Zack Barresse]

It will run automatically if the cell in BI1 is changed to OVERDUE. If this is a formula, I would change the cell change to something like ...

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Range("BI" & Target.Row).Value = "OVERDUE" And Range("S" & Target.Row).Value < Date - 10 Then 
        sendBook (Range("AJ" & Target.Row).Text) 
    End If 
End Sub

This will make it so you can change any cell in row S and it will use that row as the variables to check. If you have headers on say row 4 and do not want to check anything between rows 1 - 4, insert this line just under the "Private Sub..." line ...

    If Target.Row < 5 then Exit Sub

Note: Last code untested.

 

[amasse]

I am using this code to be able to send an emial when a cell is >=175

My problem that I am encountering is, everytime data is enter into any other cell regarless of row or column it tries to send the email agian.

Once the cell reaches >=175 I dont need it to look at that cell anymore.

Also what does the (Range("A1").text) actually do?

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G4").Value >= 175 Then
sendBook (Range("A1").Text)
End If
If Range("I4").Value >= 175 Then
sendBook (Range("A1").Text)
End If
If Range("K4").Value >= 175 Then
sendBook (Range("A1").Text)
End If
If Range("M4").Value >= 175 Then
sendBook (Range("A1").Text)
End If
If Range("O4").Value >= 175 Then
sendBook (Range("A1").Text)
End If
If Range("Q4").Value >= 175 Then
sendBook (Range("A1").Text)
End If
End Sub

Thanks