Get & send email with attachment from sql view address field

[donalexander]

Hello,
I have a simple email program from vb.net 2010 the program works successfully. Within the code I want to change part of a code - mail.To.Add("JohnDoe@hills.com") , which is hard coded and get vb to get the various email address from a sql server view (2005) called vlv_customers the view contains, a field call EmailAddrAddress, EmailAddress2, EmailAddress3 (there are multiply fields containing email address). I already have a DataSet call - 500_appDataSet1 containing the email address fields. How can I get vb.net to read from the view the various email address fields (some fields maybe Null - not having any address at all ) and send out the email with the attachment to single or multiple email address. I would really appreciate to get advice from someone who had this experience. I am not using ASP, just vb.net Form. Please take in consideration that I am new to VB.Net. Thank you in advance.

Private Sub btnSendEmail_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSendEmail.Click
'Check to see if subject text box or message text box have values
If Me.txtMessage.Text.Trim = "" Or Me.txtSubject.Text.Trim = "" Then
MsgBox("Subject or Message fields are required!", MsgBoxStyle.Exclamation, "Insufficient Data")
Return
End If
'create the mail message
Di mail As New MailMessage()
'set the addresses
mail.From = New MailAddress("azula@comm.com")
mail.To.Add("JohnDoe@hills.com")
'set subject text box and message text box content
mail.Subject = Me.txtSubject.Text.Trim
mail.Body = Me.txtMessage.Text.Trim
'add an attachment from the filesystem
mail.Attachments.Add(New Attachment("C:\Customers Statement and Invoices\Customers pdf Folder\33.pdf"))
'send the message
Dim smtp As New SmtpClient("local777")
smtp.Send(mail)

MsgBox("Copies of Statement & Invoices has been sent", MsgBoxStyle.Exclamation, "Send email")

End Sub

 

[IanRyder]

Hi,

You can iterate through the rows of a data table to get each individual row in the data source and then pass the information you need to your email code. Here is an example using the Northwind Customers table:-

Dim sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
    Dim myDataSet As New DataSet
    Dim daCustomers As New SqlDataAdapter("Select * From Customers", sqlConn)
    Dim tblCustomers As DataTable
 
    daCustomers.Fill(myDataSet, "Customers")
    tblCustomers = myDataSet.Tables(0)
 
    For Each custRow As DataRow In tblCustomers.Rows
      MsgBox(custRow.Item("YourEmailAddressFieldName"))
    Next

Cheers,

Ian