Solved

sp_send_dbmail job

Posted on 2008-06-19
2
1,238 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
  • 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MSSQL 2014 Query Synthax 8 48
Optimizing a query 3 34
grouping logic 6 49
How to base a filter depending on fields contents? 15 48
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now