Using XP_CMDSHELL to create file and send email as attachment

	--Create a global temporary table to hold data to be exported
	if object_id('tempdb..##DataTemp') is not null 
	begin     
	drop table ##DataTemp 
	end 
	
	create table ##DataTemp (DataLine varchar(297))

	--Delete the file if it already exists
	set @cmd = 'del ' +  @FileLocation + @FileName
	exec master..xp_cmdshell @cmd


	--Populate global temporary table with GL Data
	insert ##DataTemp
	SELECT 	       
		REPLACE(RIGHT('**********' + vcBatchID, 10), '*', ' ')+','+							--BATCH_ID
		REPLACE(RIGHT('******************************' + vcDescription, 30), '*', ' ')+','+ --DESCRIPTION
	FROM tbSendData d
	JOIN  tbSendFile f
	ON d.iFileID = f.iFileID
	WHERE vcFileName = @FileName


	--Build a command to export GL data to a flat file
	SET @Cmd = 'bcp "SELECT DataLine FROM ##DataTemp " queryout '+ 
			   @FileLocation + 
			   @FileName + ' -T ' + ' -c '
			   --' -U ' + 
			   --@User + ' -P ' + 
			   --@Password + ' -c '


	--Export data to text file
	exec master..xp_cmdshell @cmd

	--Send email with file attachment
	EXEC msdb.dbo.sp_send_dbmail 
		@recipients= @Email, 
		--@body='', 
		@subject = @EmailSubject,
		@profile_name ='Database Profile',
		@file_attachments = @FileFullPath;


	----Clean up temporary table
	drop table ##DataTemp

Note: The account using to run this job will need read/write access to the folder.