Email information using an email address in a spreadsheet and supporting data from the spreadsheet

[DJAnthonyMark]

I know i am close. I successfully send an email to me@anywhere.com when a value is entered into customers credit amount, however it does not contain the information I want to send.

What I would like,

an email to be sent out via Outlook to the sales person email for the account that contains "Credit Approved - Current Date - Customer Name - Customers Credit Amount" in the subject and body. A CC would be sent to salesmanager@anywhere.net.

I have not written VB in about 8 years, and I am unsure how to select the information from the cell for the specific column.

Please Help

Spreadsheet:
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-size:14.0pt; font-weight:700; text-decoration:underline; text-underline-style:single; text-align:center; vertical-align:middle; border:.5pt solid windowtext; white-space:normal;} .xl64 {font-size:14.0pt; font-weight:700; text-decoration:underline; text-underline-style:single; mso-number-format:"m\/d\/yy\;\@"; text-align:center; vertical-align:middle; border:.5pt solid windowtext; white-space:normal;} .xl65 {font-size:14.0pt; font-weight:700; text-decoration:underline; text-underline-style:single; mso-number-format:"Short Date"; text-align:center; vertical-align:middle; border:.5pt solid windowtext; white-space:normal;} .xl66 {font-size:14.0pt; font-weight:700; text-decoration:underline; text-underline-style:single; mso-number-format:"Short Date"; text-align:center; vertical-align:middle; border:.5pt solid windowtext; background:#FCD5B4; mso-pattern:black none; white-space:normal;} .xl67 {font-size:14.0pt; font-weight:700; text-decoration:underline; text-underline-style:single; mso-number-format:"_\(\0022$\0022* \#\,\#\#0\.00_\)\;_\(\0022$\0022* \\\(\#\,\#\#0\.00\\\)\;_\(\0022$\0022* \0022-\0022??_\)\;_\(\@_\)"; text-align:center; vertical-align:middle; border:.5pt solid windowtext; white-space:normal;} .xl68 {text-align:left; vertical-align:middle; border:.5pt solid windowtext;} .xl69 {mso-number-format:"m\/d\/yy\;\@"; text-align:center; vertical-align:middle; border:.5pt solid windowtext;} .xl70 {mso-number-format:"Short Date"; text-align:center; vertical-align:middle; border:.5pt solid windowtext;} .xl71 {text-align:center; vertical-align:middle; border:.5pt solid windowtext;} .xl72 {mso-number-format:"Short Date"; text-align:center; vertical-align:middle; border:.5pt solid windowtext; background:#FCD5B4; mso-pattern:black none;} .xl73 {mso-number-format:"_\(\0022$\0022* \#\,\#\#0\.00_\)\;_\(\0022$\0022* \\\(\#\,\#\#0\.00\\\)\;_\(\0022$\0022* \0022-\0022??_\)\;_\(\@_\)"; text-align:center; vertical-align:middle; border:.5pt solid windowtext;} .xl74 {mso-number-format:"m\/d\/yy\;\@"; text-align:center; border:.5pt solid windowtext;} .xl75 {mso-number-format:"Short Date"; text-align:center; border:.5pt solid windowtext;} .xl76 {border:.5pt solid windowtext;} .xl77 {mso-number-format:"_\(\0022$\0022* \#\,\#\#0\.00_\)\;_\(\0022$\0022* \\\(\#\,\#\#0\.00\\\)\;_\(\0022$\0022* \0022-\0022??_\)\;_\(\@_\)"; border:.5pt solid windowtext;} .xl78 {text-align:center; border:.5pt solid windowtext;} .xl79 {mso-number-format:"Short Date"; border:.5pt solid windowtext; background:#FCD5B4; mso-pattern:black none;} --> </style> <table style="border-collapse: collapse;width:1981pt" border="0" cellpadding="0" cellspacing="0" width="1981"> <col style="mso-width-source:userset;mso-width-alt:8234; width:193pt" span="3" width="193"> <col style="mso-width-source:userset;mso-width-alt:4992;width:117pt" width="117"> <col style="mso-width-source:userset;mso-width-alt:7893;width:185pt" width="185"> <col style="mso-width-source:userset;mso-width-alt:8960;width:210pt" width="210"> <col style="mso-width-source:userset;mso-width-alt:6784;width:159pt" width="159"> <col style="mso-width-source:userset;mso-width-alt:5248;width:123pt" width="123"> <col style="mso-width-source:userset;mso-width-alt:5248;width:123pt" width="123"> <col style="mso-width-source:userset;mso-width-alt:7338;width:172pt" width="172"> <col style="mso-width-source:userset;mso-width-alt:4736;width:111pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:5802;width:136pt" width="136"> <col style="width:66pt" width="66"> <tbody><tr style="height:72.0pt" height="72"> <td class="xl63" style="height:72.0pt;width:193pt" height="72" width="193">Customer</td> <td class="xl63" style="border-left:none;width:193pt" width="193">Our Sales Person</td> <td class="xl63" style="border-left:none;width:193pt" width="193">Our SP Email</td> <td class="xl64" style="border-left:none;width:117pt" width="117">Date of Application</td> <td class="xl63" style="border-left:none;width:185pt" width="185">Credit Reference Request Sent</td> <td class="xl63" style="border-left:none;width:210pt" width="210">2nd Credit Reference Request Sent</td> <td class="xl63" style="border-left:none;width:159pt" width="159">Credit Reference Received</td> <td class="xl63" style="border-left:none;width:123pt" width="123">Credit.net Ref. Date</td> <td class="xl65" style="border-left:none;width:123pt" width="123">Folder Deliverd to Accounting</td> <td class="xl66" style="border-left:none;width:172pt" width="172">Estimated Completion Date DO NOT EDIT (Works Automatically)</td> <td class="xl63" style="border-left:none;width:111pt" width="111">Credit Approval?</td> <td class="xl67" style="border-left:none;width:136pt" width="136"> Customers Credit Amount </td> <td class="xl63" style="border-left:none;width:66pt" width="66">Email Sent To Sales Person?</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl68" style="height:15.0pt;border-top:none" height="15">Company 1
</td> <td class="xl68" style="border-top:none;border-left:none"> Sales Person 1
</td> <td class="xl68" style="border-top:none;border-left:none"> salesperson@anywhere.net</td> <td class="xl69" style="border-top:none;border-left:none">8/10/11</td> <td class="xl70" style="border-top:none;border-left:none">8/10/11</td> <td class="xl70" style="border-top:none;border-left:none">NA</td> <td class="xl70" style="border-top:none;border-left:none">8/11/11</td> <td class="xl71" style="border-top:none;border-left:none"> </td> <td class="xl70" style="border-top:none;border-left:none">8/11/11</td> <td class="xl72" style="border-top:none;border-left:none">8/12/11</td> <td class="xl71" style="border-top:none;border-left:none">Yes</td> <td class="xl73" style="border-top:none;border-left:none"> </td> <td class="xl71" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl68" style="height:15.0pt;border-top:none" height="15">Company 2
</td> <td class="xl68" style="border-top:none;border-left:none"> Sales Person 2
</td> <td class="xl68" style="border-top:none;border-left:none"> salesperson2@anywhere.net</td> <td class="xl69" style="border-top:none;border-left:none">8/10/11</td> <td class="xl70" style="border-top:none;border-left:none">8/10/11</td> <td class="xl70" style="border-top:none;border-left:none">8/11/11</td> <td class="xl70" style="border-top:none;border-left:none">8/11/11</td> <td class="xl71" style="border-top:none;border-left:none"> </td> <td class="xl70" style="border-top:none;border-left:none">8/11/11</td> <td class="xl72" style="border-top:none;border-left:none">8/12/11</td> <td class="xl71" style="border-top:none;border-left:none">Yes</td> <td class="xl73" style="border-top:none;border-left:none"> </td> <td class="xl71" style="border-top:none;border-left:none"> </td> </tr> </tbody></table>


WORKSHEET:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("K:K"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 0 Then
Call Mail_small_Text_Outlook
End If
End If
End Sub

MACRO:
Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

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

strbody = "Credit Update" & vbNewLine & vbNewLine & _
"Line 1" & vbNewLine & _
"Line 2" & vbNewLine & _
"Line 3" & vbNewLine & _
"Notification - " & Range("A2")

On Error Resume Next
With OutMail
.To = "me@anywhere.com"
.CC = ""
.BCC = ""
.Subject = "Notification - " & Range("A2")
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[jbeaucaire]

I had to change the WS_CHange macro to watch column L, since column K was the "yes/no" approved column, and column L looks like where you enter the credit amount...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("L:L"), Target) Is Nothing Then
    If IsNumeric(Target.Value) And Target.Value > 0 Then
        Call Mail_small_Text_Outlook(Target.Row)
    End If
End If
End Sub

Sub Mail_small_Text_Outlook(Rw As Long)
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

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

'Credit Approved - Current Date - Customer Name - Customers Credit Amount
strbody = "Credit Update:" & vbNewLine & vbNewLine & _
    "    Credit Approved: " & Range("K" & Rw) & vbNewLine & _
    "    Current Date: " & Date & vbNewLine & _
    "    Customer Name: " & Range("A" & Rw) & vbNewLine & _
    "    Credit Amount: " & Range("L" & Rw)

On Error Resume Next
With OutMail
    .To = "me@anywhere.com"
    .CC = ""
    .BCC = ""
    .Subject = "Notification - " & Range("A" & Rw)
    .Body = strbody
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
    .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[DJAnthonyMark]

Thank you everyone for your help. I used this macro for another sheet that uses "Y" as the triger. How do I write for this?

==
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("Y:Y"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 0 Then
Call Mail_small_Text_Outlook(Target.Row)
End If
End If
End Sub

Also, is there a book by Mr. Excel that would help me.

 

[jbeaucaire]

Based on the question, it appears you have made the only edit necessary.