Solved

sp_send_dbmail job

Posted on 2008-06-19
2
1,236 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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

15 Experts available now in Live!

Get 1:1 Help Now