I am using the following to display whichever cell is highlighted in Column D
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cells As Range
Set cells = ActiveSheet.Range("D19:D500")
If Not (Intersect(Target, cells) Is Nothing) Then
ActiveSheet.Range("M3").Value = Target.Value
End If
End Sub
I want to create an email and include the the value that is shown using the value that is the result from above however when I use the below I am getting an error because the value dissapears from Cell M3.
Private Sub CommandButton2_Click()
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set rng = Selection.SpecialCells(xlCellTypeVisible)
On Error Resume Next
With OutMail
.To = "enterprise@xxxx.com"
.Cc = "kristin@xxxxx.com; proltl@xxxxx.com"
.Subject = "Walmart Order(s): MISSING/DELAYED"
.HTMLBody = "Hi Team, " & Chr(10) & "<br>" & "<br>" & _
Chr(10) & "Please check on PRO " & Range("M3").Value & "<br>" & "Delivery Scheduled: " & Range("N9").Value & "<br>" & "<br>" & _
Chr(10) & "Delivery of this order appears to be delayed. Please provide an update at your earliest convenience" & "<br>" & "<br>" & _
Chr(10) & "Have a great week!"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
End Sub
Can someone please assist?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cells As Range
Set cells = ActiveSheet.Range("D19:D500")
If Not (Intersect(Target, cells) Is Nothing) Then
ActiveSheet.Range("M3").Value = Target.Value
End If
End Sub
I want to create an email and include the the value that is shown using the value that is the result from above however when I use the below I am getting an error because the value dissapears from Cell M3.
Private Sub CommandButton2_Click()
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set rng = Selection.SpecialCells(xlCellTypeVisible)
On Error Resume Next
With OutMail
.To = "enterprise@xxxx.com"
.Cc = "kristin@xxxxx.com; proltl@xxxxx.com"
.Subject = "Walmart Order(s): MISSING/DELAYED"
.HTMLBody = "Hi Team, " & Chr(10) & "<br>" & "<br>" & _
Chr(10) & "Please check on PRO " & Range("M3").Value & "<br>" & "Delivery Scheduled: " & Range("N9").Value & "<br>" & "<br>" & _
Chr(10) & "Delivery of this order appears to be delayed. Please provide an update at your earliest convenience" & "<br>" & "<br>" & _
Chr(10) & "Have a great week!"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
End Sub
Can someone please assist?