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