I had a case where Unicode characters would show normally in Notepad, but opening them in Excel resulted in gibberish. I still wanted to use VBA because of automation via cscript. The FileSystemObject approach and the OpenText() subroutine failed to reproduce the Unicode characters. Instead, I used a combination of ADODB.Stream and brute conversion of each character to Unicode.
Set objStream = CreateObject("ADODB.Stream") objStream.CharSet = "UTF-8" objStream.Open objStream.LoadFromFile("C:\test.csv") objStream.Type = 2 strData = objStream.ReadText strData = ITSlugFunction(strData) ' see link below objStream.Close strData = "<html>" & strData & "..." ' write string to C:\test.html
You can embed this into an Outlook mail message and get Unicode characters from the command-line. Yes, Outlook will automatically treat &#xx; characters as Unicode!