sending email to multiple recipient

[salt]

Hi...

I want to send an email to multiple recipients from my database in sql server. I'm using .vb to do it.. Any suggestions on how i can use a for loop to send my email? Thanks.

Dim cmdEmails As New SqlCommand("select student_email from student", conn)
        conn.Open()
      
      
        Dim ds As New DataTable
        ds = New DataTable

        Dim mailMessage As New MailMessage()
        mailMessage.From = New MailAddress("0609887e@student.tp.edu.sg")
        mailMessage.To.Add(dd_email.SelectedValue)


        mailMessage.Subject = txt_subj.Text
        mailMessage.Body = txt_content.Text
        'Dim msg As New MailMessage(dd_email.SelectedValue, txt_subj.Text, txt_content.Text)
        Try
            If fileUpload.PostedFile.FileName = "" Then
            Else
                mailMessage.Attachments.Add(New System.Net.Mail.Attachment(fileUpload.PostedFile.FileName))
            End If
            Dim smtpMail As New SmtpClient("localhost")
           smtpMail.Send(mailMessage)
            lbl_status.Visible = True
            lbl_status.Text = "Message sent."
        Catch ex As Exception
            lbl_status.Visible = True
            lbl_status.Text = "Error"
            
            conn.Close()
        End Try
    End Sub
End Class

[MickT]

You'll need to add AxMAPISession and AxMAPIMessages to your form.

The emails are sent via Outlook.

If you don't want to use that, I'm sure that parts of the code will provide your loop.

Private Sub MySyb()

' Email reports to specified recipients
strSubject = "My Subject"
strBody = "My Body"

' Collect the email addresses for each recipient
strSQL = "SELECT Name AS Addressee FROM Recipient ORDER BY Name"

strSub = strSubName + " - Filling recipient dataset"
objDSRecipient = FillDataSet(str Conn, strSQLRecipient )

' Send the emails

With objDSRecipient. Tables(0)
' Loop through the records and select reports for current recipient
If .Rows.Count > 0 Then
For X = 0 To .Rows.Count - 1
intRecipientID = CInt(.Rows(X).I tem("ID").ToStr ing)
ReDim strAddressArray (1)
strAddressArray (1) = .Rows(X).Item(" Addressee").ToS tring
Next X ' Recipient
NewEmail(strAdd ressArray, _
strBody, strSubject)
End If
End With
Catch objA As Exception
Cursor.Current = Cursors.Default
MessageBox.Show ("Error in sub '" & strSub & "' with " & objA.Source & _
vbCrLf & vbCrLf & objA.Message, "Error", _
MessageBoxButto ns.OK, MessageBoxIcon. Error)

Finally
Cursor = Cursors.Default
objDSRecipient = Nothing
End Try
End Sub


Public Function FillDataSet(ByV al strConn As String, _
ByVal strProc As String) As Data.DataSet
Dim objDS As New Data.DataSet
Dim objDA As SqlClient.SqlDa taAdapter
Try
strSubName = "FillDataSe t"

strSub = strSubName + " - Initialising variables"
Cursor.Current = Cursors.WaitCur sor 'vbHourglass
' Initialise the SqlDataAdapter with the stored procedure/SQL
' and connection string, and then use the SqlDataAdapter to fill
' the Dataset with data.
strSub = strSubName + " - Creating DataAdapter & Filling Dataset"
objDA = New SqlClient.SqlDa taAdapter(strPr oc, strConn)
objDA.Fill(objD S)
FillDataSet = objDS

Catch objA As Exception
MessageBox.Show ("Error in sub '" & strSub & "' with " & objA.Source & _
vbCrLf & vbCrLf & objA.Message, "Error", _
MessageBoxButto ns.OK, MessageBoxIcon. Error)
Finally
Cursor.Current = Cursors.Default ' vbDefault
objDS = Nothing
objDA = Nothing
End Try
End Function



Private Sub NewEmail(ByVal strAddress() As String, _
ByVal strAttachment() As String, _
Optional ByVal strBody As String = "", _
Optional ByVal strSubject As String = "", _
Optional ByVal strAttachName As String = "")

Dim X As Integer
Dim lngFile As Long
Dim strRecord As String

Try
strSubName = Me.GetType.ToSt ring + " - NewEmail"

strSub = strSubName + " - Preparing Email"
' Trigger default email client to send file
mapSession.Sign On()

With mapMessage
.SessionID = mapSession.Sess ionID
.MsgIndex = -1
.Compose()

strSub = strSubName + " - Adding Recipient(s)"
' Add address(s) of recipient(s)
For X = 1 To UBound(strAddre ss)
.RecipIndex = X - 1
' .RecipAddress = strAddress(X)
.RecipDisplayNa me = strAddress(X)
Next X
'Close()

' Add message subject
.MsgSubject = strSubject

strSub = strSubName + " - Dealing With Attachment(s)"
' Deal with attachment(s)
If blnAttachments Then

' Deal with message text - attachments
' Reserve space for attachments
strMsg = Space(UBound(st rAttachment)) & vbCrLf
If Len(strBody) > 0 Then
.MsgNoteText = strMsg & strBody
Else
.MsgNoteText = strMsg & ""
End If

If Len(strAttachme nt(1)) > 0 Then
For X = UBound(strAttac hment) To 1 Step -1
.AttachmentInde x = X - 1
.AttachmentPosi tion = X - 1
If Len(strAttachNa me) > 0 Then
.AttachmentName = strAttachName
End If
.AttachmentPath Name = strAttachment(0 ) & "\" & strAttachment(X )
'MsgBox(.Attach mentPathName)
' .AttachmentName = strAttachName
Next X
End If
Else

strSub = strSubName + " - Dealing With Message Text"
' Deal with message text - no attachments
If Len(strBody) > 0 Then
.MsgNoteText = strBody
End If
End If

strSub = strSubName + " - Sending Email"
.Send(False) ' False means that there is user interaction; with True there is
End With

Catch objA As Exception
'MsgBox(Err.Num ber)
If Err.Number = 32001 Then ' User Cancelled
Exit Try
ElseIf Err.Number = 32026 Then ' Cancelled email from Outlook security message
MsgBox("Email message to " + strAddress(1) + " cancelled by user")
Security.WriteU serAudit(20, strUser, strAddress(1))

Exit Try
ElseIf Err.Number = 32011 Then ' Attachment not found
MsgBox("Attachm ent not found for email message to " + strAddress(1), MsgBoxStyle.Inf ormation, "Attachment Not Found")
Exit Try
End If

MessageBox.Show ("Error in sub '" & strSub & "' with " & objA.Source & _
vbCrLf & vbCrLf & objA.Message, "Error", _
MessageBoxButto ns.OK, MessageBoxIcon. Error)

Finally

strSub = strSubName + " - Close Session"
mapSession.Sign Off()
End Try

End Sub

From MickT - 5th Sept 2008