If your users are selecting directly against a table, I know of no way to generate such a message.
If they are accessing the data through a stored procedure, after the select statement, examine the @@rowcount variable, and if that is more than your specified count, use sp_send_dbmail to send the e-mail.
YourSelectStatement inthe stored proc here
if (@@rowcount > 1000000)
exec sp_send_dbmail ....