RetrieveSalesforceObjects from rich text field contains text pasted from Word bad characters like diamond with question mark � replace with ampscript

Question

I'm having an issue with an email that is pulling the HTML from a rich text field inside of Sales Cloud. We are using RetrieveSalesforceObjects to do this and in Outlook, some spaces are showing up as a diamond with a question mark �. Additionally, some apostrophes are doing it as well. When you inspect those apostrophes you can clearly see that they are different. Preview and Test shows things just fine, the issue is only seen in outlook. I can't even see the space characters in VS Code. The only way I knew the diamond with question mark character was in the spaces is because I did an export using dataloader. The apostrophes I can see at least, but using the REPLACE function works in Preview and Test, but the email still comes through wrong in Outlook.

Additionally, the field is truncated in the Synced DE, so pulling from there is not an option. I know that we can clean up the fields, but seeing as we have non-technical users pasting this info, it's going to keep happening. I'm hoping there is a way to intercept it and fix it.

This is what I've tried where @inquiryTextField is the data from the CRM rich text field (I've omitted the Retrieve call so as to make this more legible).

set @pattern = '/[\x00-\x1F\x7F-\xFF]/'
set @match = RegExMatch(@inquiryTextField,@pattern,0,"IgnoreCase")
set @try1 = REPLACE(REPLACE(@inquiryTextField,"’","---"),"�","SPACE")
set @try2 = REPLACE(@inquiryTextField,@match,"----------------")

In @try1, I just literally pasted the characters into the code editor as you can see. The replace works in the preview and test window, but when the email gets to outlook, nothing is changed.

In @try2, I attempted a regex match, but that also is not working. I suspect this is my best option, but I have no idea what the character codes would be for those characters.

I've tried messing with the doctype and the content type meta tags, but to no avail.

Has anyone experienced this before?

Answer 1

Found it, this solved the issue

/*Remove weird curly apostrophes*/
set @pattern = '[\u2018-\u2019]'
set @match = RegExMatch(@inquiryTextField,@pattern,0,"IgnoreCase")
set @pinquiryemail = REPLACE(@inquiryTextField,@match,"'")

/*Make a second pass and replace weird diamond with question mark with regular space*/
set @pattern = '[^\x00-\x7F]'
set @match = RegExMatch(@inquiryTextField,@pattern,0,"IgnoreCase")
set @pinquiryemail = REPLACE(@pinquiryemail,@match," ")

I know that it is still better to fix at the source, but good to know this works.