Automatically send email based on cell value

[hockeyfan123]

I am fairly good with Excel formulas but a complete novice in Macros. I am looking to automatically send an e-mail to a specific person when the value of a cell (A2) changes without any human intervention to push a button. The person's email is located in cell B2 and my email is located in cell C2. The text of the email is simply: "There has been a change in the value of cell A2 and the new value is:

There are Excel Macros I have seen that will pop-up an e-mail when a cell value changes but, the person has to be there to push the send button for it to complete. In this case, there may not be anyone present at the PC to push a send button so, the entire process must be automated.

Thank you for any help you can provide!!

 

[hockeyfan123]

PS. I found something similar from mrmmickle1 from 7 years ago but, it is different enough that it does not appear to work for this purpose.

send email based on cell value when value is text

Hi, I am needing assistance in creating a macro to send an email to a certain person based on a cells value as being a text e.g. "Waiting for Admin". I currently use a drop down menu for cells in column AH. Based off the selection from the drop down I want it to send/display an email using...

www.mrexcel.com

Again, any help would be greatly appreciated!

 

[Michael M]

How does the cell change if there is no human intervention ?

 

[hockeyfan123]

How does the cell change if there is no human intervention ?

Hi Michael,

We are continuously gathering data from devices on our network using Excel's Web Query feature to gather HTML files from a statistics server. There are numerous pivot tables and calculations involved with respect to this data on numerous tabs but, because this data is confidential, I cannot share the actual spreadsheet.

There is a summary Tab in Excel that posts the key data gathered from the tabs into a handful of cells. These cells in the summary tab change each minute when the Excel Spreadsheet automatically gathers the latest data from via a web query in HTML and cell A2 is a key one that we watch for changes.

 

[Michael M]

Sorry I don't deal with web queries, never hadn anything to do with them.
However, have a look at this thread on another forum, that should point you in the right direction.

Event That Triggers When Cell Changed By Web Query? - OzGrid Free Excel/VBA Help Forum

I run a web query from http://moneycentral.msn.com/investor/external/excel/rates.asp to import the latest currency rates into Excel. I refresh the data every minute. Every time Cell B19 changes I want to run the following code: (Code, 3 lines) I tried…

forum.ozgrid.com

 

[hockeyfan123]

Hi Michael - This is very helpful and I appreciate it very much. That gives me a good starting point and let me see if I can wrap-that into some other examples of email generation code.

Thanks again!

 

[hockeyfan123]

So I now understand that this will require VBA. Per your suggestion, in order for VBA to recognize a web query, I will have to create a new class for VBA in addition to the normal one. The site you pointed me to show that this can be created as follows:

New VBA Class Code
Public WithEvents qt As QueryTable


Private Sub qt_AfterRefresh(ByVal Success As Boolean)
MsgBox ("I just refreshed")
End Sub


Then in a normal module put
Normal VBA Module Code


Dim MyQuery As New Class1
Sub Initialize_It()
Set MyQuery.qt = ThisWorkbook.Worksheets("Sheet1").QueryTables(1)
End Sub

If anyone can help from here in VBA - which I have not worked with in many years, to answer the initial question about automatically generating an e-mail with no human intervention, I would very much appreciate it.