Running a user group and being one of the founders of SQLBits I have a need to send bulk emails out to a few thousand people; I also do this for some of my clients. Using Database Mail in SQL Server 2005 works fine but has the disadvantage that only the basic email properties are exposed, so, you can't set the Sender property etc...
Anyway, the CLR below does just that; it basically means that instead of the mail looking like SPAM to a few mail servers you now get the more friendly headers shown in Outlook and hotmail -> From: tonyrogerson@torver.net on behalf of UK SQL Server User Group (sqlfaq@sqlserverfaq.com).
Unfortunetly the assembly needs to run with the 'External' permission level which means you'll have to set the database to TRUSTWORTHY ON etc...
I've only coded it for one attachment, but I'll probably expand that, the parameter AttachmentFileName would simply be CSV which I'd split out in the CLR proc. The same with the ToEmail.
This procedure will also be handy for those people who use SQL Server Express edition because Database Mail doesn't work on that edition.
The example call in SQL Server T-SQL would be:
declare @SendStatus varchar(max)
declare @rc int
exec @rc = smtpfullemail
@SMTPServer = 'yoursmtpservername',
@SenderEmail = 'tonyrogerson@torver.net',
@FromEmail = 'sqlfaq@sqlserverfaq.com',
@FromName = 'UK SQL User Group',
@ToEmail = 'amember@hotmail.com',
@ReplyTo = 'sqlfaq@sqlserverfaq.com',
@Subject = 'Test email',
@Body = '<b>Hello Tony</b>',
@AttachmentFileName = 'c:\somefile.txt',
@SendStatus = @SendStatus OUTPUT
select @rc, @SendStatus
Here is the CLR code in VB.NET:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices
Imports System.Net.Mail
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Function SMTPFullEmail(ByVal SMTPServer As SqlString, _
ByVal SenderEmail As SqlString, _
ByVal FromEmail As SqlString, _
ByVal FromName As SqlString, _
ByVal ToEmail As SqlString, _
ByVal ReplyTo As SqlString, _
ByVal Subject As SqlString, _
<SqlFacet(MaxSize:=-1)> ByVal Body As SqlString, _
ByVal AttachmentFileName As SqlString, _
<Out()> ByRef SendStatus As SqlString) As SqlInt32
Dim Message As New MailMessage
' Set up the message
Message.Sender = New MailAddress(SenderEmail.ToString)
Message.From = New MailAddress(FromEmail.ToString, FromName.ToString)
Message.To.Add(ToEmail.ToString)
Message.Subject = Subject.ToString
Message.Body = Body.ToString
Message.IsBodyHtml = True
If AttachmentFileName.ToString <> "" Then Message.Attachments.Add(New Attachment(AttachmentFileName.ToString))
Dim smtp As New SmtpClient
' Send it
smtp.Host = SMTPServer.ToString
Try
smtp.Send(Message)
SendStatus = CType("OK", SqlString)
SMTPFullEmail = 0
Catch ex As Exception
SendStatus = CType(ex.Message, SqlString)
SMTPFullEmail = 1
End Try
End Function
End Class
[Updated 5th Feb 2008] Note the above Body etc.. are limited to 4000 bytes, to make it varchar(max) modify thus...
<SqlFacet(MaxSize:=-1)> ByVal Body As SqlString