1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Generate emails based on conditions of cell

Discussion in 'Business Applications' started by ccheyne, Sep 16, 2016.

Thread Status:
Not open for further replies.
Advertisement
  1. ccheyne

    ccheyne Thread Starter

    Joined:
    Sep 16, 2016
    Messages:
    6
    I have some excel knowledge but am not too proficient in VBA. I am looking to generate emails based on the any of the conditions I have specified below for column G "Current Approved to Date"

    Conditions:
    1. =AND(($G9<Today()),($I9=""))
    2. =AND(((J9-G9>3)),($I9="Applied for Ext."))
    3. =AND((J9>G9),($I9="Active"))

    If any of these conditions are met I would like an email to be generated.
    (SIMILAR VBA) https://forums.techguy.org/threads/solved-automatic-email-alerts-using-excel.710581/page-2
     

    Attached Files:

  2. ccheyne

    ccheyne Thread Starter

    Joined:
    Sep 16, 2016
    Messages:
    6
    I use excel 2007 and Outlook email. I will probably need an "auto ok" program for security settings.
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,797
  4. ccheyne

    ccheyne Thread Starter

    Joined:
    Sep 16, 2016
    Messages:
    6
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,797
    Yes there is an And and Or statements in VBA the syntax is like this

    If (Cells(i, 4) > Date - 8) And (Cells(i, 4) <Date +1) Then

    You can have as many Ands between the If and the Then as needed and you can extend the code on to the next line using the _ symbol.

    That code would find any date between the current day and 7 days ago in row "i" ("i" is a counter) and column "D", you can use most mathematical symbols in VBA code and you can also use Range() instead of Cells().
     
  6. ccheyne

    ccheyne Thread Starter

    Joined:
    Sep 16, 2016
    Messages:
    6
    Interesting.... and the college days are coming back to me... I will try this out when I get some time tomorrow and let you know how it goes. Thanks
     
  7. ccheyne

    ccheyne Thread Starter

    Joined:
    Sep 16, 2016
    Messages:
    6
    I am confused as to what "i," the counter, is and what does the number after the "i" correspond too?
     
  8. ccheyne

    ccheyne Thread Starter

    Joined:
    Sep 16, 2016
    Messages:
    6
    Also, what if I have (3) "if...then" statements that need to be checked?

    Would I need this part of the code after each statement?:
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    toList = Cells(i, 6)
    eSubject = "Engineer " & Cells(i, 2) & " job list " & Cells(i, 1)
    eBody = "Hello All, " & Chr$(13) & Chr$(13) & "Engineer " & Cells(i, 2) & " Is at customer " & Cells(i, 3) & " in " & Cells(i, 4) & " is " & Cells(i, 5) & Chr$(13) & Chr$(13) & "Many Thanks"
    On Error GoTo Err_email
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,797
    The i counter is used to increment the Row that is looked at Cells(i, 6), so when i is 1 it would be row, i= 2 would be row 2 etc.

    Yes you can have 3 "nested" If then statements, or you can use if/then/else as well.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1178154

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice