September 16, 2002 at 3:42 pm
In the following script I declare a variable called Message. This variable is used as a message line in for the email that is sent out. The question I have is: Is there a way to place a line break within the SQL so that everything isn't on one line when it is sent. Also I have a URL within the SQL and wanted the user to be able to just click on the link to take them to a particular place on the network. All users by the way have there Outlook set to message format of HTML. Any help would help. Thanks
Declare @Message varchar(165), @CurrentMonth nvarchar(15), @CurrentYear nvarchar(4)
SET @CurrentMonth = (select datename(m, getdate()))
SET @CurrentYear = (select datename(yy, getdate()))
SET @Message = ('PC Data has been loaded for the month of: ') +
(@CurrentMonth) + ('URL: Q:\Shared\Payor Data\Load\PC\') + (@CurrentYear)+ '\' + (@CurrentMonth)
EXEC master..xp_sendmail
@recipients = 'Brady, Kelly',
@subject = 'PC Data Loaded!',
@message = @Message
September 16, 2002 at 6:39 pm
I did a little research and came up with the following after typing in Cariage Return in Books Online
Control character Value
Tab CHAR(9)
Line feed CHAR(10)
Carriage return CHAR(13)
So now the script reads as follows:
Declare @Message varchar(165), @CurrentMonth nvarchar(15), @CurrentYear nvarchar(4)
SET @CurrentMonth = (select datename(m, getdate()))
SET @CurrentYear = (select datename(yy, getdate()))
SET @Message = ('PC Data has been loaded for the month of: ') +
(@CurrentMonth) + CHAR(13) + CHAR(10) +('URL: Q:\Shared\Payor Data\Load\PC\') + (@CurrentYear)+ '\' + (@CurrentMonth)
EXEC master..xp_sendmail
@recipients = 'Brady, Kelly',
@subject = 'PC Data Loaded!',
@message = @Message
As far as the url is concerned I still haven’t got that one figured out. When I send the email out it will not hyperlink the url. I have since then tried using this little stored procedure to either send HTML or text in my email:
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/*********************************************************************
This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp-server.rochester.rr.com'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAG! etErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
This will give you the flexibility to send in either text or html and also is cool if you don't have an email server
Edited by - kbrady on 09/16/2002 6:44:05 PM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply