Copying Mulitple Ranges, Pasting to body of Email

[Master Blaster]

Time Log2.4.xlsmI currently have adapted rondebruin's "Mail Range or Selection in the body of the mail" automatic Outlook Macro.
I have two things that I am trying to achieve.

1. I need the automatic email to be initiated by typing data into a set of predesignated cells (Odd numbered cells residing in column "AE"). As you can see I have it being initiated when any cell in column is altered.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 31 And Target.Count = 1 Then
Application.Run "Mail_outlook"
End If
End Sub

2. I need the body of the email to include two ranges of cell data.
a- The "Lot" data corresponding to the row of the cell that was altered in column AE.
b- "B6:I10" (which is the heading for that data)

Sub Mail_outlook()
    
    Dim OutApp As Object
    Dim OutMail As Object
    
    
 ActiveCell.Offset(-1, -29).Select
Application.Union(Selection.Resize(Selection.Rows.Count, _
   Selection.Columns.Count + 7), Range("B6:I10")).Select
Selection.Copy
  
    

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = "email here"
        .CC = ""
        .BCC = ""
        .Subject = "Lot Complete and Ready for Final Pack"
        .Display
        Application.Wait Now + TimeValue("00:00:02")
        SendKeys "^({v})", True


.send
        
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

So when the AE column is initialed it will pull the data from column B to column I corresponding to that active cell.
Esentially looking like this on the email.
Capture.PNG

The problem I am encountering is when it pastes the date into the body it includes all the cells between the two selections.
Is this even the right approach to take?

Thanks in advance

[Master Blaster]

Have made some progress on the original post and have edited accordingly but still can not achieve a final product. Any help would be appreciated.

[Master Blaster]

Came across this article http://www.think-cell.com/en/support/kb/0160.shtml

When multi-selecting several non-adjacent cells, e.g., by pressing the control key and subsequently clicking on cells, and then copy/pasting these cells into think-cell's internal data sheet, unselected cells in between are pasted as well.

Reproduction without think-cell:

1. Open Excel and enter some numbers into cells A1 to A5.
2. Select the cells A1, A3, and A5 by holding the control key (Ctrl) pressed and clicking into these cells (as in above screenshot). Copy the selection.
3. Start a separate Excel process by right-clicking on the Excel symbol in the task bar and clicking on Microsoft Excel 20....
Paste (e.g., by pressing Ctrl+V).

Result: The unselected cells A2 and A4 are pasted as well.

We have filed this issue with the Microsoft Office Support as case REG 112021442496011. The Microsoft Office Support says that this behavior is not documented so far, but is “by design”.

“This behavior is by design.

When you use the clipboard for copy and paste, you are using a feature that is designed to work across all office applications. Since this is the case, the clipboard uses HTML code for storing and then pasting data. The HTML code is limited to a range select and you get all the data in the range including the cells you did not select. It is creating a table in HTML code and uses this table for the paste action. This allows the clipboard to share data from Excel to other office applications such as Word, Power Point, and other instances of Excel outside this workbook.

When you use Ctrl-C and then follow it up with Ctrl-V instead of using the clipboard you are using an internal Excel single instance copy feature that allows Excel to copy only the selected cells. This gives you the desired behavior. The clipboard has limits due to the fact that it shares with all office applications. The limit to the clip board was planned due to the huge amount of data that would be required to do a simple copy and paste. Large copy actions would overwhelm the system and cause huge slowdowns in performance. The HTML copy is a stripped down version of the internal copy and therefore there are differences and limits.” (Microsoft Office Support)