[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

sp_send_dbmail job

Posted on 2008-06-19
2
Medium Priority
?
1,249 Views
Last Modified: 2011-09-20
Dear Experts

I have a sql sp_send_dbmail job which emails users an HTML email.  The email contains an image which is held on our webserver.  This images displays fine if the email is received in outlook but is missing if received in outlook express.  

I wondered if anyone can offer some advise as to how I can have the image display in Outlook Express.  Please see my syntax for the SQL job below.

Thanks

Grant
Declare @MyRecipients nvarchar (255)
Declare @firstname nvarchar (50)
Declare @Subject nvarchar (50)
Declare @ref int
Declare @urlref nvarchar (255)
Declare @postcode nvarchar (50)
Declare @minprice money
Declare @maxprice money
Declare @area char (20)
Declare @minbedrooms char
Declare @price money
Declare @pricetype char (20)
Declare @paddress1 char (60) 
Declare @paddress2 char (60) 
Declare @ptown char (60) 
Declare @bedrooms char
Declare @image_string nvarchar (4000)
Declare @dateadded nvarchar (50) 
Declare MyCursor Cursor For 
Select username,firstname,pRef,pPostCode,minprice, maxprice, area, minbedrooms,pPrice,pPriceType,pAddress1, pAddress2, pTown, pBedRooms, dateadded + 30 as dateadded
from todays_property_matches
 
Open MyCursor
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@postcode,@minprice,@maxprice,@area,@minbedrooms,@price,@pricetype,@paddress1,@paddress2,@ptown, @bedrooms, @dateadded
While @@Fetch_Status = 0
BEGIN
 
 
 
 
set @urlref = replace('http://www.bspc.co.uk/propertydetails.asp?pRef='  + rtrim(convert(nvarchar(4),@ref)) + '&' + 'pPostCode=' + rtrim(@postcode) + '' , ' ','%20')
 
 
SET @image_string =
    N'<table width="700" border="0" cellpadding="5" cellspacing="0" bgcolor="#E3EFFF">
  <tr>
    <td height="70" align="center" bgcolor="#B91518"><a href="http://www.bspc.co.uk/"><img src="http://www.bspc.co.uk/propertymatching/header.gif" alt="THE BSPC Property Guide" width="675" height="70" border="0" /></a></td>
  </tr>
<br>
 
  <tr>
	<td valign="middle"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>
	Dear ' + @firstname + ',</font></td> 
	</tr>
 
<tr>
    <td valign="middle"  ><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
      A property which matches your criteria has been added to the BSPC Property Guide   database and website. The property details are given below, click on the link or property photograph to view full   details.<br />
      <br />
    </font></td>
  </tr>
  <tr>
    <td><table width="100%" cellpadding="0" cellspacing="5" bgcolor="#E3EFFF">
      <tr>
        <td width="52%" height="245" align="center" valign="top" bordercolor="#FFFFFF" bgcolor="#E3EFFF">
            <a href="' + @urlref + '"<img src="http://www.bspc.co.uk/propertyphotos/' + rtrim(convert(nvarchar(10), @ref )) +  '.jpg" align="middle" alt="Property Image" width="345" height="235" hspace="0" vspace="0"/></a></td>
        <td width="48%" align="left" valign="top"><strong><font color="#003366" size="3" face="Verdana, Arial, Helvetica, sans-serif">'  + @pricetype + '£' + convert(nvarchar(15),@price) +  '</font></strong><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>
          <br>' + rtrim(convert(nvarchar(60), @paddress1 )) + ' ' + rtrim(convert(nvarchar(60), @paddress2 )) + '
 
          <br>'
          + rtrim(convert(nvarchar(60), @ptown))+ '<br>' 
          + rtrim(convert(nvarchar(60), @postcode))+ ' <br>
          <BR>
            Bedrooms: ' +  rtrim(convert(nvarchar(10), @bedrooms))+ '</font><br>
            <br>
            <a href="' + @urlref + '" <font color="#003366" size="2" face="Verdana, Arial, Helvetica, sans-serif">View property details on www.bspc.co.uk</font></a><font color="#003366"><br>
            </font>
 
          
                  </td>
      </tr>
    </table>    </td>
  </tr>
  
  <tr>
    <td><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br />
      Please note that your registration to receive new properties by email will expire on <strong>' + @dateadded + '</strong>. <br />
    Login to <a href="http://www.bspc.co.uk/"><font color="#003366">www.bspc.co.uk</font></a> to renew your registration. The  information contained in this email is time sensitive and may have changed between publish, delivery and reading.<br />
    <br />
    </font></td>
  </tr>
  <tr>
    <td height="1" bgcolor="#003366"><img src="images/spacer.gif" alt="spacer" width="1" height="1" /></td>
  </tr>
  <tr>
    <td height="40" align="center"><strong><font size="2" face="verdana, sans-serif">      The Borders Solicitors Property Centres Property Guide and <a href="http://www.bspc.co.uk/"><font color="#003366">www.bspc.co.uk</font></a> <br />
      are exclusive to clients 
      
      selling   their property with a Solicitor</font></strong></td>
  </tr>
</table>' 
 
 
Print @MyRecipients 
 
EXEC msdb.dbo.sp_send_dbmail
@recipients='cp@broomlands.com',
@body=@image_string,
@body_format = 'HTML',
@sensitivity ='Personal', 
@importance ='High', 
@subject ='BSPC Property Match',
@profile_name ='BMC';
 
 
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@postcode,@minprice,@maxprice,@area,@minbedrooms,@price,@pricetype,@paddress1,@paddress2,@ptown,@bedrooms,@dateadded
End
Close MyCursor
Deallocate MyCursor

Open in new window

0
Comment
Question by:grantballantyne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 9

Expert Comment

by:Tone' Shelby
ID: 21828413
Hi
you may not really have a problem with your code at all. Outlook express is a bit problematic showing images as a whole. Preferences & Settings in Outlook Express do seem to get changed etc when people upgrade thier browsers etc. etc. You may want to check the settings on the machine that tets your code if you haven't already.
The run it again to be sure...

When settings get changed or they weren't set up to allow images in an html message this can cause them not to show..

There is a support document at microsoft  entitled:
:Images are blocked when you open an e-mail message in Outlook Express on a Windows XP Service Pack 2-based computer"   that explains it more:

see: http://support.microsoft.com/kb/843018

0
 
LVL 9

Accepted Solution

by:
Tone' Shelby earned 2000 total points
ID: 21828445
SP2 included the turnoff images in that upgrade and MS official word on it is as follows with the disclaimer:

Important This article contains information that shows you how to help lower security settings or how to turn off security features on a computer. You can make these changes to work around a specific problem. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this workaround in your particular environment. If you implement this workaround, take any appropriate additional steps to help protect your system.

Manual steps to enable Internet images in Outlook Express
To manually work around this behavior, turn off the Block images and other external content in HTML e-mail option. To do this, follow these steps.

Warning The Block images and other external content in HTML e-mail option is a security feature that has been included in Windows XP SP2 to protect your computer against junk e-mail (spam). We recommend that you do not turn off this option.

1. Click Start, point to Programs, and then click Outlook Express.
2. On the Tools menu, click Options, and then click the Security tab.
3. Click to clear the Block images and other external content in HTML e-mail check box.
4. Click OK.


0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question