Auto email based on cell value including copy and paste

[daddyfoxuk]

Afternoon All,

I have the below vba code working (in a different sheet) but im looking at rather than having to use a command button when the final cell is scanned into it will automacially copy cells value and email them....?

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Front")
Set pasteSheet = Worksheets("Good Parts")

copySheet.Range("B5:I5").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

Range("C5,F5,G5,H5").Select
Range("C5").Activate
Selection.ClearContents
Range("C5").Select

ActiveWorkbook.Save

End Sub
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Front")
Set pasteSheet = Worksheets("Bad Parts")

copySheet.Range("B5:I5").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

Range("C5,F5,G5,H5").Select
Range("C5").Activate
Selection.ClearContents
Range("C5").Select

ActiveWorkbook.Save

End Sub

Sub Mail_with_outlook2(Rng As Range)

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

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

strbody = "Suspect Part Notification" & vbNewLine & vbNewLine & _
"Containment TA-420" & vbNewLine & vbNewLine & _
"Suspect Notification G+P Sorting Felicity Harris " & vbNewLine & vbNewLine & _
"Contact Team Leader For Further Information" & vbNewLine & vbNewLine & _
"Suspect Notification Part Removed" & vbNewLine & vbNewLine & _
"Pallet Number " & Rng(1).Value & " Part Number " & Rng(2).Value & vbNewLine & vbNewLine & _
"Replaced With Part " & Rng(5).Value & " From Pallet " & Rng(6).Value

On Error Resume Next
With OutMail
.to = "robert.hollows@partner.bmwgroup.com"
.CC = ""
.BCC = ""
.Subject = "Suspect Notification G+P Sorting Felicity Harris "
.Body = strbody
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Can anyone point me in the right direction....?

Thank you in advanced!