Inserting a mailto link into an email body to a customer, based on a cell value.

[Royalbloodi]

I really don't know how to state this one better.

I'm creating a spreadsheet that will take a customer's email address and Generate an email to inform them who the contacts for their selected job area are. I designed a sample spreadsheet:

And my goal is to produce the email so it has a link in it (at the HERE) that will automatically open a new email with their work contacts in the To: and automatically populate the subject with the Job area.

****This is what I can't figure out****
How/what do I put in VBA or Formulas to make this Mailto link in the customer's email above, that would populate the email below?

In this case, the email created by the mailto link would essentially look like:

The VBA for the "Generate Email Button" is:

Private Sub CommandButton1_Click()

'------------------------------------------------------------------|| GENERATE PRE-ASSIGNED EMAIL ||------------------------------------------------------------------

Dim OutObj As Object, OutMail As Object
Set OutObj = CreateObject("Outlook.Application")
Set OutMail = OutObj.createitem(0)

With OutMail
.Display
End With

Sign = OutMail.HTMLbody '-------------------------------------------------------------------------------|| Define the user's HTML signature as Sign.

With OutMail
    .to = Range("B13").Value
    .Subject = "Company Name - Order Updates and Contact Information - " & Range("B14") & ", " & Range("B15")
    .HTMLbody = _
        "<p style=font-size:20px>" & "Dear " & Range("A13") & ",<br><br>" & "Your Order Form for " & Range("B14") & " has been submitted to the proper department and they will process your information and contact you with further updates." & _
        " If you need to reach out with any questions or concerns, you can reach them by clicking HERE" & "<br><br>Sincerely," & _
        vbNewLine & Sign '-------------------------------------------------------------------------------|| Add the user's HTML signature to the end of the email.
        
        
End With
End Sub

And the Array and Cell Formulas are as follows:

F3 FORMULA:

=UNIQUE(FILTER(A2:A10,A2:A10<>""))

G3 FORMULA:

=FILTER(B2:B10,C2:C10=D13)

H3 FORMULA:

=FILTER(A2:A10,C2:C10=D13)

D13 FORMULA:

=TEXTJOIN(", ",,H3#)

Help please. I'm going insane.

Thanks!

 

[Domenic]

Here's the syntax for an html mailto link...

"<a href='mailto:example@gmail.com?subject=your subject here&body=your body here'>HERE</a>"

So, for example, maybe something like this...

.HTMLbody = _
        "<p style=font-size:20px>" & "Dear " & Range("A13").Value & ",<br><br>" & "Your Order Form for " & Range("B14").Value & " has been submitted to the proper department and they will process your information and contact you with further updates." & _
        " If you need to reach out with any questions or concerns, you can reach them by clicking <a href='mailto:" & Range("A2").Value & "?subject=" & Range("B2").Value & "&body=" & Range("C2").Value & "'>HERE</a>." & "<br><br>Sincerely," & _
        vbNewLine & Sign '-------------------------------------------------------------------------------|| Add the user's HTML signature to the end of the email.

...where A2, B2, and C2 contain the To, Subject, and Body. Change these references accordingly.

Hope this helps!

 

[Royalbloodi]

Here's the syntax for an html mailto link...

"<a href='mailto:example@gmail.com?subject=your subject here&body=your body here'>HERE</a>"

So, for example, maybe something like this...

.HTMLbody = _
        "<p style=font-size:20px>" & "Dear " & Range("A13").Value & ",<br><br>" & "Your Order Form for " & Range("B14").Value & " has been submitted to the proper department and they will process your information and contact you with further updates." & _
        " If you need to reach out with any questions or concerns, you can reach them by clicking <a href='mailto:" & Range("A2").Value & "?subject=" & Range("B2").Value & "&body=" & Range("C2").Value & "'>HERE</a>." & "<br><br>Sincerely," & _
        vbNewLine & Sign '-------------------------------------------------------------------------------|| Add the user's HTML signature to the end of the email.

...where A2, B2, and C2 contain the To, Subject, and Body. Change these references accordingly.

Hope this helps!

This helps immensely. Now I've found a new issue. Since I now need to use a cell for my email body, how do I insert line breaks so my whole email body isn't one line? I could use multiple cells I guess (i.e.

[QUOTE]
<a href='mailto:" & Range("A2").Value & "?subject=" & Range("B2").Value & "&body=" & Range("C2").Value & "<br><br>" & Range("C3").Value & "'>HERE</a>."
[/QUOTE]

but I was hoping for something using just a single cell. The Text of the body will have to change based on the type of job it is, so I can't put it in VBA without adding a bit of code to differentiate multiple subjects. Hopefully I've explained that well enough...

 

[Domenic]

For the body in a mailto link, you'll need to percent encode the new line character. So, for example, let's say that C2 contains the text for the body, the following will replace each instance of vbLf with "%0d%0a", and assign the result to the variable mailToLinkBody...

    Dim mailToLinkBody As String
    mailToLinkBody = Replace(Range("C2").Value, vbLf, "%0d%0a")

Note that the hexadecimal value 0d is equivalent to the decimal value 13 or vbCr (carriage return), and the hexadecimal value 0a is equivalent to the decimal value 10 or vbLf (line feed). Then the mailto link would be as follows...

<a href='mailto:" & Range("A2").Value & "?subject=" & Range("B2").Value & "&body=" & mailToLinkBody & "'>HERE</a>."

Hope this helps!