Insert Cell Value Into Email

[smythcounty]

I would like to place the value of a cell in this email when it sends The value is located in cell H6. Could anyone assist?

Sub Mail_with_outlook1()
    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")
    Set OutMail = OutApp.CreateItem(0)

    strto = "tdog@mailexample.com"
    strcc = ""
    strbcc = ""
    strsub = "******NOTICE******  AN ORDER IS NEARING COMPLETION"
    strbody = "****WARNING****" & vbNewLine & vbNewLine & _
              "***AN ORDER IS NEARING COMPLETION!!!" & vbNewLine & vbNewLine & _
              "This is an automated message"
    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .Display   ' or use .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Here is macro that looks for the changes in the cells and calls the mail:

Private Sub Worksheet_Calculate()
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As Double
   

    NotSentMsg = "Not Sent"
    SentMsg = "Sent"

    'Above the MyLimit value it will run the macro
    MyLimit = 400000
    
    'Set the range with the Formula that you want to check
    Set FormulaRange = Me.Range("H6")

    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            Else
                If .Value < MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook1
                    End If
                Else
                
                    MyMsg = NotSentMsg
                End If
            
            End If
            Application.EnableEvents = False
            .Offset(0, 1).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell

ExitMacro:
    Exit Sub

EndMacro:
    Application.EnableEvents = True

    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description

End Sub

 

[6StringJazzer]

Modify this line of code. You were not specific about where you wanted the value of H6, and you did not say where the code is to send the email, so modify to suit.

    strbody = "****WARNING****" & vbNewLine & vbNewLine & _
              "***AN ORDER IS NEARING COMPLETION!!!" & vbNewLine & vbNewLine & _
              "This is an automated message" & _
              "Here is the cell value: " & Worksheets("SomeWorksheet").Range("H6").Text

 

[smythcounty]

Modify this line of code. You were not specific about where you wanted the value of H6, and you did not say where the code is to send the email, so modify to suit.

    strbody = "****WARNING****" & vbNewLine & vbNewLine & _
              "***AN ORDER IS NEARING COMPLETION!!!" & vbNewLine & vbNewLine & _
              "This is an automated message" & _
              "Here is the cell value: " & Worksheets("SomeWorksheet").Range("H6").Text

Worked Great! Thanks!