Mailto hyperlink in VBA including cell value

[hash993]

Hello

since excel have a limit of 255 characters i was forced to use VBA to go around that issue.

am trying to create Mailto hyperlink in column ( E ) for each row that has data in and use cell values of the same row " am clueless in VBA"

i want to be able to insert the name and position in the subject and body of the email.

 

[PeteWright]

Excel can handle more then 255 characters in a cell, but most of the built-in functions don't.

Here is an example of a 512 character long text string in cell A1 and a workaround function to count the characters in cell A1:

Book1.xlsm
	A
1	os6hdCvZzyCcEegGfrmR3mvW3WWVCVqg99CFI17mbIFppwe0MYCRZF0HIoqSlmFpLQwRModfjTy8Lmh6ah1Uzs8JiotJCBHtZsnk8nO4n6dHDSJvt3gOaKyuux0MyLNQ85Hg7w2nBK77b3rIhBmbGSumbsj4rxgPd1mB6yz53AtMHo6lfnFcSzjTB4v7W6lateNazbl8jwB5P3KyUuhDiqikAqOJhXqdNrS6jsoQKyEOlf6ORQ2H4jZvLYTYZK8l0bVJlHvd9EVFmdPnmqLF1seqqOjqCVjdkJULdCsMCgdFWp6WAHszmmJsWwgBCrt9NjnFZEy1tkXnvj4SNc7HXCZwL5f0StsZU2VRrrIbVifEbyikVuuEiKIBGr34Pm2rLeF0lcNhSugiToQGBrlYu4TyIfS5MkiQzJnv3yLBgBuBP6HBX2EBvaLfXWSM8Dzs1mWPtDegSBBJmY1rQZiSbrvkdZIVrcuJ2nVGvjwrk2juXkGr5l4l3RH8BaV09osQ
2	512 chars
Sheet1

Cell Formulas
Range	Formula
A2	A2	= FIND("~", REPLACE(RIGHT(A1,1),1,1,A1 & "~")) - 1 & " chars"

Now the hyperlink is a bit tricky since the text of the body has to be URL-encoded.

Take a look at this simple email body:

Hello John!

This is only a sample email body text.

Best regards

Jim

Encoded as URL it would look like that:

Hello%20John!%0A%0AThis%20is%20only%20a%20sample%20email%20body%20text.%0A%0ABest%20regards%0A%0AJim

I am not sure if you really want to go through this.

Nevertheless let me know if I could help and what alternate method you are willing to use instead of Excel.
Maybe you could sum up what you are trying to achieve and I will find a way or tool (software) that can get the job done.

 

[jdellasala]

You can't get this to send the email, but it will open your default email app with the new email created and ready to send:

Book1
	A
1	someone@nowhere.com
2	The Subject is NULL
3	The Body is everything!
4	mailto:someone@nowhere.com?&subject=The Subject is NULL&body=The Body is everything!
5	Mail Me!
Sheet1

Cell Formulas
Range	Formula
A4	A4	="mailto:"&A1&"?&subject="&A2&"&body="&A3
A5	A5	=HYPERLINK(A4,"Mail Me!")

Clicking Mail Me! in the workbook will create that email in your default email app.

My default mail app is Windows 11 Mail, although I don't use it.

 

[hash993]

Thanks guys for your help but i have already figured out the solution in VBA.
below is my code for anyone who needs it in future.

Sub SendHiringRequirmentEmail()


Dim theName As String
Dim thePosition As String
Dim theEmail As String
Dim theProject As String
Dim lastrow As String


lastrow = Cells(Rows.Count, 1).End(xlDown).Row

For i = 2 To lastrow
If Range("F" & i).Value <> "" Then

theName = Range("D" & i).Value
thePosition = Range("E" & i).Value
theEmail = Range("F" & i).Value
theProject = Range("G" & i).Value


msgLink = "mailto:" & theEmail & "?subject=" & "Hiring requirement - " & theName & " - " & theProject & " - " & thePosition & " - OFFSHORE" & "&cc=" & "Email@email.com" & "&" & "body=" & "the body of email goes here."

ActiveSheet.Hyperlinks.Add Range("P" & i), Address:=(msgLink), TextToDisplay:="Send"


End If


Next

End Sub

 

[duteberta]

I would love to see your actual worksheet to see how you implemented this. I am brand new to VBA so trying to just logically match what you did to my own sheet.