Consulting

Results 1 to 9 of 9

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  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

Posting Permissions

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