Emailing in MS Excel when Condition Met

[fishax]

Hi,
Am a newbie and need some guidance. I would like to send an email via Outlook or Outlook Express, if Cell A1 value is greater than cell B1 in Excel. The email would have to contain the value of A1 and B1.

 

[SkipVought]

Hi,

Using the SendMail Method, you can send a workbook (Simple).

Other than that, you'll have to create an Outlook application object using the CreateObject Method et al, and send a message (less simple).

Take your pick.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[xlbo]

You also need to specify how the values are updated so you know which EVENT to hook into ie when the worksheet calculates? when it changes ? when it is activated?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fishax]

Can I use the SendMail Method to Email WorkBook to be triggered by an EVENT?

I was thinking of a periodic check process that sees that value A is greater than Value B in the appropriate Excel Cells. Not sure how this would be achieved, but just a preliminary thought of the approach.

Are there any other Tools available out there that can do the same thing ie Excel in combination with something else that can email me. Even if I am sendig output to a Text file upon a condition being met and another application sending me that text file output.

 

[xlbo]

yes - that was my point - HOW do you need to run the code that will do the email. You could attach it to a button that you clcik manually or you can attach it to an event in excel as per what I listed before

As a starter for 10 - to loop and check cells:

with sheets("Sheetname")

For i = 1 to 100

  If .Range("A" & i).value > .Range("B" & i).Value then
    'email commands in here
  else
  End If
Next i
end with

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fishax]

Thanks for the replies. I have managed to get somewhere. Currently I have the code running on manual press of a Button, but what I need to know is what Event to use when a cell or range of cells get updated (i.e as soon as I enter a value in a Cell...without having the need to Save..I would like the condition to be execute).

The other option I am considering is code firing after every x minutes (is there a way to achieve this?)

 

[PHV]

Have a look at the Change event procedure of the Worksheet object and at the Intersect method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886