using custom link to VF page in an email template

Question

I have an HTML email template (NOT a VF template) that should be sent by a workflow rule when the record status is changed to Converted.

rather than a link to the record in SalesForce, I want a link on the email template to point to the record shown in my VF Page, so the link would be something like

https://domain.salesforce.com/apex/SystemChangeRequest?id=a3Qg0000000HaEdEAK&mode=view

I looked at Bob Buzzard's answer here and tried adding:

Click {HYPERLINK(Left($Api.Enterprise_Server_URL_190,(find('/services',$Api.Enterprise_Server_URL_190))) + Request_for_System_Change__c.Id, 'Request' )} 

I also put a special URL field in my custom object, which can be populated by the trigger on save. This new field is called link_to_request_)c and is populated in the Save event of my trigger as below:

String sUrlRewrite = System.URL.getSalesforceBaseUrl().getHost();

system.debug('sUrlRewrite  = ' + sUrlRewrite);
//  Example: c.cs7.visual.force.com
// https://cs87.salesforce.com/apex/SystemChangeRequest?id=a3K8E0000000CMyUAM&mode=view
sUrlRewrite = 'https://'
    + sUrlRewrite.substring(0,5) 
    + 'salesforce.com'
    + '/apex/SystemChangeRequest?mode=view&id='
    + sId;

r.link_to_request__c= sUrlRewrite;      

so in my email template I tried adding that as well, but when I click on the add link button in editing HTML template, it just shows me the add HTML link dialog:

and when the email is sent, it looks like this:

I am stuck with this, I could of course hard-code the domain name in each domain, but I'd rather try and have it done dynamically if possible.

UPDATE following answer by bennie

so I have a formula field in my custom object called UrlFormulaField using the formula LEFT($Api.Partner_Server_URL_360, FIND( '/services', $Api.Partner_Server_URL_360)) & '/apex/systemchangerequest?id=' & Id & '&mode=view' and this creates the correct URL as

https://domain.salesforce.com//apex/systemchangerequest?id=a3Qg0000000HaMImode=view

so in my HTML template I want to add this as a Merge field link, but when I click add the Merge field, select it, click the add link button and paste in the merge field it doesn't add it as a link:

so what is the correct way to add the merge field as a link?

Answer 1

First create a formula field Instance_URL__c on your object -

LEFT($Api.Partner_Server_URL_360, FIND( '/services', $Api.Partner_Server_URL_360))

Now in your template use it as a merge field. I have taken Account object as an example.

{!Account.Instance_URL__c}{!Account.Id} 

In your instance something like

Click {!Request_for_System_Change__c.UrlFormulaField__c}{!Request_for_System_Change__c.Id} to go to change request

Click on link and insert the formula string like this