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 :banghead:

[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.