quick vba question - how do you refer to a cell

[Muzza86]

I have a code that I can attach to a button in excel so that when clicked - it automatically sends an email of my choosing to whom I declare it to... HOWEVER.. that is fine when attached the macro to a button.

BUT, what I now need to do is to apply this code (or similar) to a change in a specific cell?

my problem is i cant for the life of me figure out how to do that in vba. i just don't know the commands to do that.

all i need is an example of someone using an If/else statement referring to a specific cell on the spreadsheet - (e.g. if c4 = something, send the email)


thanks.


and i can do the rest

[Muzza86]

to make it easier.. this is the code i have for the automatic email macro for a button

Sub emailnotification()

Dim OutApp As Object
Dim OutMail As Object
On Error Resume Next
Err.Clear
Set OutApp = GetObject(Class:="Outlook.Application")
If Err <> 0 Then Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Training Expiry Date Warning"
.Body = "This employee's current training is due to expire in 30 days - needs renewing"
.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Muzza86]

Thanks for reply John,

This is the code I'm trying, but i know its wrong but i don't know how to edit it accordingly.


Sub Expirydate()

If Range("ah21").Value = "YES" Then

Dim outapp As Object
Dim outmail As Object
On Error Resume Next
Err.Clear
Set outapp = GetObject(Class:="Outlook.Application")
If Err <> 0 Then Set outapp = CreateObject("Outlook.Application")
outapp.Session.Logon
Set outmail = outapp.CreateItem(0)

On Error Resume Next
With outmail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Training Expiry Date Warning"
.Body = "This employee's current training is due to expire in 30 days - needs renewing"
.Send
End With

Set outmail = Nothing
Set outapp = Nothing

End If
end sub

i know the dim as object parts are wrong and for 'objects' etc. but i don't know how to edit it