September 15, 2010 at 2:14 pm
Hello folks,
I know there are lots of useful script/approaches on how to send out HTML email from SQL, one particular situation I am having is: is it possible to send out this kind of HTML email and know the recipient actually opened it and record the time stamp? Normally the way to ask for a READ receipt is (in outlook) mark the email as "Request for READ receipt" and the recipient MUST click on a button to actually SEND out the receipt, I don't want that, many people don't like that, so I just want quietly record the user's READ time stamp for future reference.
Besides, creating HTML email seems to be really tedious, is there any good approach that I can create it based on some sort of template? Most solutions I've seen are creating HTML code piece by piece, really cumbersome and not easy for maintenance.
Thanks.
September 15, 2010 at 2:34 pm
you'll have to create something in code or in CLR;
the ability to READ emails ( the read receipt IS an email)
was removed when sp_send_dbmail was introduced;
so there is no wat to do what you are asking from within SQL anymore.
it would probably be easier to create an application which reads the mailbox and if the email address or a certain unique id in the message or body exists, you can have the application update the database for the row that is referenced by that unique value/ email it was sent from.
since the sending a return receipt / read receipt is an option, there's no guarantee that you'll get responses for the emails you send out.
one sleazy way to know if someone opened an email is to have the src of an img tag be processed by a server side script, that was passed a uniqueId;
so if the end user has enabled the images in the email browser, you know if they opened it;
this is exactly what you see on a daily basis in your spam folder...it's how they can test your email address and if you open/downlaod the image, they know they can sell your email or dump a ton of spam at you.
Lowell
September 15, 2010 at 2:41 pm
Lowell (9/15/2010)
one sleazy way to know if someone opened an email is to have the src of an img tag be processed by a server side script, that was passed a uniqueId;so if the end user has enabled the images in the email browser, you know if they opened it;
this is exactly what you see on a daily basis in your spam folder...it's how they can test your email address and if you open/downlaod the image, they know they can sell your email or dump a ton of spam at you.
Exactly what I was going to recommend...unfortunately Outlook, Gmail, Yahoo, etc, all now block images from unknown addresses unless you explicitly allow it.
The whole point of read receipts is that the reader has a choice on whether or not to send it, and that is by design in all modern email applications. If I accidentally arrow-up onto an email I don't have time to read yet, I don't want to send a read receipt for it right then, because I haven't actually read it yet.
September 16, 2010 at 9:27 am
Thank you guys for replying.
I knew the trick putting an image in the email and I knew many email system allows user to block it.
It IS very TRICKY to do this, I guess spam sender is using some tricky method to determine what email address is active.
Maybe embedding a server side "dot" in the email and make it only visible when the email is opened - which means the recipient opened the email and retrieved the "dot" from server, how does this sound? do-able?
or there are better ideas?
Any hints would be appreciated.
Thanks.
September 16, 2010 at 9:31 am
Lowell (9/15/2010)
one sleazy way to know if someone opened an email is to have the src of an img tag be processed by a server side script, that was passed a uniqueId;
I wouldn't say it's "sleazy", I would rather say it's "tricky", :-D:-D there are lots of legitimate reasons to ask for this kind of requirement.
August 24, 2015 at 1:54 pm
You can find out if SQL was at least successful at sending the email to the SMTP server: not the warm fuzzy blanket of getting a delivery receipt, but at least you'll know of there was a failure in the SQL path. This is the query I run about an hour after a batch email process runs:
SELECT TOP 1000
m.sent_date
, CASE m.sent_status WHEN '1' THEN 'Sent' WHEN '2' THEN 'Failed' WHEN '3' THEN 'Unsent' ELSE 'Unknown' END AS sent_status
, SUBSTRING(l.[description], CHARINDEX('Exception Message:', l.[description]), 2000) AS fail_description
, m.from_address
, m.recipients
, m.reply_to
, m.[subject]
, m.body
, m.file_attachments
,l.[mailitem_id]
FROM [msdb].[dbo].[sysmail_log] l
JOIN msdb.dbo.sysmail_mailitems m
ON l.mailitem_id = m.mailitem_id
WHERE m.sent_status <> '1' -- Sent
ORDER BY log_date DESC
August 24, 2015 at 7:22 pm
halifaxdal (9/15/2010)
Hello folks,I know there are lots of useful script/approaches on how to send out HTML email from SQL, one particular situation I am having is: is it possible to send out this kind of HTML email and know the recipient actually opened it and record the time stamp? Normally the way to ask for a READ receipt is (in outlook) mark the email as "Request for READ receipt" and the recipient MUST click on a button to actually SEND out the receipt, I don't want that, many people don't like that, so I just want quietly record the user's READ time stamp for future reference.
Besides, creating HTML email seems to be really tedious, is there any good approach that I can create it based on some sort of template? Most solutions I've seen are creating HTML code piece by piece, really cumbersome and not easy for maintenance.
Thanks.
To be honest, this would be dishonest and it doesn't actually matter if the person on the other end reads the email or not. All you have to do is prove that you sent the email in good faith.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply