OPENROWSET does not accept variables for its arguments and you have to use
dynamic SQL. It may look like this:

DECLARE @FileDir NVARCHAR(80);
DECLARE @FileName NVARCHAR(80);
DECLARE @sql NVARCHAR(500);
DECLARE @params NVARCHAR(50);
DECLARE @cnt INT;

SET @FileDir = N'C:\Temp';
SET @FileName = N'emails.csv';
SET @params = N'@cnt_out INT OUTPUT';

SET @sql =
N'SELECT @cnt_out = COUNT(*)
FROM OPENROWSET(''MSDASQL'',
''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir= ' + @FileDir +
''', ''SELECT * FROM ' + @FileName + ''');';

EXEC sp_executesql @sql, @params, @cnt_out=@cnt OUTPUT;
SELECT @cnt;

HTH,

Plamen Ratchev
http://www.SQLStudio.com