Want to contents of cell to be email body - Please Help

[Isuckatprogramming]

Exactly as the title says.

I have a macro that checks to see if a condition is met in a range of cells, and then sends an email. I want to go one step further and have the contents of the cell to the left of the cell that triggered the macro to be part of my email body. Can anyone help. I am a novice. See below for my macros.

Email Macro:

Sub EmailNotification()
'For mail code examples visit my mail page at:
'http://www.rondebruin.nl/sendmail.htm
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 = ""
strcc = ""
strbcc = ""
strsub = "Surveillance Reminder - 30 Days"
strbody = ActiveSheet.Cells(ActiveCell.Row)





With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
'You can add a file to the mail like this
'.Attachments.Add
'.Send ' or use .Send
.Display
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Check Macro:

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

NotSentMsg = "Not Due"
SentMsg = "First Notification Sent"
SentMsg2 = "Second Notification Sent"
SentMsg3 = "Third Notification Sent"
SentMsg4 = "Final Notification Sent"

'Above the MyLimit value it will run the macro
MyLimit = 30


'Set the range with the Formula that you want to check
Set FormulaRange = Me.Range("D2:D13")

On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells

With FormulaCell
If IsNumeric(.Value) = False Then
MyMsg = "Not numeric"
Else
If .Value <= 30 Then
MyMsg = SentMsg
If .Offset(0, 1).Value = NotSentMsg Then
Call EmailNotification
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
End If
If .Value <= 20 Then
MyMsg = SentMsg2
If .Offset(0, 1).Value = SentMsg Then
Call EmailNotification2
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
End If
If .Value <= 10 Then
MyMsg = SentMsg3
If .Offset(0, 1).Value = SentMsg2 Then
Call EmailNotification3
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
End If
If .Value = 1 Then
MyMsg = SentMsg4
If .Offset(0, 1).Value = SentMsg3 Then
Call EmailNotification4
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
End If
End If
End If
End If
Else
MyMsg = NotSentMsg
End If

End If
Application.EnableEvents = False
.Offset(0, 1).Value = MyMsg
Application.EnableEvents = False
End With







Next FormulaCell



ExitMacro:
Exit Sub

EndMacro:
Application.EnableEvents = True

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

End Sub

 

[mrmmickle1]

Welcome to the board. Please use code tags (Select code text with mouse cursor then use the # in the above menu)

You can try something like this. Pay close attention to the notes and the red highlights:

[COLOR=#0000ff]Sub[/COLOR] EmailNotification([COLOR=#ff0000]myBody [/COLOR][COLOR=#0000ff]As String[/COLOR])


 [COLOR=#0000ff]   Dim [/COLOR]OutApp [COLOR=#0000ff]As Object[/COLOR]
 [COLOR=#0000ff]   Dim[/COLOR] OutMail [COLOR=#0000ff]As Object[/COLOR]
    
 [COLOR=#0000ff]   Set[/COLOR] OutApp = CreateObject("Outlook.Application")
 [COLOR=#0000ff]   Set[/COLOR] OutMail = OutApp.CreateItem(0)
    
[COLOR=#0000ff]    With [/COLOR]OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = [COLOR=#ff0000]myBody[/COLOR]
[COLOR=#008000]        'You can add a file to the mail like this[/COLOR]
[COLOR=#008000]        '.Attachments.Add[/COLOR]
[COLOR=#008000]        '.Send ' or use .Send[/COLOR]
        .Display
[COLOR=#0000ff]    End With[/COLOR]
    
   [COLOR=#0000ff] Set[/COLOR] OutMail = [COLOR=#0000ff]Nothing[/COLOR]
   [COLOR=#0000ff] Set [/COLOR]OutApp = [COLOR=#0000ff]Nothing[/COLOR]
    
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Sub[/COLOR] TestTheBody()
    
[COLOR=#0000ff]    Dim[/COLOR] SentMsg [COLOR=#0000ff]As String[/COLOR]
 [COLOR=#0000ff]   Dim[/COLOR] YourEmailBody [COLOR=#0000ff]As String[/COLOR]
    
[COLOR=#008000]    'Change String Variables to meet your needs...[/COLOR]
    SentMsg = "First Notification Sent"
[COLOR=#ff0000]    YourEmailBody = "Pass this email body to the email procedure....."[/COLOR]
    
[COLOR=#008000]    'Change Range Accordingly....[/COLOR]
[COLOR=#0000ff]    If[/COLOR] Range("A1").Value = SentMsg [COLOR=#0000ff]Then[/COLOR]
        [COLOR=#0000ff]Call [/COLOR]EmailNotification([COLOR=#ff0000]YourEmailBody[/COLOR]) [COLOR=#008000]'This passes the YourEmailBody variable into the Email notification email...[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]