Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1252
  • Last Modified:

sp_send_dbmail job

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
grantballantyne
Asked:
grantballantyne
  • 2
1 Solution
 
Tone' ShelbyMicrosoft® Platform Business Intelligence & Power BI Developer / Data AnalystCommented:
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
 
Tone' ShelbyMicrosoft® Platform Business Intelligence & Power BI Developer / Data AnalystCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now