VB .Net : Buat Report, Export PDF, Kirim Melalui E-Mail

Imports System.Console

Imports System

Imports System.IO

Imports System.Data.SqlClient

Imports System.Data.Odbc

Imports System.Data.DataRowExtensions

Imports System.Data.DataTableExtensions

Imports System.Data.DataTableCollection

Imports System.Linq

Imports System.Xml

Imports System.Net.Mail







Module Module1

    Sub Main()


        Dim tabelPegawai As DataTable = New DataTable("pegawai5")

        Dim columnPegawai As DataColumn

        Dim rowPegawai As DataRow


        Dim koneksi As New OdbcConnection("DSN=latihan")


        'Membuat kolom idpegawai5

        columnPegawai = New DataColumn

        columnPegawai.DataType = System.Type.GetType("System.Int32")

        columnPegawai.ColumnName = "idpegawai5"

        columnPegawai.Unique = True

        tabelPegawai.Columns.Add(columnPegawai)




        'Membuat kolom idpegawai
        columnPegawai = New DataColumn

        columnPegawai.DataType = System.Type.GetType("System.String")

        columnPegawai.ColumnName = "idpegawai"

        tabelPegawai.Columns.Add(columnPegawai)




        ' Membuat kolom namapegawai

        columnPegawai = New DataColumn

        columnPegawai.DataType = System.Type.GetType("System.String")

        columnPegawai.ColumnName = "namapegawai"

        tabelPegawai.Columns.Add(columnPegawai)



        ' Membuat kolom Alamat

        columnPegawai = New DataColumn

        columnPegawai.DataType = System.Type.GetType("System.String")

        columnPegawai.ColumnName = "alamat"

        tabelPegawai.Columns.Add(columnPegawai)


        ' Membuat kolom alamatemail

        columnPegawai = New DataColumn

        columnPegawai.DataType = System.Type.GetType("System.String")

        columnPegawai.ColumnName = "alamatemail"

        tabelPegawai.Columns.Add(columnPegawai)



        tabelPegawai.PrimaryKey = New DataColumn() {tabelPegawai.Columns(0)}

        Dim dAdapter1 As OdbcDataAdapter = New OdbcDataAdapter("SELECT idpegawai5,idpegawai,namapegawai,alamat,alamatemail FROM pegawai5", koneksi)

        dAdapter1.Fill(tabelPegawai)

        tabelPegawai.AcceptChanges()





        ' Membuat tabel tabelBayangan

        Dim tabelBayangan As DataTable = New DataTable("tabelBayangan")

        Dim columnBayangan As DataColumn

        Dim rowBayangan As DataRow



        ' Membuat kolom idpegawai5

        columnBayangan = New DataColumn

        columnBayangan.DataType = System.Type.GetType("System.Int32")

        columnBayangan.ColumnName = "idpegawai5"

        columnBayangan.Unique = True

        tabelBayangan.Columns.Add(columnBayangan)



        ' Membuat kolom idpegawai

        columnBayangan = New DataColumn

        columnBayangan.DataType = System.Type.GetType("System.String")

        columnBayangan.ColumnName = "idpegawai"

        tabelBayangan.Columns.Add(columnBayangan)




        ' Membuat kolom namapegawai

        columnBayangan = New DataColumn

        columnBayangan.DataType = System.Type.GetType("System.String")

        columnBayangan.ColumnName = "namapegawai"

        tabelBayangan.Columns.Add(columnBayangan)



        ' Membuat kolom alamat

        columnBayangan = New DataColumn

        columnBayangan.DataType = System.Type.GetType("System.String")

        columnBayangan.ColumnName = "alamat"

        tabelBayangan.Columns.Add(columnBayangan)



        ' Membuat kolom alamatemail

        columnBayangan = New DataColumn

        columnBayangan.DataType = System.Type.GetType("System.String")

        columnBayangan.ColumnName = "alamatemail"

        tabelBayangan.Columns.Add(columnBayangan)


        ' Set Primary Key untuk tabelBayangan

        tabelBayangan.PrimaryKey = New DataColumn() {tabelBayangan.Columns(0)}

        ' Simpan perubahan ke tabel bayangan
        tabelBayangan.AcceptChanges()



        For i As Integer = 1 To 6

            Dim query = From pegawai5 In tabelPegawai.AsEnumerable() Where pegawai5("idpegawai5") = i Select pegawai5


            tabelBayangan = query.CopyToDataTable


            For Each rowBayangan In tabelBayangan.Rows

                Dim penulis As New System.IO.StringWriter

                Dim namaFile As String

                Dim alamatEmail As String

                Dim e_mail As New MailMessage()

                Dim smtp_server As New SmtpClient

                Dim attachment As System.Net.Mail.Attachment

                Dim alamatSuratElektronik As String


                Console.WriteLine("{0} | {1} | {2} | {3}", rowBayangan(1), rowBayangan(2), rowBayangan(3), rowBayangan(4))


                Console.WriteLine()

                Console.WriteLine()

                tabelBayangan.TableName = "tabelBayangan"

                namaFile = rowBayangan(1)

                alamatEmail = rowBayangan(4)

                tabelBayangan.WriteXml(penulis, XmlWriteMode.WriteSchema, True)

                Console.WriteLine(penulis)

                tabelBayangan.WriteXml("D:\StevenNathaniel\Proyek VB NET\AplikasiConsoleMySQL23\AplikasiConsoleMySQL23\AplikasiConsoleMySQL23\bin\Debug\data.xml", XmlWriteMode.WriteSchema, True)


                ' Buat export data di file XML menjadi file PDF

                Dim laporan As New XtraReport1

                laporan.ExportToPdf("D:\StevenNathaniel\Proyek VB NET\AplikasiConsoleMySQL23\AplikasiConsoleMySQL23\AplikasiConsoleMySQL23\bin\Debug\" & namaFile & ".pdf")


                ' Mengirimkan file PDF melalui email ke alamat email yg sudah tersimpan di tabel MySQL

                smtp_server.UseDefaultCredentials = False

                smtp_server.Credentials = New Net.NetworkCredential("[email protected]", "akusenangkamusenang")

                smtp_server.Port = 587

                smtp_server.EnableSsl = True

                smtp_server.Host = "smtp.gmail.com"

                attachment = New System.Net.Mail.Attachment("D:\StevenNathaniel\Proyek VB NET\AplikasiConsoleMySQL23\AplikasiConsoleMySQL23\AplikasiConsoleMySQL23\bin\Debug\" & namaFile & ".pdf")


                e_mail = New MailMessage()


                e_mail.From = New MailAddress("[email protected]")

                alamatSuratElektronik = alamatEmail

                e_mail.To.Add(alamatSuratElektronik)

                e_mail.Subject = "Test Kirim Banyak E-Mail"

                e_mail.IsBodyHtml = False

                e_mail.Body = "Data Pegawai Terlampir"

                e_mail.Attachments.Add(attachment)

                smtp_server.Send(e_mail)

                Console.WriteLine("E-Mail Telah Terkirim")






                Console.WriteLine()

                Console.WriteLine()





                Console.ReadKey()



            Next rowBayangan



        Next i





    End Sub

End Module