November 21, 2005 at 11:17 am
Guru's,
Is there an @query paramater for xp_smtp_sendmail? I have heard there is, but after googling and finding the complete parameter list for this stored proc, I did not see it. I am currently using MAPI and xp_sendmail. As you know you can use @query to email the results. I am trying to move away from using MAPI, thus the reason for the use of xp_smtp_sendmail. I also know that you can export the findings to a file and then use the @attachment parameter, but unfortunately that is not an option for me in this case due to the laziness of executive management. Thanks for your help in advance.
CLR
November 22, 2005 at 6:01 am
What do you mean by laziness of management? That they don't want to open attachments? If the attachment is a HTML file (produced e.g. by sp_makewebtask) of the size less than 65535 bytes, you can send the attachment "embedded" into the message, so that users can see it as soon as they open the message. Of course the problem with larger attachments persists, but IMHO most messages should not exceed the limit.
Supposing that name of the file created by sp_makewebtask is stored in @HTML_File, this is how to embed the file into message:
EXEC master..xp_smtp_sendmail
@server = N'SERVER',
@from = N'nomail@company.com',
@from_name = N'Auto Check',
@to = some.manager@company.com,
-- @cc = ,
-- @bcc = ,
@subject = 'Auto Check: Orders not completed in time',
@messagefile = @HTML_File,
-- @message = @Message,
-- @attachment = @Attachment,
-- @attachments = @Attachments,
@priority = 'NORMAL',
@type = 'text/html'
You can write SP that checks for size of the file and then calls xp_smtp_sendmail, specifying @messagefile=@HTML_File when it is OK, or @attachment=@HTML_File if it is too big to fit into the body. Then, when writing code that sends mail, instead of calling xp_smtp_sendmail directly, you call your SP.
Hope this helped a bit...
Vladan
November 22, 2005 at 7:48 am
Yes, Vladan, executive management does not like to open attachments and they prefer to see the results in the body of the email. Thanks for your advice, I will give this a try for sure.
November 22, 2005 at 9:13 am
Well, I can't blame them much, because I hate attachments, too :-). We have lots of checks coming in every day - generally I get everything that is sent from the SQL mail - and opening attachments or casting a quick glance on the mail in preview window is quite a difference. Large part I'm getting as attachments, since we originally didn't know how to insert HTML into message body and didn't have time to rewrite all the procs since.
All new check procedures are written using the abovementioned approach (user SP that checks file size and calls xp_smtp_sendmail). BTW, the file check is done with EXEC master..xp_getfiledetails @MessageFile, one of the returned columns is called Size... that's the one you need. Parameter @MessageFile must contain the whole path.
November 22, 2005 at 9:22 am
Thanks brotha, I appreciate it.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply