The body of the email can't be seen when using sp_send_dbmail in sql server 2005

Question

I create a procedure to send automatic emails. The email gets to the address and everything seems working fine but the body can not be seen. I'm using sql 2005 and MS exchange server 2007. The part of the procedure that writes the body is as follow.

declare @bodymsg as varchar(1000)
 set @bodymsg = 'The application '
 set @bodymsg = @bodymsg + @appnum
 set @bodymsg = @bodymsg + ' have been auto assign to you by the call center auto assign program.'
 set @bodymsg = @bodymsg + CHAR(13)
 set @bodymsg = @bodymsg + 'The borrower information is as follow:'
 set @bodymsg = @bodymsg + CHAR(13)
 set @bodymsg = @bodymsg + 'Name: '
 set @bodymsg = @bodymsg + @borrower
 set @bodymsg = @bodymsg + CHAR(13)
 set @bodymsg = @bodymsg + 'Email: '
 set @bodymsg = @bodymsg + @borremail
 set @bodymsg = @bodymsg + CHAR(13)
 set @bodymsg = @bodymsg + 'Phone: '
 set @bodymsg = @bodymsg + @borrhome
 set @bodymsg = @bodymsg + CHAR(13)
 set @bodymsg = @bodymsg + 'Cellphone: '
 set @bodymsg = @bodymsg + @borrcell
 set @bodymsg = @bodymsg + CHAR(13)
 set @bodymsg = @bodymsg + CHAR(13)
 set @bodymsg = @bodymsg + 'Please contact the borrower ASAP.'

 execute [msdb].[dbo].[sp_send_dbmail]
  @profile_name = 'CallCenter',        
  @recipients   = @email, 
  @subject      = @subjectmsg,
  @body         = @bodymsg,
  @body_format  = 'TEXT'

Answer 1

Just before you execute the sp_send_dbmail PRINT the @bodymsg parameter out so that you know the data has been built correctly.

e.g.

PRINT @bodymsg
execute [msdb].[dbo].[sp_send_dbmail] 
@profile_name = 'CallCenter',         
@recipients   = @email,  
@subject      = @subjectmsg, 
@body         = @bodymsg, 
@body_format  = 'TEXT' 

As you passing a number of parameters to it one of them could be setting the @bodymsg to NULL

Answer 2

Just to add to kevchadders reply, when you call PRINT @bodymsg, if it prints nothing at all then you know that it's a null-concatenation problem.

If you wrap each variable you append in the ISNULL() function, then it will be easier to find which variable is causing the problem.

eg.

set @bodymsg = @bodymsg + ISNULL(@appnum,'')

Then the body of the email will be printed, but there will be a missing parameter. Then you need to find out why the parameter is missing.