Send an email when a cell says something specific

[PaulOPTC]

Hey!

This forum has been a huge help thus far, and I am coming here again with a potentially tough one. Here's what I would like to do:

When a cell says the word "Active" or "Service Active" it sends my coworker an email letting them know to add it to quick books.

Here is what it looks like: It is a running list of all of our jobs.


it is currently a table:

Job Number	Job address	Job name	STATUS ***	Billing name	Billing address	Contact name	Contact phone number	Contact email
1500	123 job lane	Lights	Bid	john smith
1501	456 Job dr	Camera	Service Bid	joe smith
1502	789 job st	action	Active	Jane smith

the 4th column "Status" is the column I am talking about.

When we are awarded a job, we change the status from Bid to Active (or service bid to service active)

I would like this process to automatically send an email straight to our billing manager so they can add it to quickbooks. That's the top priority, often times we are falling behind on billing because she does not know it is ready to be billed.

It needs to at least be able to send her the job number, but
It would be even better if the email could have the "Billing name - Contact email" as well.

Here is what I have:


Sub Mail_small_Text_Outlook()
'For Tips see: Excel Automation - Ron de Bruin
'Working in Office 2000-2016
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hey So and so" & vbNewLine & vbNewLine & _


On Error Resume Next
With OutMail
.To = "email"
.CC = ""
.BCC = ""
.Subject = "New Active Job"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub



So that is great, and it works as a button, but I would like that to go off whenever someone changes it from bid to active, or from service bid to service active.

Something to also consider though, is sometimes they add jobs into the spreadsheet that never go to bid, they start off as active, I would also like those jobs to message her.






I am not sure how to go about this?

Do I made a new hidden page in the spread sheet and then somehow have it compare the active jobs with itself to see if anything has changed?
Its not even like I can sort it by job number and do it for only new jobs. For example we are on Job 1540, but job 1480 just became active because it wasn't awarded for months.

or perhaps if we have it talk to file explorer, if a new job is added into the "Active" folder on our server, maybe then it emails her?

or perhaps if it can see if the status has changed? like if it checks it when the file first opens, verse before it closes? I just don't know how to go about this.

I am really not sure if I am over thinking it and there is a nice easy solution.

Thank you for your help!

 

[gordsky]

Maybe something like this in the sheet code

Option Compare Text
Private Sub Worksheet_Change(ByVal target As range)

If Not Application.Intersect(target, ActiveSheet.range("D:D")) Is Nothing And target Like "*active*" Then
 
     'insert your email code here

Else
End If

End Sub

 

[PaulOPTC]

Maybe something like this in the sheet code

Option Compare Text
Private Sub Worksheet_Change(ByVal target As range)

If Not Application.Intersect(target, ActiveSheet.range("D:D")) Is Nothing And target Like "*active*" Then
 
     'insert your email code here

Else
End If

End Sub

Let me give try, for my own knowlage, how does this work?

And would there be a way to add that if it does see the change, it takes whatever is in the A column and the row in which it has changed and adds that to the email? Because that would be the job number associated with the change.

 

[gordsky]

Let me give try, for my own knowlage, how does this work?

And would there be a way to add that if it does see the change, it takes whatever is in the A column and the row in which it has changed and adds that to the email? Because that would be the job number associated with the change.

the code works and is activated by cell change within the worksheet. If it matches the two criteria in the "IF" statement it will run your email code.
yes it would be possible to add the cell info to the email but would need to know how you wanted the email set out and the layout of your worksheet

 

[PaulOPTC]

the code works and is activated by cell change within the worksheet. If it matches the two criteria in the "IF" statement it will run your email code.
yes it would be possible to add the cell info to the email but would need to know how you wanted the email set out and the layout of your worksheet

It doesnt seem to do anything, maybe I did it wrong.

I inserted it in the "This workbook" section of the code.

Then I copied my email code and inserted it where your comment said too, then I tried adding a job into active, and tried changing an existing one to active. neither did anything?

I can send you my sheet if youd like?

 

[gordsky]

it doesnt go in the 'This Workbook' it needs to go in the sheet code. Right Click on tab name and select code and then enter it there

 

[PaulOPTC]

it doesnt go in the 'This Workbook' it needs to go in the sheet code. Right Click on tab name and select code and then enter it there

Okay! it works if I change it from bid to active! Thats awesome! It does not work however, if I add in a new job onto the sheet. it gives me a run-time error 13 – Type mismatch
and shows the

If Not Application.Intersect(target, ActiveSheet.Range("D:D")) Is Nothing And target Like "*active*" Then

Highlighted



So this must because of the way I insert a new job into that sheet.
the way that it works is I have a macro that copies the information from one sheet then
"insert copied cells"
onto that table.

so its giving me an error when I try to insert the copied cells.
I can maybe have the macro copy all the other columns first except for D and then do D last?

 

[gordsky]

Okay! it works if I change it from bid to active! Thats awesome! It does not work however, if I add in a new job onto the sheet. it gives me a run-time error 13 – Type mismatch
and shows the

If Not Application.Intersect(target, ActiveSheet.Range("D:D")) Is Nothing And target Like "*active*" Then

Highlighted



So this must because of the way I insert a new job into that sheet.
the way that it works is I have a macro that copies the information from one sheet then
"insert copied cells"
onto that table.

so its giving me an error when I try to insert the copied cells.
I can maybe have the macro copy all the other columns first except for D and then do D last?

that is exactly why it is giving you the run time. If you could modify your other code to insert the job and then manually enter "active" it will work.
If you wanted to attach a copy of your workbook I will look at your second request re email

 

[gordsky]

as a test try replacing

If Not Application.Intersect(target, ActiveSheet.range("D:D")) Is Nothing And target Like "*active*" Then

with

If Not Application.Intersect(target(4), ActiveSheet.range("D:D")) Is Nothing And target(4) Like "*active*" Then

I think that may stop the type mismatch if you use your original macro to populate your job

 

[PaulOPTC]

as a test try replacing

If Not Application.Intersect(target, ActiveSheet.range("D:D")) Is Nothing And target Like "*active*" Then

with

If Not Application.Intersect(target(4), ActiveSheet.range("D:D")) Is Nothing And target(4) Like "*active*" Then

I think that may stop the type mismatch if you use your original macro to populate your job

That absoloutly did! Looks like that worked when adding a new job, with out me having to change my macro!

So the last thing we need to figure out, is how to edit the email to show what job number has been changed ( and ideally add in the email the job address name and other billing information)

I am trying to remove all of the address and whatnot off my sheet so that I can send it to you, ill be done in just a second