Automatically send email from worksht based on an IF formula

Mulevariant

New Member
Joined
Dec 2, 2004
Messages
17
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Dec 2, 2004
Messages
17
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
In the VBE (Alt + F11) go to Insert --> Module.
 

Mulevariant

New Member
Joined
Dec 2, 2004
Messages
17

ADVERTISEMENT

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

New Member
Joined
Dec 2, 2004
Messages
17
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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 ...

Code:
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 ...

Code:
    If Target.Row < 5 then Exit Sub

Note: Last code untested.
 

amasse

New Member
Joined
May 12, 2011
Messages
1
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
 

Forum statistics

Threads
1,147,676
Messages
5,742,555
Members
423,737
Latest member
tom_xls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top