CODEWITHSUNDEEP
excelvbanumbersformat
adit123
adit123

Reputation: 117

Sending out an Email depending on data point using VBA

I am basically trying to send out an email if a certain data point is greater than or less than a max and min point respectively on the sheet. The thing is I set up the "If, Then" statement but it is not giving me the results that I wanted. Some text is even showing up and I am trying to only show the numbers that are in column "E". I wonder where I am going wrong. I will specify in the code where I think I am going wrong. Also I am comparing with "K26" and "K25" respectively for Max and Min. Thank you for your help!

Option Explicit


Public Sub OutofControl()
Dim lRow        As Long
Dim lstRow      As Long
Dim toList      As String
Dim ccList      As String
Dim bccList     As String
Dim eSubject    As String
Dim EBody       As String
Dim vbCrLf As String
Dim data As Variant
Dim ul As Variant
Dim ll As Variant



Dim ws          As Worksheet

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True


End With

Set ws = Sheets(2)
ws.Select

lstRow = WorksheetFunction.Max(3, ws.Cells(Rows.Count, "E").End(xlUp).Row)

For lRow = 1 To lstRow

data = Cells(lRow, "E").Value
ul = Range("K26")
ll = Range("K25")


    If data > ul Or data < ll And Not Null Then 
'THIS IS WHERE I THINK I NEED TO CHANGE SOMETHING SO IT DOES NOT DETECT THE TEXT



   vbCrLf = "<br><br>"

        toList = Range("B1") 'gets the recipient from col B1
        eSubject = "Text " & ActiveWorkbook.Name
        EBody = "<HTML><BODY>"
        EBody = EBody & "Dear " & Range("B1") & vbCrLf
        EBody = EBody & "Text :" & Cells(lRow, "C").Value & vbCrLf
        EBody = EBody & "Link to the SQC Chart:"
        EBody = EBody & "<A href ='Text'>Text </A>" & vbCrLf

        EBody = EBody & "</BODY></HTML>"



        MailData msgSubject:=eSubject, msgBody:=EBody, Sendto:=toList

        'Cells(lRow, "W").Value = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column W"

    End If
Next lRow


ActiveWorkbook.Save


With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True

End With

End Sub



Function MailData(msgSubject As String, msgBody As String, Sendto As String, _
    Optional CCto As String, Optional BCCto As String, Optional fAttach As String)

Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)
With Itm
    .Subject = msgSubject
    .To = Sendto
    If Not IsMissing(CCto) Then .Cc = CCto
    If Len(Trim(BCCto)) > 0 Then
        .Bcc = BCCto
    End If
    .HTMLBody = msgBody
    .BodyFormat = 2 '1=Plain text, 2=HTML 3=RichText -- ISSUE: this does not keep HTML formatting -- converts all text
    'On Error Resume Next
    If Len(Trim(fAttach)) > 0 Then .Attachments.Add (fAttach) ' Must be complete path'and filename if you require an attachment to be included
    'Err.Clear
    'On Error GoTo 0
    .Save           ' This property is used when you want to saves mail to the Concept folder
    .Display      ' This property is used when you want to display before sending
    '.Send         ' This property is used if you want to send without verification
End With
Set app = Nothing
Set Itm = Nothing
End Function

Upvotes: 1

Views: 135

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

  1. or requires parenthesis to distinguish from and. 2. Code below will check for numeric value.
For lRow = 1 To lstRow
    data = Cells(lRow, "E").Value
    ul = Range("K26")
    ll = Range("K25")
If data > ul Or data < ll Then
  If IsNumeric(data) = True And isBlank(data) = False Then

   vbCrLf = "<br><br>"
        toList = Range("B1") 'gets the recipient from col B1
        eSubject = "Text " & ActiveWorkbook.Name
        EBody = "<HTML><BODY>" _
        & "Dear " & Range("B1") & vbCrLf _
        & "Text :" & Cells(lRow, "C").Value & vbCrLf _
        & "Link to the SQC Chart:" _
        & "<A href ='Text'>Text </A>" & vbCrLf _
        & "</BODY></HTML>"

        MailData msgSubject:=eSubject, msgBody:=EBody, Sendto:=toList

        'Cells(lRow, "W").Value = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column W"

      End If
    End If
Next lRow

Upvotes: 1

Related Questions