i face same issue. I use SQLCMD or BCP method to convert the file as UTF-8. Please see my SP below for details

ALTER PROCEDURE [wmwhse1].[SP_CUSTOMER_GetLoadDataLastHourEmail]
    @StorerKeys NVARCHAR(500) = 'XYZ',
    @EmailTo NVARCHAR(255) = '[email protected]',
    @EmailSubject NVARCHAR(255) = '[PROD] CUSTOMER - Load Data Report Hourly'
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @FileName NVARCHAR(255);
    DECLARE @FilePath NVARCHAR(500);
    DECLARE @EmailBody NVARCHAR(MAX);
    DECLARE @CurrentDateTime NVARCHAR(50);
    DECLARE @HtmlTable NVARCHAR(MAX);
    DECLARE @RecordCount INT;
    DECLARE @BcpCommand NVARCHAR(4000);
    
    BEGIN TRY
        -- Generate timestamp for filename
        SET @CurrentDateTime = REPLACE(REPLACE(CONVERT(NVARCHAR(50), GETDATE(), 120), '-', ''), ':', '');
        SET @CurrentDateTime = REPLACE(@CurrentDateTime, ' ', '_');
        SET @FileName = 'LoadDataReport_' + @CurrentDateTime + '.csv';
        
        -- Set file path - ensure this directory exists and has write perHELLOsions
        SET @FilePath = 'C:\temp\' + @FileName;
        
        -- Get data for HTML table and record count
        DECLARE @TempTable TABLE (
            storerkey NVARCHAR(50),
            MANIFEST NVARCHAR(50),
            EXTERNALORDERKEY2 NVARCHAR(100),
            LOADSTOP_EDITDATE DATETIME
        );
        
        INSERT INTO @TempTable
        EXEC [wmwhse1].[SP_CUSTOMER_GetLoadDataLastHourData] @StorerKeys = @StorerKeys;
        
        SELECT @RecordCount = COUNT(*) FROM @TempTable;
        
        PRINT 'Records found in temp table: ' + CAST(@RecordCount AS NVARCHAR(10));
        
        -- Only proceed if we have data
        IF @RecordCount > 0
        BEGIN
            -- Create a global temp table for BCP export
            IF OBJECT_ID('tempdb..##TempLoadData') IS NOT NULL
                DROP TABLE ##TempLoadData;
                
            CREATE TABLE ##TempLoadData (
                storerkey NVARCHAR(50),
                MANIFEST NVARCHAR(50),
                EXTERNALORDERKEY2 NVARCHAR(100),
                LOADSTOP_EDITDATE VARCHAR(50)  -- Changed to VARCHAR for consistent formatting
            );
            
            INSERT INTO ##TempLoadData
            SELECT 
                storerkey,
                MANIFEST,
                EXTERNALORDERKEY2,
                CONVERT(VARCHAR(50), LOADSTOP_EDITDATE, 120)
            FROM @TempTable;
            
            PRINT 'Global temp table created with ' + CAST(@@ROWCOUNT AS NVARCHAR(10)) + ' records';
            
            -- Method 1: Try SQLCMD approach first (more reliable than BCP for this use case)
            SET @BcpCommand = 'sqlcmd -S' + @@SERVERNAME + ' -d SCPRD -E -Q "SET NOCOUNT ON; SELECT ''storerkey,MANIFEST,EXTERNALORDERKEY2,LOADSTOP_EDITDATE''; SELECT storerkey + '','' + ISNULL(MANIFEST,'''') + '','' + ISNULL(EXTERNALORDERKEY2,'''') + '','' + LOADSTOP_EDITDATE FROM ##TempLoadData ORDER BY LOADSTOP_EDITDATE DESC" -o "' + @FilePath + '" -h -1 -w 8000';
            
            PRINT 'Executing SQLCMD: ' + @BcpCommand;
            EXEC xp_cmdshell @BcpCommand;
            
            -- Check if file was created and has content
            DECLARE @CheckFileCommand NVARCHAR(500);
            SET @CheckFileCommand = 'dir "' + @FilePath + '"';
            PRINT 'Checking if file exists:';
            EXEC xp_cmdshell @CheckFileCommand;
            
            -- Alternative Method 2: If SQLCMD doesn't work, try BCP with fixed syntax
            DECLARE @FileSize TABLE (output NVARCHAR(255));
            INSERT INTO @FileSize
            EXEC xp_cmdshell @CheckFileCommand;
            
            -- If file is empty or doesn't exist, try BCP method
            IF NOT EXISTS (SELECT 1 FROM @FileSize WHERE output LIKE '%' + @FileName + '%' AND output NOT LIKE '%File Not Found%')
            BEGIN
                PRINT 'SQLCMD failed, trying BCP method...';
                
                -- Create CSV header
                DECLARE @HeaderCommand NVARCHAR(500);
                SET @HeaderCommand = 'echo storerkey,MANIFEST,EXTERNALORDERKEY2,LOADSTOP_EDITDATE > "' + @FilePath + '"';
                EXEC xp_cmdshell @HeaderCommand;
                
                -- BCP data export to temp file
                SET @BcpCommand = 'bcp "SELECT ISNULL(storerkey,'''') + '','' + ISNULL(MANIFEST,'''') + '','' + ISNULL(EXTERNALORDERKEY2,'''') + '','' + ISNULL(LOADSTOP_EDITDATE,'''') FROM ##TempLoadData ORDER BY LOADSTOP_EDITDATE DESC" queryout "' + @FilePath + '_data" -c -T -S' + @@SERVERNAME + ' -d SCPRD';
                
                PRINT 'Executing BCP: ' + @BcpCommand;
                EXEC xp_cmdshell @BcpCommand;
                
                -- Append data to header file
                DECLARE @AppendCommand NVARCHAR(500);
                SET @AppendCommand = 'type "' + @FilePath + '_data" >> "' + @FilePath + '"';
                EXEC xp_cmdshell @AppendCommand;
                
                -- Clean up temp file
                SET @AppendCommand = 'del "' + @FilePath + '_data"';
                EXEC xp_cmdshell @AppendCommand;
            END
            
            -- Final file check
            PRINT 'Final file check:';
            EXEC xp_cmdshell @CheckFileCommand;
        END
        ELSE
        BEGIN
            -- Create empty CSV with headers only
            DECLARE @EmptyFileCommand NVARCHAR(500);
            SET @EmptyFileCommand = 'echo storerkey,MANIFEST,EXTERNALORDERKEY2,LOADSTOP_EDITDATE > "' + @FilePath + '"';
            EXEC xp_cmdshell @EmptyFileCommand;
            PRINT 'Created empty CSV file with headers only';
        END
        
        -- Build HTML table (same as before)
        SET @HtmlTable = '
        <style>
            table { border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; }
            th { background-color: #4CAF50; color: white; padding: 12px; text-align: left; border: 1px solid #ddd; }
            td { padding: 8px; border: 1px solid #ddd; }
            tr:nth-child(even) { background-color: #f2f2f2; }
            tr:hover { background-color: #f5f5f5; }
            .summary { background-color: #e7f3ff; padding: 10px; margin: 10px 0; border-left: 4px solid #2196F3; }
        </style>
        
        <div class="summary">
            <strong>Report Summary:</strong><br/>
            Generated: ' + CONVERT(NVARCHAR(50), GETDATE(), 120) + '<br/>
            Storer Keys: ' + @StorerKeys + '<br/>
            Time Range: Last 1 hour<br/>
            Total Records: ' + CAST(@RecordCount AS NVARCHAR(10)) + '<br/>
            <span style="color: green;"><strong>File Encoding: UTF-8</strong></span>
        </div>
        
        <table>
            <thead>
                <tr>
                    <th>Storer Key</th>
                    <th>Manifest</th>
                    <th>External Order Key</th>
                    <th>Load Stop Edit Date</th>
                </tr>
            </thead>
            <tbody>';
        
        -- Add table rows
        IF @RecordCount > 0
        BEGIN
            SELECT @HtmlTable = @HtmlTable + 
                '<tr>' +
                '<td>' + ISNULL(storerkey, '') + '</td>' +
                '<td>' + ISNULL(MANIFEST, '') + '</td>' +
                '<td>' + ISNULL(EXTERNALORDERKEY2, '') + '</td>' +
                '<td>' + CONVERT(NVARCHAR(50), LOADSTOP_EDITDATE, 120) + '</td>' +
                '</tr>'
            FROM @TempTable
            ORDER BY LOADSTOP_EDITDATE DESC;
        END
        
        SET @HtmlTable = @HtmlTable + '</tbody></table>';
        
        -- Handle case when no data found
        IF @RecordCount = 0
        BEGIN
            SET @HtmlTable = '
            <div class="summary">
                <strong>Report Summary:</strong><br/>
                Generated: ' + CONVERT(NVARCHAR(50), GETDATE(), 120) + '<br/>
                Storer Keys: ' + @StorerKeys + '<br/>
                Time Range: Last 1 hour<br/>
                <span style="color: orange;"><strong>No records found for the specified criteria.</strong></span>
            </div>';
        END
        
        -- Create email body
        SET @EmailBody = 'Please find the Load Data Report for the last hour below and attached as UTF-8 encoded CSV.
        
        ' + @HtmlTable + '
        
        <br/><br/>
        <p style="font-size: 12px; color: #666;">
        This is a system generated email, please do not reply.<br/>
        CSV file is encoded in UTF-8 format.
        </p>';
        
        -- Send email with HTML body and UTF-8 CSV attachment
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'HELLO',
            @recipients = @EmailTo,
            @subject = @EmailSubject,
            @body = @EmailBody,
            @body_format = 'HTML',
            @file_attachments = @FilePath;
        
        -- Clean up
        IF OBJECT_ID('tempdb..##TempLoadData') IS NOT NULL
            DROP TABLE ##TempLoadData;
        
        -- Optionally delete the file after sending
        DECLARE @DeleteCommand NVARCHAR(500);
        SET @DeleteCommand = 'del "' + @FilePath + '"';
        EXEC xp_cmdshell @DeleteCommand;
        
        PRINT 'Email sent successfully with UTF-8 CSV attachment: ' + @FileName;
        PRINT 'Records processed: ' + CAST(@RecordCount AS NVARCHAR(10));
        
    END TRY
    BEGIN CATCH
        -- Clean up in case of error
        IF OBJECT_ID('tempdb..##TempLoadData') IS NOT NULL
            DROP TABLE ##TempLoadData;
        
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();
        
        PRINT 'Error occurred while sending email: ' + @ErrorMessage;
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END