משלוח מיילים דרך Gmail באמצעות פרוצדורת CLR

Imports System.Net

Imports System.Net.Mail

Public Class StoredProcedure

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub P_EMail(ByVal Mail_To As String, _

                            ByVal Mail_Cc As String, _

                            ByVal Mail_Bcc As String, _

                            ByVal Subject As String, _

                            ByVal From As String, _

                            ByVal Body As String, _

                            ByVal Attach As String)

        Dim Smtp_Client As SmtpClient

        Dim Str As String

        Dim Nmbr As Integer

        Using EMail As New MailMessage()

            EMail.Subject = Subject

            EMail.SubjectEncoding = System.Text.Encoding.Unicode

            EMail.Body = Body

            EMail.BodyEncoding = System.Text.Encoding.Unicode

            Attach = Attach.Trim

            While Attach <> ""

                Nmbr = InStr(Attach, ";")

                If Nmbr = 0 Then

                    Str = Attach

                    Attach = ""

                Else

                    Str = Attach.Substring(0, Nmbr - 1)

                    Attach = Attach.Substring(Nmbr, Attach.Length - Nmbr)

                    Attach = Attach.Trim

                End If

                EMail.Attachments.Add(New Attachment(Str))

            End While

            Mail_To = Mail_To.Trim

            While Mail_To <> ""

                Nmbr = InStr(Mail_To, ";")

                If Nmbr = 0 Then

                    Str = Mail_To

                    Mail_To = ""

                Else

                    Str = Mail_To.Substring(0, Nmbr - 1)

                    Mail_To = Mail_To.Substring(Nmbr, Mail_To.Length - Nmbr)

                    Mail_To = Mail_To.Trim

                End If

                EMail.To.Add(New MailAddress(Str))

            End While

            Mail_Cc = Mail_Cc.Trim

            While Mail_Cc <> ""

                Nmbr = InStr(Mail_Cc, ";")

                If Nmbr = 0 Then

                    Str = Mail_Cc

                    Mail_Cc = ""

                Else

                    Str = Mail_Cc.Substring(0, Nmbr - 1)

                    Mail_Cc = Mail_Cc.Substring(Nmbr, Mail_Cc.Length - Nmbr)

                    Mail_Cc = Mail_Cc.Trim

                End If

                EMail.CC.Add(New MailAddress(Str))

            End While

            Mail_Bcc = Mail_Bcc.Trim

            While Mail_Bcc <> ""

                Nmbr = InStr(Mail_Bcc, ";")

                If Nmbr = 0 Then

                    Str = Mail_Bcc

                    Mail_Bcc = ""

                Else

                    Str = Mail_Bcc.Substring(0, Nmbr - 1)

                    Mail_Bcc = Mail_Bcc.Substring(Nmbr, Mail_Bcc.Length - Nmbr)

                    Mail_Bcc = Mail_Bcc.Trim

                End If

                EMail.Bcc.Add(New MailAddress(Str))

            End While

            EMail.From = New MailAddress(From)

            EMail.IsBodyHtml = True

            Smtp_Client = New SmtpClient("smtp.gmail.com", 587)

            Smtp_Client.EnableSsl = True

            Smtp_Client.Credentials = New NetworkCredential("MyMail@gmail.com", "MyPwd")

            Smtp_Client.Send(EMail)

        End Using

    End Sub

End Class

Use msdb 

Go 

Alter Database msdb Set TrustWorthy On

Go

If (Select Object_Id('P_EMail')) Is Not Null Drop Procedure dbo.P_EMail

Go

If (Select name From sys.assemblies Where name='EMail') Is Not Null Drop Assembly Email

Go

Create Assembly Email From 'C:\Tmp\Email.dll'   

With Permission_Set = UnSafe   

Go  

Create Procedure dbo.P_EMail 

                @Mail_To NVarChar(Max), 

                @Mail_Cc NVarChar(Max), 

                @Mail_Bcc NVarChar(Max), 

                @Subject NVarChar(Max), 

                @From NVarChar(Max), 

                @Body NVarChar(Max), 

                @Attachment NVarChar(Max)

With Execute As Caller 

As 

External Name Email.StoredProcedure.P_EMail

Go

Sp_Configure 'Clr Enabled', 1

Go

ReConfigure

Go

Execute msdb..P_EMail @Mail_To = 'MyFriend1@HisMail1.com',

                @Mail_Cc = 'MyFriend2@HisMail2.com',

                @Mail_Bcc = 'MyFriend3@HisMail3.com',

                @Subject = 'Hello friends',

                @From = 'MySelf@MyMail.com',

                @Body = N'מה שלומכם? זו התמונה שלי..',

                @Attachment = 'C:\Tmp\MyPicture.jpg';

Execute msdb..P_EMail @Mail_To = 'MyFriend1@HisMail1.com;MyFriend2@HisMail2.com',

                @Mail_Cc = '',

                @Mail_Bcc ='',

                @Subject = 'Hello friends',

                @From = 'MySelf@MyMail.com',

                @Body = N'מה שלומכם? הנה כמה תמונות שלי..',

                @Attachment = 'C:\Tmp\Pic1.jpg;C:\Tmp\Pic2.jpg;C:\Tmp\Pic3.jpg';

Execute msdb..P_EMail @Mail_To = 'MyFriend1@HisMail1.com',

                @Mail_Cc = '',

                @Mail_Bcc ='',

                @Subject = 'Hello friends',

                @From = 'MySelf@MyMail.com',

                @Body = N'מה שלומכם? <B>אני מרגיש טוב!</B.',

                @Attachment = '';