I have a proc where I generate small html doc with a link and send it out via xp_smtp_sendmail proc. Link is generated based on query results and is long. This works in most cases. However, sometimes the link gets broken due to spaces being inserted into querystring variable names, i.e. &Na me=John.
This might vary between email clients(same link works in Gmail, but might not work in comcast due to spaces. The space seems to be randomly inserted, so in each broken email link space might break other querystring variables. When I do PRINT from proc the link is clean, no spaces.
Here's my sample of the mail proc being executed within main proc(that gets query results and generates html for @Message). The space seems to be inserted regardless of whether I encode the url or not.
Thank you in advance for your help. I can send a cleaner version of the code if it's not displayed properly here.
....query results above
SET @Message = NULL
SET @Message = @Message +
+ '<br/>Dear ' + @FirstName + ' ' + @LastName + ','
+ '<br/><br/>Recently you took "' + @Title + '". '
+ 'In response to the question "What is it?" '
+ 'you responded "' + @Response + '".'
+ '<br/><br/>Following up on previous mailing'
+ '<br/><br/>Please click on the link below'
+ '<br/><br/><a href="' + @Link + '">Please click here</a>'
+ '<br/><br/>plain text'
+ '<br/><br/>plain text,'
+ '<br/><br/>plain text<br/>
plain text<br/>
plain text<br/>
plain text<br/>
plain text<br/>
plain text
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = '[email protected]',
@FROM_NAME = 'Any User',
@TO = @Email,
@priority = N'NORMAL',
@subject = N'My email',
@message = @Message,
@messagefile = N'',
@type = N'text/html',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = 'smtp.server.any'
1 - Check the length of the message you generate and switch to sending from a file it the length comes close to 4000 char.
2 - Inject cr/lf chars before every sentence or paragraph.
3 - Make sure that you have cr/lf before and after a tag opening (or just lf if that's what smtp server likes)
4 - Talk to whoever you have to about shortening the length of that embedded link - it's plain unsustainable
5 - Ask about any configuration options on the smtp server to let you send e-mail as fully encoded binary (requires MIME headers and Base64 encoding)
6 - Look for a more recent alternative to xp_smtp_sendmail, a .NET xp perhaps
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With