Sending mail using DatabaseMail in SQL Server 2008
Depending on the method you use for sending mail: Yes

If you use sp_send_dbmail  (which is what I use), then yes, you can set the FROM address in the call to send mail.

See docs:
http://msdn.microsoft.com/en-us/library/ms190307.aspx

@from_address  is what you want, I believe

If you want to actually log in to the mail server as a diff user, then you need to mod the profile or create a new one. The auth to the mail server is configured in the profile.