Consulting

Results 1 to 9 of 9

Thread: Automaticaly send emails when specific cells reach a required value

  1. #1
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location

    Automaticaly send emails when specific cells reach a required value

    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.
    Last edited by Aussiebear; 04-25-2023 at 09:58 PM. Reason: Reduced the whitespace

  2. #2
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    Sorry to everyone.
    No need to bother you.
    I just figured out what was wrong.
    Thanks again for the help.

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Kindly post your solution so others may learn from it
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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.
    Last edited by Aussiebear; 04-25-2023 at 09:58 PM. Reason: Reduced the whitespace

  5. #5
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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.

  6. #6
    Quote Originally Posted by caudillo View Post
    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.

  7. #7
    the solution is already posted in #4?

  8. #8
    Banned VBAX Newbie
    Joined
    Jul 2022
    Posts
    5
    Location
    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.
    Last edited by Aussiebear; 07-28-2024 at 05:34 PM.

  9. #9
    Banned VBAX Newbie
    Joined
    Jul 2022
    Posts
    5
    Location
    Quote Originally Posted by inkaterу View Post
    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.
    Last edited by Aussiebear; 07-10-2024 at 11:26 AM. Reason: Removed spam link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •