bypassing hyperlink() maximum characters (many links on sheet)

[MasterBash]

Hello,

USA	DOG	Send e-mail to DOG	E-mail	List with too many e-mail address
CANADA	CAT	Send e-mail to CAT	Subject :	REQUEST… TO $B1 to $B4 depending on the link I click
Mexico	PIG	Send e-mail to PIG	Body :	Hello, Please provide… to $B1 to $B4 depending on the link I click
Germany	ELEPHANT	Send e-mail to ELEPHANT	Signature :	If that is possible to add a signature, possibly the one added to outlook ?

I am looking to do something like this. Column has the name of different animals (Sorry, the real worksheet has confidential data). Column C contains an hyperlink that I can click on =hyperlink(mailto:...) Send e-mail to $B1, etc.
Now, the list of e-mails is way too long, so I get a value error when using hyperlink().

What is the best way to get around this ? I do not believe it is possible to add a body text and a signature (optional) when using the Insert -> Link button.

Help would be very much appreciated.

 

[NoSparks]

IF I understand what you're after I'd use a macro to compose and send the emails.
Based on this

Book1
	A	B	C	D	E
1	USA	DOG	Send e-mail to DOG	E-mail	john@somewhere.com, paul@somewhere.com, george@somewhere.com, ringo@somewhere.com
2	CANADA	CAT	Send e-mail to CAT	Subject :	REQUEST… TO $B1 to $B4 depending on the link I click
3	Mexico	PIG	Send e-mail to PIG	Body :	Hello, Please provide… to $B1 to $B4 depending on the link I click
4	Germany	ELEPHANT	Send e-mail to ELEPHANT	Signature :	If that is possible to add a signature, possibly the one added to outlook ?
Sheet1

By using this to set up the hyperlinks in column C

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

End Sub

you could use the Worksheet_Followhyperlink event to get the info you want
and call a macro that uses it, with something along the lines of
this code in the sheet module.
Note: I don't use Outlook so it's not fully tested

Option Explicit
    Dim animal As String

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Range(Target.SubAddress).Column = 3 Then
    animal = Range("B" & Range(Target.SubAddress).Row).Value
    Call Send_The_Emails
End If
End Sub

Sub Send_The_Emails()
' adapted from
' https://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook

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("E1").Value
    .Subject = "REQUEST TO... " & animal & "  ( you can add to subject line here )"
    .body = "Hello" & vbNewLine & vbNewLine & _
            "Please provide… to " & animal & "  ( and you can put more text here )" _
            & vbNewLine & vbNewLine & signature
    .Display
    '.Send
End With

Set OMail = Nothing
Set OApp = Nothing

End Sub

Hope this is of some use,
let me know if it works for you.

 

[MasterBash]

Thank you !

This is exactly what I am trying to do, but unfortunately it is not working for me, as column C are not changing into hyperlinks.
I am guessing that I have to remove the formulas from those cells, correct ? Only keep the text "Send e-mail to dog" as an example ?

I changed it from Sheet1 to Sheet7, because that is what I am using.

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

End Sub

Where do you put that code ? I believe I am doing something incorrect.

 

[NoSparks]

I am guessing that I have to remove the formulas from those cells, correct ?

I wouldn't think so providing that the result of the formula is "Send e-mail to dog"
but I'd remove the original hyperlinks and change TextToDisplay to
TextToDisplay:=cel.value

What is the formula you have in these cells?

Where do you put that code ?

Put that code into a standard module.
You should only need to run this macro once.
If you want it to show up in the Alt-F8 macro dialogue, remove the word Private

 

[MasterBash]

What is the formula you have in these cells?

=HYPERLINK("mailto:"&E$1&"?Subject="&$E$2&" "&B1&"&body=Hello, %0d%0a%0d%0a"&$E$3&" "&$B1;"Send e-mail to "&B1&"")

This is what I currently have in those cells. So the Send e-mail to... is actually part of the hyperlink.

 

[MasterBash]

I actually got it to work. Sheet7 wasn't valid, I actually had to use the sheet's name, I couldn't just use the sheet's number.

Thank you. I will report back after some tests.

 

[MasterBash]

Thank you ! It works very well. One thing I would like to change... Using an html e-mail. I am guessing I have to change .body to .htmlbody, correct ?
Everything works great. Team tested it out yesterday and today, they really like it. Thank you so much.

 

[NoSparks]

Glad that works for you.
Regarding the html... that sounds logical but I really don't know. I suspect there would be a little more than just that required.
If you run into issues with that start a new thread dealing with that part of things and I'm sure somebody here will help you achieve whatever you're needing.

Good luck with the project.