Summary:

This article shows how to get the address fields in an opportunity email template to show information on the address entity

More information:

Naviagate to Administration -> Customisation -> Opportunity -> Views -> vSummaryOpportunity.

This is the original view:

CREATE VIEW vSummaryOpportunity AS SELECT epd_pers.epd_EmailAddress as Pers_EmailAddress, epd_pers.epd_PhoneCountryCode as Pers_PhoneCountryCode, epd_pers.epd_PhoneAreaCode as Pers_PhoneAreaCode, epd_pers.epd_PhoneNumber as Pers_PhoneNumber, epd_pers.epd_FaxCountryCode as Pers_FaxCountryCode, epd_pers.epd_FaxAreaCode as Pers_FaxAreaCode, epd_pers.epd_FaxNumber as Pers_FaxNumber, epd_comp.epd_EmailAddress as Comp_EmailAddress, epd_comp.epd_PhoneCountryCode as Comp_PhoneCountryCode, epd_comp.epd_PhoneAreaCode as Comp_PhoneAreaCode, epd_comp.epd_PhoneNumber as Comp_PhoneNumber, epd_comp.epd_FaxCountryCode as Comp_FaxCountryCode, epd_comp.epd_FaxAreaCode as Comp_FaxAreaCode, epd_comp.epd_FaxNumber as Comp_FaxNumber, Person.*, Company.*, Opportunity.*, Account.* FROM Opportunity LEFT JOIN Person ON Oppo_PrimaryPersonId = Pers_PersonId LEFT JOIN CRMEmailPhoneData epd_pers ON epd_pers.epd_EntityID = 13 AND epd_pers.epd_RecordID = Pers_PersonID LEFT JOIN Company ON  Oppo_PrimaryCompanyId  = Comp_CompanyId  AND Comp_Deleted IS NULL LEFT JOIN CRMEmailPhoneData epd_comp ON epd_comp.epd_EntityID = 5 AND epd_comp.epd_RecordID = Comp_CompanyID LEFT JOIN Account ON Oppo_PrimaryAccountId = Acc_AccountId WHERE Oppo_Deleted IS NULL

To get the address fields to contain address information in an email that has address fields in a opportunity template, the user must add the lines highlighted in yellow to the vSummaryOpportunity view. (see below)

CREATE VIEW vSummaryOpportunity AS SELECT epd_pers.epd_EmailAddress as Pers_EmailAddress, epd_pers.epd_PhoneCountryCode as Pers_PhoneCountryCode, epd_pers.epd_PhoneAreaCode as Pers_PhoneAreaCode, epd_pers.epd_PhoneNumber as Pers_PhoneNumber, epd_pers.epd_FaxCountryCode as Pers_FaxCountryCode, epd_pers.epd_FaxAreaCode as Pers_FaxAreaCode, epd_pers.epd_FaxNumber as Pers_FaxNumber, epd_comp.epd_EmailAddress as Comp_EmailAddress, epd_comp.epd_PhoneCountryCode as Comp_PhoneCountryCode, epd_comp.epd_PhoneAreaCode as Comp_PhoneAreaCode, epd_comp.epd_PhoneNumber as Comp_PhoneNumber, epd_comp.epd_FaxCountryCode as Comp_FaxCountryCode, epd_comp.epd_FaxAreaCode as Comp_FaxAreaCode, epd_comp.epd_FaxNumber as Comp_FaxNumber, Person.*, Company.*, Opportunity.*, Account.*,Address.* FROM Opportunity LEFT JOIN Person ON Oppo_PrimaryPersonId = Pers_PersonId LEFT JOIN CRMEmailPhoneData epd_pers ON epd_pers.epd_EntityID = 13 AND epd_pers.epd_RecordID = Pers_PersonID LEFT JOIN Company ON  Oppo_PrimaryCompanyId  = Comp_CompanyId  AND Comp_Deleted IS NULL LEFT JOIN CRMEmailPhoneData epd_comp ON epd_comp.epd_EntityID = 5 AND epd_comp.epd_RecordID = Comp_CompanyID LEFT JOIN Account ON Oppo_PrimaryAccountId = Acc_AccountId LEFT JOIN Address ON Comp_PrimaryAddressId = Addr_AddressId WHERE Oppo_Deleted IS NULL

If the opportunity email template contains an address field eg #addr_address1# the address information should get passed into the email now.