Automatic email sent from excel based on cell value

[SAFranklin]

I am new to coding and I am doing everything I can to learn all that I can but I need to get a code established ASAP for work and am looking for some help.

I think it's pretty basic but like I said, it's all new to me so it could be complex.

Anyway, I am trying to get an automatic e-mail sent to the same person when the value in column F reaches 4 and then another email sent whenever the value increases in value. The value can decrease as well but will not need an email sent when this happens, only when it reaches 4 and then when it increases in value when above 4. The subject of this email will need to have the content in column B from the same row and the body will include some text and the current value in column F. The row that this starts in is row 15.

I hope this is enough information and help is very much appreciated.

Thanks!

 

[Worf]

Hi
This is how the mail section would look like. I must go offline now but will return tomorrow with the complete code...

Sub CallMail()
Dim s As Worksheet
Set s = Worksheets("Users")
SendEmail2 s.Range("e1").Value, s.Range("e2").Value & ";" & s.Range("e3").Value, _
s.Range("e4").Value, s.Range("e5").Value
End Sub


Sub SendEmail2(toinf$, ccinf$, subj$, bd$)
'Uses early binding
'Requires a reference to the Outlook Object Library
Dim OutlookApp As Outlook.Application, MItem As Outlook.MailItem
Set OutlookApp = New Outlook.Application
If Not toinf Like "*@*" Or Not ccinf Like "*@*" Then Exit Sub
Set MItem = OutlookApp.CreateItem(olMailItem)
With MItem
    .To = toinf
    .CC = ccinf
    .Subject = subj
    .Body = bd
    '.Send          ' send message
    .Save           'to Drafts folder
End With
Set OutlookApp = Nothing
End Sub

 

[Worf]

Please test this:

Dim range_sum#
' sheet module
Private Sub Worksheet_Activate()


range_sum = Application.WorksheetFunction.Sum(Me.Range("f15:f30"))


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Me.Range("f15:f30") ' monitored range


If Not Intersect(Target, r) Is Nothing Then
     If Application.WorksheetFunction.Sum(r) > range_sum And Target.Value >= 4 Then _
     SendEmail2 "yar@starfleet.com", "jlpicard@starfleet.com", Me.Cells(Target.Row, 2), "value is " & Target.Value
     range_sum = Application.WorksheetFunction.Sum(r)
End If
End Sub