Arsip

Posts Tagged ‘Stored Procedure’

Send Email di SQL Server Express

28 Desember 2011 1 komentar

Kemarin habis meeting dikantor dan ada tugas baru dimana ada data yang harus dikirim lewat email. Sedangkan datanya harus dikirimkan berkala jika terjadi masalah dicabang. Akhirnya browsing2 dan ketemu di website MS SQL Tips.  Cara untuk melakukan kirim email melalui SQL Server Express dengan data hasil proses Stored Procedure.

Nah tanpa banyak cerita seperti ini caranya membuatnya…
1. Buat File SendEmail.vb dengan isi syntax seperti dibawah ini.

Imports System.Net
Imports System.Net.Mail

Public Class SQLCLREmail
  <Microsoft.SqlServer.Server.SqlProcedure()> _
  Public Shared Sub SendEmail(ByVal recipients As String, ByVal CC as String, ByVal BCC as String, ByVal subject As String, ByVal from As String, _
    ByVal body As String, ByVal strAttachments as String, ByVal strSMTPServer as String, ByVal strSMTPServerPort as String, _
    ByVal strSMTPServerUser as String, ByVal strSMTPServerPwd as String)

    Using MailMsg As New MailMessage()
      MailMsg.From = New MailAddress(from)
      MailMsg.Subject = subject
      MailMsg.Body = body
      MailMsg.IsBodyHtml = True

      If Not recipients.Equals(String.Empty) Then
        Dim strRecip As String
        Dim strTo() As String = recipients.Split(";")

        For Each strRecip In strTo
          MailMsg.To.Add(New MailAddress(strRecip))
        Next
      End If

      If Not CC.Equals(String.Empty) Then
        Dim strCCRecip As String
        Dim strCCTo() As String = CC.Split(";")

        For Each strCCRecip In strCCTo
          MailMsg.CC.Add(New MailAddress(strCCRecip))
        Next
      End If

      If Not BCC.Equals(String.Empty) Then
        Dim strBCCRecip As String
        Dim strBCCTo() As String = BCC.Split(";")

        For Each strBCCRecip In strBCCTo
          MailMsg.BCC.Add(New MailAddress(strBCCRecip))
        Next
      End If

      If Not strAttachments.Equals(String.Empty) Then
        Dim strFile As String
        Dim strAttach() As String = strAttachments.Split(";")

        For Each strFile In strAttach
          MailMsg.Attachments.Add(New Net.Mail.Attachment(strFile.Trim()))
        Next
      End If

      If Not strSMTPServer.Equals(String.Empty) Then
        Dim smtp As New System.Net.Mail.SmtpClient
        With smtp
          If Not strSMTPServerUser.Equals(String.Empty) Then
            .UseDefaultCredentials = False
            .Credentials = New System.Net.NetworkCredential(strSMTPServerUser, strSMTPServerPwd)
          End If
          .Host = strSMTPServer
          .Port = strSMTPServerPort
          .Send(MailMsg)
        End With
      Else
        Dim smtp As New System.Net.Mail.SmtpClient
        With smtp
          .Host = "localhost"
          .Port = 25
          .Send(MailMsg)
        End With
      End If
    End Using
  End Sub
End Class

Baca selanjutnya…

Iklan