November 17, 2011 at 7:34 am
I need to format the Body of an Email Message.
There is only one variable in the body.
I'm guessing that I could use carriage returns, line feeds and spaces to accomplish this.
Any help would be greatly appreciated.
The following is the desired format. I had trouble getting the paragraphs to indent on this post.:unsure:
Body
YOUR XYZ INCENTIVE SCORECARD
Thank you for participating in our new business Production Incentive.Since October 17th your Company has issued @CountVariable qualifying items.
Remember you only have until January 31st to qualify for an Incentive payment !
Thank you for using our Company .If you have any questions you can contact one of our representatives listed below.
Contact: Larry Lard
Phone: (555-555-5555)
Email: LLard@xyz.com
Contact: Sandra Snow
Phone: (555-555-5556)
Email: SSnow@xyz.com
Contact: Al Gord
Phone: (555-555-5557)
Email: AGord@xyz.com
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 17, 2011 at 7:37 am
email is raw text mode or html mode?
is that a static footer/email, or are the elements pulled dynamically in a SQL statement?
Lowell
November 17, 2011 at 8:05 am
Lowell (11/17/2011)
email is raw text mode or html mode?is that a static footer/email, or are the elements pulled dynamically in a SQL statement?
The Following are variables derived from a Cursor
@EMailRecipient
@Company
@BillToNumber
SET @Message = 'Total Sales Contracts Issued since 10/17/2011: ' + CAST(@SalesCount AS VARCHAR(12))
SET @MySubject = 'XYZ Incentive Program Production Status for ' + @Company + ' ' + CAST(@BillToNumber AS VARCHAR(7))
EXEC DatabaseName.dbo.usp_SMTPMail
@SenderName= @MySubject,
@SenderAddress= @EMailSender,
@RecipientAddress = @EMailRecipient, @Subject= @MySubject,
@body=@Message
As far as Raw TExt or HTML, I'm assuming that it is raw but honestly I don't really know.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 17, 2011 at 8:23 am
Database mail is disabled in that Server.
I created the following SP.
CREATE PROCEDURE [dbo].[usp_SMTPMail]
@SenderName VARCHAR(100),
@SenderAddress VARCHAR(100),
--@RecipientName VARCHAR(100),
@RecipientAddress VARCHAR(100),
@Subject VARCHAR(200),
@Body VARCHAR(8000)
AS
SET NOCOUNT ON
DECLARE @oMail INT --Object reference
DECLARE @resultcode INT
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail',
@oMail OUT
IF @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC sp_OADestroy @oMail
END
SET NOCOUNT OFF
GO
I believe that I need to change the Body Property to BodyFormat in the following line:
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
http://msdn.microsoft.com/en-us/library/ms526367(v=exchg.10).aspx
Then set @body to the HTML that I need.
I'm not sure how to do the following:
This code fragment shows the usage of the BodyFormat property in preparing and sending an HTML message:
Dim myMail
Set myMail = CreateObject("CDONTS.NewMail")
HTML = "<!DOCTYPE HTML PUBLIC ""-//IETF//DTD HTML//EN"">" & vbCrLf
HTML = HTML & "<html>"
HTML = HTML & "<head>"
HTML = HTML & "<meta http-equiv=""Content-Type"""
HTML = HTML & ""content=""text/html; charset=iso-8859-1"">""
HTML = HTML & "<title>Sample NewMail</title>"
HTML = HTML & "</head>"
HTML = HTML & "<body>"
HTML = HTML & "This is a sample message being sent using HTML.
</body>"
HTML = HTML & "</html>"
myMail.From = "user2@example.com"
myMail.To = "user@example.com"
myMail.Subject = "Sample Message"
myMail.BodyFormat = 0
myMail.MailFormat = 0
myMail.Body = HTML
myMail.Send
Set myMail = Nothing
I'm going to try a differnt approach, i.e. CR & LF & Tabs.
I'm opening a new post.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 17, 2011 at 2:44 pm
This site definitely needs a few options for the forums..."Question Resolved" and "Remove Post" would be ideal 🙂
Did you get your email issue all fixed up in the other post?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 17, 2011 at 3:04 pm
MyDoggieJessie (11/17/2011)
This site definitely needs a few options for the forums..."Question Resolved" and "Remove Post" would be ideal 🙂Did you get your email issue all fixed up in the other post?
I used HTML FormatBody.
I have not had time to post the solution.
Thanks!
I'm trying to resolve an issue is Reporting Services where I accidently deleted a text box after 10 hours of work.
I can't remember how to format the following so that it does not display the time:
=DateAdd("d", -(WeekDay(Today(),2))+1, Today())
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply