# Mailto hyperlink in VBA including cell value



## hash993 (Dec 19, 2022)

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 (Dec 19, 2022)

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.xlsmA1os6hdCvZzyCcEegGfrmR3mvW3WWVCVqg99CFI17mbIFppwe0MYCRZF0HIoqSlmFpLQwRModfjTy8Lmh6ah1Uzs8JiotJCBHtZsnk8nO4n6dHDSJvt3gOaKyuux0MyLNQ85Hg7w2nBK77b3rIhBmbGSumbsj4rxgPd1mB6yz53AtMHo6lfnFcSzjTB4v7W6lateNazbl8jwB5P3KyUuhDiqikAqOJhXqdNrS6jsoQKyEOlf6ORQ2H4jZvLYTYZK8l0bVJlHvd9EVFmdPnmqLF1seqqOjqCVjdkJULdCsMCgdFWp6WAHszmmJsWwgBCrt9NjnFZEy1tkXnvj4SNc7HXCZwL5f0StsZU2VRrrIbVifEbyikVuuEiKIBGr34Pm2rLeF0lcNhSugiToQGBrlYu4TyIfS5MkiQzJnv3yLBgBuBP6HBX2EBvaLfXWSM8Dzs1mWPtDegSBBJmY1rQZiSbrvkdZIVrcuJ2nVGvjwrk2juXkGr5l4l3RH8BaV09osQ2512 charsSheet1Cell FormulasRangeFormulaA2A2= 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.
> 
> ...


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 (Dec 19, 2022)

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:
Book1A1someone@nowhere.com2The Subject is NULL3The Body is everything!4mailto:someone@nowhere.com?&subject=The Subject is NULL&body=The Body is everything!5Mail Me!Sheet1Cell FormulasRangeFormulaA4A4="mailto:"&A1&"?&subject="&A2&"&body="&A3A5A5=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 (Dec 21, 2022)

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
```


----------

