-
VBA copy to Outlook
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
Code:
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
-
i am beginning to think this is impossible to achieve :banghead:
-
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
-
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
-
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
-
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.