Excel VBA to send email. Message cut off

[Verstegen99]

Good afternoon,

For my work I have to send out multiple simmilar emails with a few minor changes per email towards cliënts.

I am making (or trying) to make tool that prepares the emails for me as a whole (receiver, header, body).
I would only have to click a hyperlink, check if everything is correct and send it.

I managed to create a hyperlink containing 1336 characters. But when I add a relatively basic additional cell reference it won't work anymore (no link is generated).
I think it is because there are too many characters in the email (extending the name in the greeting also "breaks" the link).

Does anyone know of a good alternative?

My current code is;

Private Sub Worksheet_Change(ByVal Target As Range)
Dim h As Hyperlink, r As Range, c As Range, n As Long

Set r = Intersect(Target, Range("B2:E" & Cells(Rows.Count, "B").End(xlUp).Row))
If r Is Nothing Then Exit Sub

Application.EnableEvents = False

On Error Resume Next
For Each c In r.Rows
n = c.Row
Set r = Cells(n, "G")
With r
.Hyperlinks(1).Delete
'"mailto:"&B&"?Subject="&N&"&Body="&O&C&P&Q
.Hyperlinks.Add r, "mailto:" & Cells(n, "B") & "?Subject=" & Cells(n, "N") & "&Body=" & _
Cells(n, "O") & Cells(n, "C") & Cells(n, "P") & Cells(n, "Q"), , , "Click Here"
End With
Next c

Application.EnableEvents = True
End Sub

Thanks in advance.

 

[Micron]

I've never used that method - usually used Automation (it may have a different name now) because it's much more powerful. Basically, you interact with outlook to create/send email from Access, Excel and the like. If you research the method you'll find a gazillion code examples and one or more of them will probably be useful to you. If you're not using Outlook then that is a different matter.

EDIT forgot to say please enclose code in code tags (vba button on posting toolbar) to maintain indentation and readability.