VBA FollowHyperlink Generating Email Template Without Subject/Body Character Limit

[whereswaller]

Hi,
I am trying to insert a button into my Excel spreadsheet that pulls the contents from neighbouring cells and inserts them into an email (which would become a template). The below code works in most instances, however when the characters in the mailto string exceed ~900 characters I receive the following error, "Run-time error '5': Invalid procedure call or argument". How do I get around this?

Sub emailer()


Dim r As String, i As String, e As String, x As Long, y As Long, s As String, b As String, mailto As String


r = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row


i = Range("H" & r)
x = InStr(i, "(")
y = InStrRev(i, ")")
e = Mid(i, x + 1, y - x - 1)


s = Range("D" & r)


b = Replace(Range("E" & r), "]", "]%0D%0A")
If ActiveWorkbook.Worksheets("Task List").Range("C6") <> Empty Then
    b = Replace(b, "RM/AE Name: [insert]", "RM/AE Name: " & ActiveWorkbook.Worksheets("Task List").Range("C6"))
End If
If ActiveWorkbook.Worksheets("Task List").Range("C7") <> Empty Then
    b = Replace(b, "State: [insert]", "State: " & ActiveWorkbook.Worksheets("Task List").Range("C7"))
End If
If ActiveWorkbook.Worksheets("Task List").Range("C8") <> Empty Then
    b = Replace(b, "Tier: [insert]", "Tier: " & ActiveWorkbook.Worksheets("Task List").Range("C8"))
End If
b = Replace(b, "I accept", "%0D%0AI accept")


mailto = "mailto:" & e & "?subject=" & s & "&body=" & b


ActiveWorkbook.FollowHyperlink mailto


End Sub

Many thanks in advance,
James

 

[Jerry Sullivan]

Hi James and Welcome to the Board,

Consider using one of Ron de Bruin's email VBA code templates on this site.
Mail from Excel and make/mail PDF files (Windows)

He has example templates using the most common mail programs.
This should give you more control than the use of the FollowHyperlink method.

 

[whereswaller]

Hi Jerry,
Thanks for your suggestion. I have already been on Ron de Bruin's website and, unfortunately, the functionality he demonstrates is not what I'm looking for.

Essentially, all I would like to do is launch the "create e-mail" prompt on the user's default "mailto:" program and populate the To, Subject and Body fields with some pre-defined text. I do not want the e-mail to send and I want the populating text to be unlimited in length.

My existing code does all of the above except allow me to populate text to an unlimited length.

Thoughts?
James