How to send an email when a cell is filled or approved.

[UMAKEMESIK]

To all,

We have an excel sheet with many columns of data.

at the end of each row we have a column titled - Approved.

What we would like is that once we put any character in the approved column

the sheet will auto generate an outlook email and send it to a specified address.

for example.

once the item is approved I will type the word approved in the cell and the presence
of a character in the cell will prompt the VB to send the approval email.


the approved column in the 15th column over from the left.


thanks in advance for any help.

 

[Logit]

.
It would be great to see an example of your worksheet. You can post a copy to DROP BOX or similar free cloud service.
Having a copy of your worksheet avoids alot of questions and problems.

When APPROVED is placed in the column, what is the content of the email ?
What email address are you sending the email to ?

 

[UMAKEMESIK]

Thanks for the help.

its a pretty simple sheet.

15 columns across.

No other macros on the sheet.

When any character is put into the cell in the 15th column an email response will be triggered.

The email will go to only one or two email addresses , can use cc or bcc.

Subject line is simple - Tooling has been approved.

Body of the email:

would like this in the body.


The tooling listed below is available for use and has been approved.

P:urchase Order# - 8101 ( pulls from column a)
Customer - box buyer (( pulls from column d)
Design #- 1234 (( pulls from column f)
Ident # - 54321 ( pulls from column e)
Machine - Rotary ( pulls from column k)
Verified By - Designer ( pulls from column g)


-------------------------

Have similar items in other pages as shown below, but I could not hack to
use in this situation.









This is an example of what I currently have but could not figure out how to hack to use in my situation as there is no v lookup.

body of email

------------------------------------------------------------------------------------------------

A NEW PROJECT HAS BEEN RECEIVED AND LOGGED IN.
Please Contact Sales and/or C/S if you did not initiate this request.
As a general rule:
-All new projects are assigned to the DOD
-Please send electronic files and/or samples if needed to complete the project
-Same day request should be coordinated with the DOD
-All BLUE line items on the SR Log are in development
-Will call requests will be at the Green Rack in front of shipping
***************************************
Sample Request# - 8101
Sales Person - User
Customer Service Rep - User
Distributor - Customer
End User - same
Designer - User
***************************************
Please DO NOT reply

----------------------------------------------------------------------------------

the code for this is below, or one version of it.
text may be a little different.

With Sheets(1)
        strBody = "<I><font size = 5>Your project is complete.<BR>Please Contact Sales and/or C/S if you did not initiate this request." & "</FONT>" & "</I>" & "<p>" & vbCr & _
                    "<FONT COLOR = BLUE><font size = 4>As a general rule:" & "</font color>" & "</FONT>" & "<p>" & vbCr & _
                    "<FONT COLOR = BLUE>-Designs that do not require review or assembly are marked complete when sent to the sample table" & "</font color>" & "<BR>" & vbCr & _
                    "<FONT COLOR = BLUE>-Designs sent to the table by 2:30pm, for UPS, will ship the same day" & "</font color>" & "<BR>" & vbCr & _
                    "<FONT COLOR = BLUE>-Designs sent to the table by  5:00pm, for Reno, will ship the same day" & "</font color>" & "<BR>" & vbCr & _
                    "<FONT COLOR = BLUE>-Designs sent to the table by 8:00pm, for all other Truck Shipments, will ship the same day" & "</font color>" & "<BR>" & vbCr & _
                    "<FONT COLOR = BLUE>-Will call requests are at the Green Rack at the time designated in the Sample Request" & "</font color>" & "<BR>" & vbCr & _
                    "<FONT COLOR = BLUE>-Designs to be picked up by Salesperson/Requestor are available by 8:00am the next day, or the specific time designated in the Sample Request" & "</font color>" & "<P>" & vbCr & _
                    "***************************************<FONT COLOR = black><font size = 5>  " & "</font color>" & "</FONT>" & "<p>" & vbCr & _
                    "Sample Request# -<FONT COLOR = RED><font size = 4> " & .Range("A" & ActiveCell.Row).Value & "</font color>" & "</FONT>" & "<BR>" & vbCr & _
                  "Sales Person -<FONT COLOR = RED><font size = 4>  " & .Range("B" & ActiveCell.Row).Value & "</font color>" & "</FONT>" & "<BR>" & vbCr & _
                  "Customer Service Rep -<FONT COLOR = RED><font size = 4>  " & .Range("C" & ActiveCell.Row).Value & "</font color>" & "</FONT>" & "<BR>" & vbCr & _
                  "Distributor -<FONT COLOR = RED><font size = 4>  " & .Range("D" & ActiveCell.Row).Value & "</font color>" & "</FONT>" & "<BR>" & vbCr & _
                  "End User -<FONT COLOR = RED><font size = 4>  " & .Range("E" & ActiveCell.Row).Value & "</font color>" & "</FONT>" & "<BR>" & vbCr & _
                  "Completion Date -<FONT COLOR = RED><font size = 4>  " & .Range("I" & ActiveCell.Row).Value & "</font color>" & "</FONT>" & "<BR>" & vbCr & _
                "Project Name -<FONT COLOR = RED><font size = 4>  " & .Range("K" & ActiveCell.Row).Value & "</font color>" & "</FONT>" & "<p>" & vbCr & _
                    "*************************************** <FONT COLOR = black><font size = 5> " & "</font color>" & "</FONT>" & "<p>" & vbCr & _
                "<I><font size = 4>Please DO NOT reply<FONT COLOR = black> " & "</font color>" & "</FONT>" & "</I>" & "<p>"