Need Help generating an email based on a value in a cell

[thardin17]

I have never used VBA before until trying to learn to write this code. I am trying to look at the value in a specific cell (I2) and if it reads "Time to call" then I want it to send an email with specific text and values of additional cells in that row of data. Once I get the code to work in one row of data, I would like the code to then repeat itself through the entire worksheet. I currently have only about 250 lines but the worksheet will continue to grow. When I run my Macro I get the "Run-time error '1004': Application-defined or object define error". I cannot figure out where my mistake is and how to fix. Any help is appreciated.

Sub DataCheck()

If Range("I2").Value = "Time to call" Then Call FinalEmail
End Sub


Sub FinalEmail()

Dim emailApplication As Object
Dim emailItem As Object


Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)


emailItem.to = "troy_hardin@zeeco.com"

emailItem.Subject = "Tickler File Alert"

emailItem.Body = "Contact" & vbNewLine & _
Range("A2+B2") & vbNewLine & _
"about their" & vbNewLine & _
Range("C2").Value

emailItem.display

Set emailItem = Nothing
Set emailApplication = Nothing


End Sub

 

[Logit]

Option Explicit

Sub DataCheck()

If Range("I2").Value = "Time to call" Then Call FinalEmail
End Sub


Sub FinalEmail()

Dim emailApplication As Object
Dim emailItem As Object


Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)


emailItem.to = "troy_hardin@zeeco.com"

emailItem.Subject = "Tickler File Alert"

emailItem.Body = "Contact" & vbCrLf & _
Range("A2") & Range("B2") & vbCrLf & _
"about their" & vbCrLf & _
Range("C2").Value

emailItem.display

Set emailItem = Nothing
Set emailApplication = Nothing


End Sub

 

[thardin17]

Option Explicit

Sub DataCheck()

If Range("I2").Value = "Time to call" Then Call FinalEmail
End Sub


Sub FinalEmail()

Dim emailApplication As Object
Dim emailItem As Object


Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)


emailItem.to = "troy_hardin@zeeco.com"

emailItem.Subject = "Tickler File Alert"

emailItem.Body = "Contact" & vbCrLf & _
Range("A2") & Range("B2") & vbCrLf & _
"about their" & vbCrLf & _
Range("C2").Value

emailItem.display

Set emailItem = Nothing
Set emailApplication = Nothing


End Sub

Thank you for the help. It works now. Is there a way to copy this and have the cell references move 1 row down and repeat through the whole worksheet?

 

[thardin17]

I found some looping code and it works but I need the macro to reference a new row of data on each subsequent loop. In my email body on the first run it needs to look at "I2" and give me data from "A2:C2" as it is written below. But on the second loop, I need it to look at "I3" and give me data from "A3:C3" instead of row 2. Is there a good way to do this? Or is it even possible?

Sub DataCheck()

If Range("I2").Value = "Time to call" Then Call FinalEmail

End Sub



Sub FinalEmail()

Dim emailApplication As Object
Dim emailItem As Object


Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)


emailItem.to = "troy_hardin@zeeco.com"

emailItem.Subject = "Tickler File Alert"

emailItem.Body = "Contact" & " " & Range("A2") & " " & Range("B2") & " " & "about their" & " " & Range("C2").Value

emailItem.display

Set emailItem = Nothing
Set emailApplication = Nothing


End Sub

Sub Cell_Loop()

Dim cell As Range

'Loop through each cell in a cell range
For Each cell In ActiveSheet.Range("I2:I4")
Call DataCheck
Next cell

End Sub