I give in. Having spent the last week on and off trying to allow a SQL Server authenticated user to execute the xp_SendMail stored procedure I am at a loss as to what to do to allow this to happen. It looks like I am going to have to rewrite my code to utilise the new Database Mail method, something I could really do without at the moment. I will still need my code to execute the xp_sendmail stored procedure because my code still needs to use the xp_sendmail on SQL 2000 servers.
I suppose the alternative is to change the login from the specific one I have created, which can only access stored procedures I give it execute acess to (it cannot access views nor tables directly), and allow my application to login as the sa user. Ironically making the SQL Server 2005 less not more secure.