Post reply

Another way to format @message in XPSMTP or Stored Procs period

  • January 16, 2006 at 11:29 am

    #188911

    Is there a more manageable way to send body text such as using HTML formatted string to fill the body of my

    XPSMTP mail message than having to create a concatenated string that has no column headers?  I am doing it this way below but that means I have to space out each header to align with the columns and it's a pain and takes a lot of time:

    In my stored procedrue I have this line to setup the headers:

    SET

    @SUMMARY = 'REPORT' + CHAR(13) + CHAR(13) + 'Phone # ' + ' Hr Called ' + 'Call Date ' + ' State ' + ' Zone ' + ' Stop 1 ' + ' Stop Hr ' + CHAR(13) + '------------' + ' ---------' + ' -------------------' + ' ------' + ' -------' + ' -------------------' + ' ------' + CHAR(13) + @SUMMARY

    Then I append to that with line returns later after my cursor to put in the actual fields:

    SET

    @SUMMARY = ISNULL(@SUMMARY + '', '') + @Phonenum + ' ' + @hrcld + ' ' + @calldatetime + ' ' + @state + ' ' + @zonename + ' ' + @stop1 + ' ' + @stphr + CHAR(13)

    I simply shove this off to the message paramter of my XPSMTP mail like this:

    @message

    = @SUMMARY

    If I could somehow do this in HTML and use tables rather than one big long unformatted string, it would be an extreme time saver.

  • January 16, 2006 at 2:56 pm

    #615008

    Take a look at sp_makewebtask, with it you can generate a HTML file and then use it as the message body.

     

    --------------------
    Colt 45 - the original point and click interface

  • January 16, 2006 at 4:33 pm

    #615027

    thank you!

  • January 18, 2006 at 4:10 am

    #615415

    Hi,

    SET @SUMMARY = '%html$%body$%H1$REPORT%/H1$%br$%br$%table$' + '%tr$%td$Phone # %/td$%td$ Hr Called %/td$%td$Call Date %/td$%td$ State %/td$%td$ Zone %/td$%td$ Stop 1 %/td$%td$ Stop Hr %/td$%/tr$'

    Then append to that with line returns later after my cursor to put in the actual fields:

    SET @SUMMARY = @SUMMARY + '%tr$%td$' + @Phonenum + '%/td$%td$ ' + @hrcld + '%/td$%td$' + @calldatetime + '%/td$%td$' + @state + '%/td$%td$' + @zonename + '%/td$%td$' + @stop1 + '%/td$%td$' + @stphr %/td$%/tr$

    And at the End:

    SET @SUMMARY = @SUMMARY + '%/table$%/body$%/html$'

    REPLACE( @SUMMARY, '%', '')

    /* This replacement is only because this forum is a web site, showing html code here is difficult. you might do that prior to running the code ;-)... */

    regards karl

    Best regards
    karl

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply