VBA copy to Outlook

[Me00550]

im trying to make so certain range copy into outlook when cells change value. i can get the first part of the code to work, cell changes value. but i cannot figure out how to get the cells to copy. everything i have tried has returned a blank email(including deleting the string body)

example of what i am trying accomplish:

cell H5 changes value--copy\paste value C5:J5.
cell H6 changes value--copy\paste value C6:J6.
and so forth down the spreadsheet

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim xRgSel As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim StrBody As String
    Dim xRngCop As Range
    
    On Error Resume Next
    StrBody = "Loose Parts Request"
    
                
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set xrg = Range("$H5:$H99999")
    Set xRgSel = Intersect(Target, xrg)
    Set xRngCop = Range("$C5:$J99999")
    ActiveWorkbook.Save
    If Not xRgSel Is Nothing Then
        Set xOutApp = CreateObject("Outlook.Application")
        Set xMailItem = xOutApp.CreateItem(0)
       
        With xMailItem
            .To = ""
            .Subject = "Loose Parts Request"
            .Body = StrBody
            
            
 
            
            .Display
        End With
        Set xRgSel = Nothing
        Set xOutApp = Nothing
        Set xMailItem = Nothing
    End If
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

[Me00550]

i am beginning to think this is impossible to achieve

[Dave]

No it's probably not possible within a worksheet change event. Make a separate sub and call it from the worksheet change event. Better yet code anything other than a worksheet change event... they are often recursive and produce unexpected results. I'm sure if U searched this site (including the knowledge base) there is likely a ready made code to achieve your objective. HTH. Dave

[Kenneth Hobs]

Change is by a manual change for the most part. If it is a change in formula value, that is another matter.

Another consideration is to capture multiple "changes" at once. e.g. Copy and pasted the value of 5 to cells H5, H7:H12. If it is always a one row in column H at a time, that is a more simple matter.

If multiple changes, I would make the macro copy the rows in each column H that changed to a scratch sheet. That range would then be emailed.

In all of these cases, most would use Ron de Bruin's RangeToHTML() routine for the .htmlBody. https://www.rondebruin.nl/win/s1/outlook/mail.htm

[Dave]

Thanks for the info Ken. Setting up a separate sheet area for output is almost where U should start a project re. "copy the rows in each column H that changed to a scratch sheet. That range would then be emailed.".... seems like an easy resolution. So does the change in formula value create a sheet event? Dave

[Kenneth Hobs]

If the change is by a formula, one uses a Calculate event and looks at Precidents. The rangers in the formula governs the solution. Attach a short simple file to get the best help.