Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: @variable in sp_send_dbmail HTML body

    Hi all

    I'm almost there with my app.
    I have created a .bat file for my sqlcmd
    my sqlcmd includes a .sql file that builds up my email with a HTML body.
    Is it possible to insert a variable (@customername) in to the HTML body?
    I am already using @maillist and select statement to create my customer email address's.

    Hope that makes sense!

    Thanks in advance
    Andy

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Got code?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    Hi gvee

    Here's my code
    When i run it i get the variable code rather than the variable value :-)

    Thanks for helping
    Andy


    Code:
    Declare @TempEmails TABLE (TempID int IDENTITY(1,1), TempCustID int, TempEmail char(100), TempTitle char(10), TempFirstName char(200), TempLastName char(200), TempPoints int) 
    INSERT INTO @TempEmails (TempCustID, TempEmail, TempTitle, TempFirstName, TempLastName, TempPoints) 
    Select dbo.Customers.CustomerID, dbo.Customers.CustomerEmail, dbo.Customers.Title, dbo.Customers.FirstName, dbo.Customers.LastName, dbo.loyalty_points.LPoints FROM dbo.Customers INNER JOIN dbo.loyalty_points ON dbo.Customers.CustomerID = dbo.loyalty_points.CustomerID 
    WHERE (dbo.Customers.CustomerID=11224)
    
    Declare @count as int 
    set @count =1
    Declare @Recepient_Email as varchar(100)
    Declare @CustTitle as char(10)
    Declare @CustFirstname as char(200)
    Declare @CustLastName as char(200)
    Declare @CustLPoints as int
    
    while (@count <=(select COUNT(*) from @TempEmails)) 
    begin
    
    set @Recepient_Email =(select top(1) TempEmail from @TempEmails where TempID=@count)
    set @CustTitle =(select top(1) TempTitle from @TempEmails where TempID=@count)
    set @CustFirstname = (select top(1) TempFirstName from @TempEmails where TempID=@count)
    set @CustLastName = (select top(1) TempLastName from @TempEmails where TempID=@count)
    
    
    DECLARE @tableHTML  NVARCHAR(MAX)
    Set @tableHTML = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Loyalty Points</title>
    <style type="text/css">
    <!--
    #wrapper {
    	height: 800px;
    	width: 600px;
    }
    #wrapper #custname {
    	position: absolute;
    	z-index: 2;
    	left: 48px;
    	top: 197px;
    	font-family: Geneva, Arial, Helvetica, sans-serif;
    	font-size: 12px;
    	color: #FFFFFF;
    	width: 200px;
    }
    -->
    </style>
    </head>
    
    <body>
    <div id="wrapper"><img src="http://www.mysite.co.uk/assets/img/lpreminder_93.jpg" width="600" height="800" />
    <div id="custname">@CustTitle &nbsp;@CustFirstname &nbsp;@CustLastName </div>
    <div id="custpoints">customer points </div>
    <div id="pointsexpiry">expiry date </div>
    </div>
    
    </body>
    </html>'
    
    
    
    EXEC msdb.dbo.sp_send_dbmail
    
                @profile_name='DefaultMailProfile',
    
                @recipients=@Recepient_Email,            
    
                @subject = 'This is subject of test Email',
    
                @body = @tableHTML,
    
                @body_format = 'HTML'
    
                set @count =@count +1
    
                END

  4. #4
    Join Date
    Feb 2008
    Posts
    120
    ok i'm getting closer :-)
    I'm using +@CustTitle+ or similar to get my variable values into the HTML body.
    But, one of the datatypes is an INT and for some reason, it doesn't like it.
    The variable is Declared as an INT before inserting into my temptable.
    I get the following:

    Msg 245, Level 16, State 1, Line 26
    Conversion failed when converting the varchar value to data type int

    can't figure it out because it's already an INT



    Andy

  5. #5
    Join Date
    Feb 2008
    Posts
    120
    hmm

    I'm guessing it's because i'm Declaring the HTML table as NVARCHAR(MAX) and my int variable sits within the HTML code.

    Maybe?

    Andy

  6. #6
    Join Date
    Feb 2008
    Posts
    120
    all done!
    I used the Convert method to change the datatype to varchar(10).
    If anyone wants to use sp_send_dbmail, please feel free to use my code or ask for help (if i can).

    Thanks to all

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •