Hi Jeff,
Thanks for the help. After doing few experiments I managed to fix the issue. The issue wasn't in the mail profile and this is what I have done.

I have created a user login in SQL Server (eg: WebEmailUser) with public server role.

And under user mapping for the database which I am executing the query I gave 'public' & 'db_datareader' database roles.

Under msdb database I have included the 'DatabaseMailUserRole'.

When I am executing the procedure I execute it as WebEmailUser.

EXECUTE AS LOGIN = 'WebEmailUser'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestMailProfile',
@recipients = '<valid_email>',
@subject = 'Email Attachement Check',
@body = 'Sample Body Text',
@body_format = 'HTML',
@query = N'SELECT GETDATE() AS CurrentDate;',
@execute_query_database = 'master',
@attach_query_result_as_file = 1,
@exclude_query_output = 0,
@query_attachment_filename = 'Attachment.txt'


Then I was able to fix my problem. 😀

Mail (Id: 13556) queued.