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!

 

[PaulOPTC]

All Projects (Mr Excel).xlsm

drive.google.com

Is there a better way to send a workbook?

 

[PaulOPTC]

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

All Projects (Mr Excel).xlsm

drive.google.com

Is there a better way to send a workbook?

Thank you so much for your help also!

 

[gordsky]

All Projects (Mr Excel).xlsm

drive.google.com

Is there a better way to send a workbook?

Thank you so much for your help also!

my machine wont let me access that link. Tell me what cells the bits you want added to the email are in and where you want them in the email , main body, subject etc and i will have a go. if its not exact at least you will have a starting point and can manipulate the references

 

[PaulOPTC]

my machine wont let me access that link. Tell me what cells the bits you want added to the email are in and where you want them in the email , main body, subject etc and i will have a go. if its not exact at least you will have a starting point and can manipulate the references

Sure!, going back to the table

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H	Column I
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 recipitant of the email will always remain the same


Subject should be - "New Active Job - (Job number from column A)"

Main body: "There is a new active job, please add the information to quickbooks

(Job number Column A) - (Job address column B) - (Job name Column C)

The billing name is : (Billing name Column E)
Billing address : (Billing Address column F)
Billing Email Address: ( Contact Email column I)
Contact Name: (Column G)
Contact Phone Number : (Column H) "

Something along those lines, it would just obviously change depending on what jobs are marked active.

Thank you again!

 

[gordsky]

try this as a finished code. Ive changed a few bits so that if a job is turned active without using your macro it wont crash. Ive changed the "SEND" in email to "Display". That way you can check it before it sends. If you want it to auto send then just change it back.
The text can be tweaked to suit your needs within the code if youre not happy with the way it is

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

Dim i As Long
 
If target.count > 1 Then
    i = 4
Else
    i = 1
End If


If Not Application.Intersect(target(i), ActiveSheet.range("D:D")) Is Nothing And target(i) Like "*active*" Then
 
    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" & "<br>" & "<br>" & _
              "There is a new active job, please add the information to quickbooks." & "<br><br>" & _
               ActiveSheet.range("A" & target.Row) & " - " & ActiveSheet.range("B" & target.Row) & " - " & ActiveSheet.range("C" & target.Row) & "<br><br>" & _
              "The billing name is : " & ActiveSheet.range("E" & target.Row) & "<br>" & _
              "Billing address : " & ActiveSheet.range("F" & target.Row) & "<br>" & _
              "Billing Email Address: " & ActiveSheet.range("I" & target.Row) & "<br>" & _
              "Contact Name: " & ActiveSheet.range("G" & target.Row) & "<br>" & _
              "Contact Phone Number : " & ActiveSheet.range("H" & target.Row)
 
    On Error Resume Next
 
    With OutMail
        .To = "email"
        .cc = ""
        .Bcc = ""
        .subject = "New Active Job - " & range("A" & target.Row)
        .htmlBody = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .display 'or use .send
    End With
    On Error GoTo 0
 
    Set OutMail = Nothing
    Set OutApp = Nothing
Else
End If

End Sub

 

[PaulOPTC]

try this as a finished code. Ive changed a few bits so that if a job is turned active without using your macro it wont crash. Ive changed the "SEND" in email to "Display". That way you can check it before it sends. If you want it to auto send then just change it back.
The text can be tweaked to suit your needs within the code if youre not happy with the way it is

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

Dim i As Long
 
If target.count > 1 Then
    i = 4
Else
    i = 1
End If


If Not Application.Intersect(target(i), ActiveSheet.range("D:D")) Is Nothing And target(i) Like "*active*" Then
 
    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" & "<br>" & "<br>" & _
              "There is a new active job, please add the information to quickbooks." & "<br><br>" & _
               ActiveSheet.range("A" & target.Row) & " - " & ActiveSheet.range("B" & target.Row) & " - " & ActiveSheet.range("C" & target.Row) & "<br><br>" & _
              "The billing name is : " & ActiveSheet.range("E" & target.Row) & "<br>" & _
              "Billing address : " & ActiveSheet.range("F" & target.Row) & "<br>" & _
              "Billing Email Address: " & ActiveSheet.range("I" & target.Row) & "<br>" & _
              "Contact Name: " & ActiveSheet.range("G" & target.Row) & "<br>" & _
              "Contact Phone Number : " & ActiveSheet.range("H" & target.Row)
 
    On Error Resume Next
 
    With OutMail
        .To = "email"
        .cc = ""
        .Bcc = ""
        .subject = "New Active Job - " & range("A" & target.Row)
        .htmlBody = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .display 'or use .send
    End With
    On Error GoTo 0
 
    Set OutMail = Nothing
    Set OutApp = Nothing
Else
End If

End Sub

Hey! it works!!! its exactly how I want it! thank you!

one slight issue, and I am not sure if it is just me, it sends the same email twice? other then that it is exactly right!

 

[gordsky]

Hey! it works!!! its exactly how I want it! thank you!

one slight issue, and I am not sure if it is just me, it sends the same email twice? other then that it is exactly right!

Nothing in the code would make it send twice, unless when you changed the display you put send twice.

 

[PaulOPTC]

Nothing in the code would make it send twice, unless when you changed the display you put send twice.

Tested it again this morning and it worked and only sent one mail! , so maybe it was VPN lag (even when it was set as .display it would show two emails open)? Not sure, but again thank you so much for your help!!

 

[gordsky]

Tested it again this morning and it worked and only sent one mail! , so maybe it was VPN lag (even when it was set as .display it would show two emails open)? Not sure, but again thank you so much for your help!!

No problem, glad I could help