Try this. The problem with your query is that @username local variable wont be available in the @query because it is being executed in separate session. You have already got the password in @Password so I am using it in your send mail.
CREATE PROCEDURE [dbo].[spGetPassword](
@Username Varchar(50))
AS
BEGIN
DECLARE @Password Varchar(100)
DECLARE @RecEmail Varchar(100)
DECLARE @bodyMsg nvarchar(max)
Select @Password=[Password] From [dbo].[LoginDetails] Where [UserName]=@Username
set @bodyMsg = 'Hi, Your password is' + @Password
Select @RecEmail= Value FROM MemberDetails WHERE EmpName=@Username AND Name='Email'
execute msdb.dbo.sp_send_dbmail
@profile_name='*******',
@recipients=@RecEmail,
@subject= 'Password Recovery',
@body = @bodyMsg
END