changing the endcoding of an attached file in sql trasaction

I see this is an old question, but posting my solution anyway. I was struggling with the same issue, except my attachment was being generated using UTF-16 LE BOM, and I too wanted UTF-8. Since sysmail_configure_sp has no effect on the encoding of the attachment <insert surprised emoji here> I had to find a different solution.

Turns out Excel does support UTF-16 / UTF-16 BOM for "seperated values" files, but only if the seperator being used is the TAB character.

The following code snippet works to generate an email with an attachment that opens up with proper columns in Excel:

DECLARE @tab CHAR
SET @tab = CHAR(9)

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'profile_name',
							 @recipients = '******@company.com',
							 @subject = N'Excel does not excel at UTF-16',
							 @body = 'This attachment works, I promise!',
							 @query = 'SET NOCOUNT ON; SELECT ''"'' + T1.column1 + ''"'', ''"'' + T1.column2 + ''"'' FROM dbo.table1 AS [T1]',
							 @attach_query_result_as_file = 1,
							 @query_result_separator = @tab,
							 @query_result_no_padding = 1;

Hi,

The document about the sysmail_configure_sp does not list the values that can specify for the parameter 'DefaultAttachmentEncoding'. It seems that specifying "UTF-8" is invalid, or that this option is not supported.

I saw some similar posts, the solution is to use powershell to generate attachments and then send the attachments.
https://dba.stackexchange.com/questions/284545/database-mail-has-incorrect-encoding-of-csv-attachment
https://dba.stackexchange.com/questions/6290/is-there-a-way-to-force-sp-send-dbmail-to-use-ansi-or-to-not-include-the-unicod

I too had this issue where the csv files had all row data in single column after opening in excel. I tried several ways to fix it from SQL but wasn't successful.
Finally I got some PowerShell code to fix the csv files once generated, probably from here:
https://stackoverflow.com/questions/5596982/using-powershell-to-write-a-file-in-utf-8-without-the-bom