Send Email on cell change works..BUT..

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Good day,

I am sending an email when the value of a cell changes, http://www.rondebruin.nl/mail/change.htm
I am using the following code in my worksheet....
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Target.Cells.Count > 1 Then Exit Sub
    On Error GoTo EndMacro
    If Not Target.HasFormula Then
        Set rng = Target.Dependents
        If Not Intersect(Range("AE7"), rng) Is Nothing Then
            If Range("AE7").Value < 20 Then EmailOut 'MyMacroName
    End If
    End If
EndMacro:
End Sub

My Macro code..
Code:
Sub EmailOut ()    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
 
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
 
    strto = "[EMAIL="Ak@XYZ.com"]Ak@XYZ.com[/EMAIL]"
    strcc = ""
    strbcc = ""
    strsub = "Testing email send"
    strbody = "Using EmailOut" & vbNewLine & vbNewLine & _
              "Cell AE7 has changed"
 
    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .Send
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

What I would like to do is increase the range that this applies to.
Range from AE7 to Range Y3:AE250.
I would like to Include the Range A3:A250 in the subject/body.

So If AE7 has changed, A7 (along with some text "This cell has changed, do X Y Z") would be somewhere within the email.

I hope that is clear and possible!

Ak
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Can someone explain why this range wont work?
What do I need to change, it's driving me nuts.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("[COLOR=blue]Y3:AE250[/COLOR]"), rng) Is Nothing Then
If [COLOR=black]Range("[/COLOR][COLOR=blue]Y3:AE250"[/COLOR][COLOR=black]).[/COLOR]Value < 20 Then EmailOut 'MyMacroName
End If
End If
EndMacro:
End Sub 
[CODE] 
 
 
Ak
 
Upvote 0
*** BUMP***

I'm sorry to have to bump this, but if I pull any more of my hair out over this, I'm going to need my head polishing.

If the only help to this, is how I can increase the range, then I will be happy with that.

Any takers?

Thanks

Ak
 
Upvote 0
***BUMP Again, sorry***

I have been working on this for over a week now and I am no closer to resolving my problem. Is there an Excel Guru out there that can help this mentally challenged Excel novice?

P L E A S E.

Ak
 
Upvote 0
***BUMP***

I have no hair left, Google will ban me if I do any more searching!
I am all worn out and brain drained over this.

I have tried everything that I know to conclude this problem,
but I am still stuck at my starting point...

How do I change the range from one cell to numerous cells?


Ak
 
Upvote 0
****BUMP****

Another day, another bump, sorry folks.

I cannot believe, that I cannot find the answer to this problem on the Internet. :confused:
Am I the only person in the world who wants to do something like this?
Surely there is a solution out there, HELP me out people, Please.

Ak
 
Upvote 0
:) I've almost cracked it :)

Using the following code works IF I type in 20, BUT :(...
I need it to work when the formula I have changes it to 20.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim KeyCells As String
KeyCells = "Y3:AE250"
If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
Application.ScreenUpdating = False
If Not Intersect(Target, Range("Y3:AE250")) Is Nothing Then
    If Target.Value = "20" Then EmailOut
End If
End If
Application.ScreenUpdating = True
 
End Sub

What do I now need to add to have the EmailOut work when the cell is changed by a formula?
I've tried...."If Not Target.HasFormula Then" but this doesn't work :(

Ak
 
Upvote 0
Hi Peter,

Your post is almost cruel :)

I have spent over a week trying to solve this, I'm sure my vision is now impaired :( and you hang a complicated carrot in front of me :confused:.

I appreciate your input but as usual, my brain is a blank to what you have "kindly" suggested.

Any further advice may save me a visit to the opticians :)

Ak
 
Upvote 0
Perhaps (I'm not clear which range you want to check - adjust as necessary)

Code:
Private Sub Worksheet_Calculate()
Dim c As Range
For Each c In Range("Y3:AE250")
    If c.Value = 20 Then
        Call EmailOut
        Exit For
    End If
Next c
End Sub
 
Upvote 0

Forum statistics

Threads
1,217,365
Messages
6,136,122
Members
449,993
Latest member
Sphere2215

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
Back
Top