Having a little problem copying cells into the body of my email. Found a nice looking code by Ken Puls and modified it for my situation. The problem I have now is it says that I get an Argument is not Optional error with the email.

Goal - to generate an email every time my target value is greater than 200 and add certain cells of information to that email and to have it email out to the email address in column X. I feel this should work just cannot figure out where code is not optional. Any help would be great.


I'm using excel 2010 and outlook.

First code is change event second is email.

If Not Application.Intersect(Range("v:v"), target) Is Nothing Then
    For Each Cell In Application.Intersect(Range("V:V"), target)
        If IsNumeric(target.Value) And target.Value > 200 Then Call Send_Selected_Mail_Only
        Next Cell
                
    End If
Public Sub Send_Selected_Mail_Only(cl As Range)
Dim objOL As Object
 Dim objMail As Object
 Dim sEmail As String
 Dim sEmailColumn As String
 Dim sSubject As String
 Dim sBody As String
 sEmailColumn = "x"
With cl.Parent
    sEmail = .Range(sEmailColumn & cl.Row)
    sSubject = "Agreement " & .Range("B" & cl.Row) & " requires urgent remediation!"
    sBody = "A new CCN has been entered:" & vbNewLine & .Range("A" & cl.Row) & _
    vbNewLine & vbNewLine & " Part Number:" & vbNewLine & .Range("H" & cl.Row) & _
    vbNewLine & vbNewLine & "serial number:" & vbNewLine & .Range("I" & cl.Row) & _
    vbNewLine & vbNewLine & "RMA Number:" & vbNewLine & .Range("V" & cl.Row) & _
    vbNewLine & vbNewLine & "Customer Notes/Issue/How Product Failed:" & vbNewLine & .Range("K" & cl.Row) & _
    vbNewLine & vbNewLine & "Customer Request:" & vbNewLine & .Range("R" & cl.Row)
 End With
On Error GoTo Cleanup
Set objOL = CreateObject("Outlook.Application")
 
 
 Set objMail = objOL.CreateItem(0)
 
 With objMail
    .To = sEmail
    .Subject = sSubject
    .Body = sBody
    .Display
 End With
Cleanup:
  Set objMail = Nothing
 Set objOL = Nothing
 On Error GoTo 0

End Sub