• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA macro to send email when my criteria is met...

mudo2219

New Member
I am fairly new with VBA and need your help to write a macro that will send an email automatically once a criteria is met. My workbook is being updated every minute through a query, and the information is constantly changing. Let's say I want to send an email through Outlook if any cell in column Y meets a criteria based upon a formula that cell already has. I have the specific email in column R that is also being updated every time the query updates. The subject of the email will be in column A combined with cell $AB$2. Finally the body of the email will be a combination of data in column E and column O, which is changing row after row.

I hope you can help me with this, and thank you in advance for your time.

Attached is a dummy version of the file I am running.

Thanks a lot!
 

Attachments

  • TEST.xlsx
    10.7 KB · Views: 11
Hi, mudo2219!

There're a lot of wheels yet made, so why invent a new one? If you can manage to handle with a bit of VBA these are a couple of the classical links to mail from Excel:
http://www.rondebruin.nl/win/s1/outlook/mail.htm
http://www.cpearson.com/excel/Email.aspx

In your case, once built a procedure to send mail using any of these methods, you have to build another procedure to go through each line, checking criteria at column Y, and when met, call the send mail procedure.

As you say that data is updated thru a query every minute, you'll have to include the call to the previous procedure at the end of the query and test that timing is fine and there's enough time to send all the mails before next update happens.

It'll be useful to know about how many records are we talking about on each update, if they're added at the end of existing data of if they replace it, how many time takes the update, and if you have to do anything more than sending mails.

Regards!
 
Thank You SirJB7,

The records vary since its something that is updated manually on a programs that then excel extracts the information from. The data is added at the end of the existing data not replacing what we already have. At the end of the day we have about 200 records only. I only need to send the email, but there will be 2 scenarios to send an email, this would be the way to escalate the issue.
 
Hi, mudo2219!
A bit confused with the update frequency and the no. of records at the end of the day.
a) The figure 200 represent the total of records added during a day?
b) If the update frequency via query is 1', that means that only one record each 2' is added? (7hx60m=420, 420/200?2,1)
c) Describe the 2 scenarios that should trigger the mail.
Regards!
PS: BTW, language of columns Y:Z differ from previous columns :rolleyes:... and sounds familiar to me.
 
Do I understand the first email may be line 1 (if it meets the criteria) and the next email may be ??? which row ?

What is the criteria ?
 
Hello SirJB7:

The number of records is not always the same as that depends on the number of orders that are being entered into the system every day. I only gave you an estimate. We may even get more records if we pull the data for a longer date range.

In regards of the 2 scenarios:
1) if the order has not been processed (still appears as "APRO") for over 15 minutes, then an email needs to be send out to the JG.
2) if the order has not been processed (still appears as "APRO") for over 30 minutes, then an email needs to be send out tonthe ING.

Thank you for taking the time to help me with this.

Regards,
 
Do I understand the first email may be line 1 (if it meets the criteria) and the next email may be ??? which row ?

What is the criteria ?

Hello Logit,

Attached you will find the file with dummy information, the criterias are found on columns X and Y with a formula. If the formula turns blank or false, I do not want to perform any tasks. If the formula returns either "LLAMAR JG" or "LLAMAR ING" I want to send an email to the email I have on column R with a subject that combines what I have on cell $A$3 and the value on the range $A4 through $A8. Finally I want the body to combine data in column E and G.
 

Attachments

  • TEST.xlsx
    10.7 KB · Views: 20
Back
Top