USING DYNAMIC DATA IN EMAIL (VBA)

[O177812]

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?

 

[StephenCrump]

when I use the below I am getting an error because the value dissapears from Cell M3.

What do you mean by "error"? Are you receiving an error message? Or are you saying the code is not working the way you expect?

And what do you mean by disappears? Sub CommandButton2_Click() has an unqualified reference to Range("M3"), which will refer to M3 in the ActiveSheet. Is this the same sheet that contains your Sub Worksheet_SelectionChange that wrote to M3?

 

[O177812]

What do you mean by "error"? Are you receiving an error message? Or are you saying the code is not working the way you expect?

And what do you mean by disappears? Sub CommandButton2_Click() has an unqualified reference to Range("M3"), which will refer to M3 in the ActiveSheet. Is this the same sheet that contains your Sub Worksheet_SelectionChange that wrote to M3?

Yes, it is on the same sheet.
No I am not receiving an actual error message.

When I run the second macro the value from the first disappears before it can be copied and transferred to the bottom of the email.

 

[StephenCrump]

When I run the second macro the value from the first disappears ...

Are you sure the value in M3 isn't being cleared before Sub CommandButton2_Click runs? If M3 contains some value, there is nothing in the Sub CommandButton2_Click code you've posted that would clear that value. Nor should it trigger any other event code that might be clearing the value.

Do you really need to be setting M3 using a Worksheet_SelectionChange event? I suspect this is the problem.

And do you even need to be storing in M3 at all. Instead, could you just have Sub CommandButton2_Click email the selected cell (or cells - do you want to be sending 10 emails if there are 10 orders delayed?) in D19:D500?

 

[O177812]

Are you sure the value in M3 isn't being cleared before Sub CommandButton2_Click runs? If M3 contains some value, there is nothing in the Sub CommandButton2_Click code you've posted that would clear that value. Nor should it trigger any other event code that might be clearing the value.

Do you really need to be setting M3 using a Worksheet_SelectionChange event? I suspect this is the problem.

And do you even need to be storing in M3 at all. Instead, could you just have Sub CommandButton2_Click email the selected cell (or cells - do you want to be sending 10 emails if there are 10 orders delayed?) in D19:D500?

Basically what I am trying to accomplish is to be able to select one of the cells in column D and then add whatever value is in the selected cell to the body of an email.
I don't see anything in my current code that would cause it to clear. Everything is working and building correctly except the value is not appearing in the email body.

 

[StephenCrump]

If I run your Sub CommandButton2_Click, it correctly populates an email with the values of cells M3 and N9 in the email body (i.e. M3 and N9 in the ActiveSheet).

Similarly, if I replace Range("M3").Value with Selection.Value (a single cell), it correctly populates the email body with the value of that cell.

What happens if you get rid of your Sub Worksheet_SelectionChange, and run just Sub CommandButton2_Click?

By the way, it's bad practice to wrap code in:

On Error Resume Next
'code
On Error GoTo 0

unless you know there is a reason a particular piece of code might error, and your code tests and allows for that error occurring, e.g. a .Find or a .Match that produces no results.