Automaticaly send emails when specific cells reach a required value

[caudillo]

Hello again.
I am at the final step regarding my .xlsm file.
This time I want to make my Excel file send automatically emails when certain cells reach a specific value.
The recipient's email adress in in cell E7, the cells which will trigger the emails are G15, G16 and G17, the subject of the email is in cell L8 and the bodies of the email are in cells L15, L16 and L17.
I have three email bodies because I am trying to address three different situations, each one represented by cells G15-G17.
What I have right now is the following:



Sub Email_From_Excel_()
Dim emailApplication As Object
Dim emailItem As Object
Set emailApplication = CreateObject("Outlook.Application")
Set emailItem1 = emailApplication.CreateItem(0)
Set emailItem2 = emailApplication.CreateItem(0)
Set emailItem3 = emailApplication.CreateItem(0)
emailItem.to = Sheets("Sheet1").Range("E7")
emailItem.Subject = Sheets("Sheet1").Range("L8")
If Sheets("Sheet1").Range("G15") <= 7 Then emailItem1.Body = Sheets("Sheet1").Range("L15")
If Sheets("Sheet1").Range("G16") <= 7 Then emailItem2.Body = Sheets("Sheet1").Range("L16")
If Sheets("Sheet1").Range("G17") <= 7 Then emailItem3.Body = Sheets("Sheet1").Range("L17")
If Sheets("Sheet1").Range("G15") <= 7 Then emailItem1.Send
If Sheets("Sheet1").Range("G16") <= 7 Then emailItem2.Send
If Sheets("Sheet1").Range("G17") <= 7 Then emailItem3.Send
Set emailItem = Nothing
Set emailApplication = Nothing
End Sub


But when I try to run the code I get a run-time error 424 "Object required".
Any ideas on how to fix this?
Thank you.

[caudillo]

Sorry to everyone.
No need to bother you.
I just figured out what was wrong.
Thanks again for the help.

[Aussiebear]

Kindly post your solution so others may learn from it

[caudillo]

You are right Aussiebear, my mistake.
Here is the code I used:


Sub Email_From_Excel_()
Dim emailApplication As Object
Dim emailItem1 As Object
Dim emailItem2 As Object
Dim emailItem3 As Object
Set emailApplication = CreateObject("Outlook.Application")
Set emailItem1 = emailApplication.CreateItem(0)
Set emailItem2 = emailApplication.CreateItem(0)
Set emailItem3 = emailApplication.CreateItem(0)
emailItem1.to = Sheets("Sheet1").Range("E7")
emailItem2.to = Sheets("Sheet1").Range("E7")
emailItem3.to = Sheets("Sheet1").Range("E7")
emailItem1.Subject = Sheets("Sheet1").Range("L8")
emailItem2.Subject = Sheets("Sheet1").Range("L8")
emailItem3.Subject = Sheets("Sheet1").Range("L8")
If Sheets("Sheet1").Range("G15") <= 7 Then emailItem1.Body = Sheets("Sheet1").Range("L15")
If Sheets("Sheet1").Range("G16") <= 7 Then emailItem2.Body = Sheets("Sheet1").Range("L16")
If Sheets("Sheet1").Range("G17") <= 7 Then emailItem3.Body = Sheets("Sheet1").Range("L17")
If Sheets("Sheet1").Range("G15") <= 7 Then emailItem1.Send
If Sheets("Sheet1").Range("G16") <= 7 Then emailItem2.Send
If Sheets("Sheet1").Range("G17") <= 7 Then emailItem3.Send
Set emailItem = Nothing
Set emailApplication = Nothing
End Sub

As you can easily see I just had to add the missing objects.
I hope someone benefits from this.

[caudillo]

Hello again.
As I was working with the code to send emails I realized that in order to receive the email notification, I had to open the workbook and run the module manually.
I was under the impression that the second If statements would "automate" the whole process.
Is there any way we can achieve exactly that, to make the excel file send email notifications automatically?
Any idea would be greatly appreciated.
Thanks.

[expecttell]

Hello again.
As I was working with the code to send emails I realized that in order to receive the email notification, I had to open the workbook and run the module manually.
I was under the impression that the second If statements would "automate" the whole process.
Is there any way we can achieve exactly that, to make the excel file send email notifications automatically?
Any idea would be greatly appreciated.
Thanks.
You will save a lot of time if you can automate that process. I'm hoping for a solution.

[arnelgp]

the solution is already posted in #4?

[inkaterу]

Your Excel project sounds super cool. I remember diving into automation myself. Regarding your runtime error, it seems like the code's missing object references.

[inkaterу]

Your Excel project sounds super cool. I remember diving into automation myself. Regarding your runtime error, it seems like the code's missing object references.
Try declaring "emailItem" as "Object" instead of "emailItem1", "emailItem2", etc. Also, ensure Outlook is properly set up.