Email Alert Excel Using VBA

[daddyfoxuk]

Hi All, i'm wanting to create an auto email to be sent from excel when a cell in I equals more than one, kind of like an alert... However i would like this email to include certain cells of information depending on which cell in I has changed. For example if I10 changes i would like cells A10 to H10 to be copied and sent in the email.

I have the below code but i'm at a loss right now.... Any help or suggestion would be much appreciated! Thank You!!

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("I1:I10000"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 1 Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Suspect Part Found" & vbNewLine & vbNewLine & _
"Containment Inspection" & vbNewLine & _
"Please Contact Inspection Area"
On Error Resume Next
With xOutMail
.To = "My Email"
.CC = ""
.BCC = ""
.Subject = "Suspect Part Located"
.Body = xMailBody
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[lrobbo314]

How about this...

Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim cel As Range
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Suspect Part Found" & vbNewLine & vbNewLine & _
"Containment Inspection" & vbNewLine & _
"Please Contact Inspection Area" & vbNewLine


For Each cel In Range("A" & Target.Row & ":H" & Target.Row)
    xMailBody = xlmailbody & cel.Value & vbLf
Next cel


On Error Resume Next
With xOutMail
.To = "My Email"
.CC = ""
.BCC = ""
.Subject = "Suspect Part Located"
.Body = xMailBody
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[daddyfoxuk]

Hi, thanks.... Im now getting a runtime error and the emails arent sending

How about this...

Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim cel As Range
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Suspect Part Found" & vbNewLine & vbNewLine & _
"Containment Inspection" & vbNewLine & _
"Please Contact Inspection Area" & vbNewLine


For Each cel In Range("A" & Target.Row & ":H" & Target.Row)
    xMailBody = xlmailbody & cel.Value & vbLf
Next cel


On Error Resume Next
With xOutMail
.To = "My Email"
.CC = ""
.BCC = ""
.Subject = "Suspect Part Located"
.Body = xMailBody
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[lrobbo314]

I see what I did wrong. Both subs need to be changed.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("I1:I10000"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value > 1 Then
Call Mail_small_Text_Outlook(Target.Row)
End If
End Sub

Sub Mail_small_Text_Outlook(tRow)
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim cel As Range
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Suspect Part Found" & vbNewLine & vbNewLine & _
"Containment Inspection" & vbNewLine & _
"Please Contact Inspection Area" & vbNewLine


For Each cel In Range("A" & tRow & ":H" & tRow)
    xMailBody = xMailBody & cel.Value & vbLf
Next cel


On Error Resume Next
With xOutMail
.To = "My Email"
.CC = ""
.BCC = ""
.Subject = "Suspect Part Located"
.Body = xMailBody
.display
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[daddyfoxuk]

HI, thanks but all im getting now is a box asking me to create a macro when i try and run the code...?

 

[lrobbo314]

The code needs to be triggered by the worksheet event. The Mail_small_Text_Outlook sub is receiving a variable, so you can't run it from the editor.