Alternative to xp_cmdshell for emailing a report as a CSV file
Since using sp_send_dbmail is an option here, I don't see why you need to export anything given that sp_send_dbmail can run a query and include the results, either in the body or as an attachment. I would first try to make use of the @query, @attach_query_result_as_file, @query_attachment_filename, @query_result_header, @query_result_width, @query_result_separator, @query_no_truncate, and @query_result_no_padding parameters. Please keep in mind this note from that MSDN page (i.e. the link above) regarding the query to run:


  Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.


Because of this, and assuming that you won't have this process running more than once at any particular moment, you should dump the desired rows from the Table Variable into a Global Temporary Table (i.e. starting with ## instead of #).

I believe sp_send_dbmail uses Service Broker and is asynchronous. This would seem to allow for the possibility of the global temporary table not existing when the query in @query finally gets executed, if the session that executed sp_send_dbmail ends before the query in @query starts. In this case, you can do either:


add a WAITFOR DELAY '00:00:10.000'; to add a 10-second delay before the calling session ends.
instead of using a global temporary table, use NEWID() to construct a unique table name for the purpose of dumping that data into tempdb in a real table that will exist for as long as you need it to. Then you can drop it at the end of the query that you submit for the report. The bigger issue is that now Dynamic SQL is needed for the Table name, yet that won't work with a Table Variable since that can't be passed into Dynamic SQL. We need a known and consistent object name to insert the rows from the Table Variable into the real Table. Thankfully, a SYNONYM can provide a consistent name in the current context that points to the real Table in tempdb since they can be created in Dynamic SQL. That gives us the following (and you can run the following to see that it works, though I did not implement the final DROP of the real Table in tempdb):

SET NOCOUNT ON;

DECLARE @TableName NVARCHAR(70) = N'tempdb.dbo.[Report_'
                                  + CONVERT(NVARCHAR(36), NEWID()) + N']';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'CREATE TABLE ' + @TableName + N' ([Col1a] INT);';
EXEC (@SQL);

SET @SQL = N'CREATE SYNONYM dbo.TempDump FOR ' + @TableName + N';';
EXEC (@SQL);

DECLARE @SomeTableVar TABLE ([Col1b] INT);
INSERT INTO @SomeTableVar ([Col1b]) VALUES (1), (20), (34), (4444);

INSERT INTO dbo.TempDump ([Col1a])
  SELECT Col1b
  FROM   @SomeTableVar;

SELECT * FROM dbo.TempDump; -- only needed for debug

SET @SQL = N'DROP SYNONYM dbo.TempDump;';
EXEC (@SQL);


-- create the report query, ending with the DROP statement
DECLARE @ReportSQL NVARCHAR(MAX) = N'';
SET @ReportSQL += N'my report, sent as @query, using ' + @TableName + N';';
SET @ReportSQL += NCHAR(0x0D) + NCHAR(0x0A) + N'DROP TABLE ' + @TableName + N';';
print @ReportSQL;

-- EXEC  sp_send_dbmail ..., @query = @ReportSQL, @attach_query_result_as_file = 1,
--                      @query_result_separator = N',', @query_no_truncate = 1, ...;


If the process fails after creating the Table but before the DROP statement is called, no problem: tempdb is created from the empty model Database every time the SQL Server service restarts (which is why I am suggesting to create the Tables in tempdb!).