Send multiple columns/rows by e-mail

[MasterBash]

Hello,
Adding an image and the code

Inside of module :

Sub AddHyperlink()
    Dim rng As Range, cel As Range
    
    With Sheets("Sheet1")
        Set rng = .Range("G1")
        For Each cel In rng
            .Hyperlinks.Add anchor:=cel, Address:="", SubAddress:=cel.Address(0, 0), TextToDisplay:=cel.Text
        Next cel
    End With
End Sub

Inside of the worksheet :

Option Explicit
    Dim subject As String
    Dim body As String

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Range(Target.SubAddress).Column = 7 Then
    subject = Date
    body = Range("G2")
    
    Call Send_The_Emails
End If
End Sub

Sub Send_The_Emails()


Dim OApp As Object, OMail As Object, signature As String

Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)

With OMail
    .Display
End With

signature = OMail.body

With OMail
    .To = Range("P1").Value
    .subject = "Here is the report for " & subject
    .body = "Hello, " & vbNewLine & vbNewLine & _
            "" & body & "" _
            & vbNewLine & vbNewLine & signature
    .Display
    '.Send
End With

Set OMail = Nothing
Set OApp = Nothing

End Sub

When I click Send e-mail, I would like to send the data from G2 to K5, but I get an error everytime I try to add the entire range inside of body = Range("G2")
The best case scenario, it would not add to the e-mail column K, because all the values are at 0. However, if K would have values > 0, then add that column to the e-mail (This should actually apply to all columns H and K, not just K).

How can I do that ?

Thank you !

 

[MasterBash]

Also, on the sheet, I would like 2 different links, so I can choose different e-mail address, subject and body text for each of them.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Range(Target.SubAddress).Column = 7 Then
    subject = Date
    body = Range("G2")
    
    Call Send_The_Emails
End If
End Sub

I believe If Range(Target.SubAddress).Column = 7 Then needs to be changed to something else, if I want a specific cell (In this case, it is G1, but there will be more in the future) to have its own e-mail address, subject and body.

Thank you !

 

[NoSparks]

Perhaps this response by John_w to a somewhat similar question would be of interest.

Regarding the second question, the way the subaddress is set for the hyperlinks it is the cell containing the hyperlink so you could use something like

    If Target.SubAddress = "G1" Then
        'whatever you want
    End If
    
    If Target.SubAddress = "G8" Then
        'whatever you want
    End If

    'etc

 

[MasterBash]

Thank you !
It improved a bit. Your VBA code works.

However, I can't get john w's code to work. I get compile: error User-defined type not defined for this part :

    Dim OutApp As Outlook.Application
    Dim OutEmail As Outlook.MailItem
    Dim WordDoc As Word.Document

 

[NoSparks]

did you add the references he indicates at the very top of what he posted?

In the vba environment menu at the top
Tools -> references

 

[MasterBash]

I apologize for my ignorance, but how can I do that ?

Also, will it require my co-workers to do any extra steps on their side ?

 

[MasterBash]

Also posted this question on another forum, forum rule #13 (cross-posting) :
Send multiple columns and rows by e-mail vba

How to add references :

From VBA Editor toolbar:

Tools > References > scroll down until you find Microsoft Outlook x.x Object Library and check it. Scroll down some more until you see Microsoft Word x.x Object Library and check it. Click OK.

From June7 in the link above.