How to add database content in email body

Question

0.00/5 (No votes)

See more:

Hi all...
I am doing invoice project in vb.net, i have a problem in invoice due details sending time.
I want display the details in email body, i don't know how to do this, the following details is my database and the sample email body structure .

This is my database

________________________________
Client name     Balance amount
________________________________
xxxxxx            1000
zzzzzz            2500
tttttt            5244
________________________________

This details ditto display in email body like below

Client name     Balance amount

xxxxxx            1000
zzzzzz            2500
tttttt            5244

i try to many way but no use my vb.net code is below

VB

Dim a As String
       a = "xxxxxx@gmail.com"
       Dim mail As New MailMessage()
       Dim SmtpServer As New SmtpClient("smtp.gmail.com")
       mail.From = New MailAddress("xxxxxx@gmail.com")
       mail.[To].Add(a)
       mail.Subject = "Test"

       'Body Content
       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       cmd = New OleDbCommand("select ClientName,BalanceAmount from receipt ", conn)
       adp1 = New OleDbDataAdapter(cmd)
       ds2 = New DataSet
       adp1.Fill(ds2, "receipt")
       mail.Body = (ds2.Tables(0).Rows(0).Item(0))
       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       mail.IsBodyHtml = True
       SmtpServer.Port = 587
       SmtpServer.Credentials = New      System.Net.NetworkCredential("xxxxxxx@gmail.com", "••••••••••••••••")
       SmtpServer.EnableSsl = True
       SmtpServer.Send(mail)
       MsgBox("send ")

i got the mail without the database details.
so please send your ideas how to solve this

Posted 19-Sep-13 8:27am

sv sathish

Updated 19-Sep-13 18:53pm

v3

Add a Solution

3 solutions

Solution 1

This is the class i use to send email:

VB

Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Net.Mail
 
Public Class EmailRelay
    Public smtpServer As String
    Public userName As String
    Public password As String
 
    Public Sub new(ByVal _smtpServer As String, ByVal _userName As String, ByVal _password As String)
        smtpServer  = _smtpServer
        userName    = _userName
        password    = _password
    End Sub
 
    Public Shared Function ServerCertificateValidationCallback(ByVal sender As Object, _
                                                               ByVal cert As System.Security.Cryptography.X509Certificates.X509Certificate, _
                                                               ByVal chain As System.Security.Cryptography.X509Certificates.X509Chain, _
                                                               ByVal sslPolicyErrors As Net.Security.SslPolicyErrors) As Boolean
        Return sslPolicyErrors = Net.Security.SslPolicyErrors.None
    End Function
 
    Public Function SendMail(ByVal fromAddress As String, ByVal toAddress as String, 
                                ByVal subject As String, ByVal body As String, 
                                Optional ByVal fromEmailName As String = "",
                                Optional Byval useSSL As Boolean = True, 
                                Optional ByRef errMsg As String = "") As Boolean
 
        Dim mm As New MailMessage()
	Dim smtp As New SmtpClient()
        Dim emailFrom as New MailAddress(fromAddress, fromEmailName) 
        Dim NetworkCred As New System.Net.NetworkCredential()
 
        Try
            NetworkCred.UserName = userName  
            NetworkCred.Password = password  
 
            With mm
                .[To].Add(New MailAddress(toAddress))
                .From                   = emailFrom
                .Subject                = subject
                .Body                   = body
                .IsBodyHtml             = True
            End With
 
            With smtp
                .Host                   = smtpServer 
                .EnableSsl              = useSSL 
                .UseDefaultCredentials  = True
                .Credentials            = NetworkCred
                .Port                   = 587
                .Send(mm)
            End With
 
        Catch ex As Exception
            errMsg = ex.Message
            Return False
        End Try
        
        Return True
        
    End Function
 
End Class

Notice the .IsBodyHtml line? Simply use this class and format your results in HTML.

Use the class like this:

VB

Dim errMsg as String
Dim email as new EmailRelay("smtp.gmail.com", "you@gmail.com", "YourEmailPassword")

If NOT email.SendMail("you@gmail.com", "targetEmailAddress@wherever.com", _
   "Subject Line", _
   "<HTML>This is where your html formatted body content will go</HTML>", _
   "Your name here", True, errMsg) Then

   MsgBox("There was a problem sending your email. The error returned is: " & errMsg)

End If

You may need to set the "useSSL" parameter to false, depending on how your smtp server is configured.

- Pete

Posted 19-Sep-13 8:45am

pdoxtader

Updated 20-Sep-13 2:53am

v7

Comments

sv sathish 20-Sep-13 0:51am

where i put the body content from database in this code?????

pdoxtader 20-Sep-13 8:33am

I updated the solution to show usage. Please have a look.

- Pete

Solution 2

Hi,
please change your code:

VB

 ...
 adp1.Fill(ds2, "receipt")
       
       Dim sb As New System.Text.StringBuilder
' add header
       For Each col As DataColumn In ds2.Tables(0).Columns
            sb.Append(col.ColumnName.ToString.PadRight(30))
        Next
        sb.AppendLine()
' table content
        For Each row As DataRow In ds2.Tables(0).Rows
            sb.Append(row.Item(0).ToString.PadRight(30))
            sb.AppendLine(row.Item(1).ToString.PadLeft(30))
        Next
     mail.Body= sb.Tostring
...

Posted 19-Sep-13 22:28pm

Raimis9

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sv sathish · Accepted Answer · 2013-10-01T23:06:00

Thank you for your answers!
i solve this problem,my code is below.

Private Sub sendmail()

Try
Dim eaddress, ccmail, passw As String
Dim frommailidds As DataSet
cmd = New OleDbCommand("select EmailAddress,CCMailAddress,Psw from company Where id=1", conn)
adp = New OleDbDataAdapter(cmd)
frommailidds = New DataSet
adp.Fill(frommailidds, "company")

eaddress = (frommailidds.Tables(0).Rows(0).Item(0))
ccmail = (frommailidds.Tables(0).Rows(0).Item(1))
passw = (frommailidds.Tables(0).Rows(0).Item(2))


Dim mail As New MailMessage()
Dim SmtpServer As New SmtpClient("smtp.gmail.com")
mail.From = New MailAddress(eaddress)
mail.[To].Add(ccmail)
mail.Subject = "Total Invoice Due Details"


Dim qry As String
Dim dsbody As New DataSet
Dim adpbody As OleDbDataAdapter
qry = "select Clientid,ClientName,(SUM(TotalAmount)-Sum(Amountpaid)) as BalanceAmount  from receipt GROUP BY Clientid,Clientname "
cmd = New OleDbCommand(qry, conn)
dsbody.Clear()
adpbody = New OleDbDataAdapter(cmd)
adpbody.Fill(dsbody, "receipt")
Dim dts As New DataTable
Dim dv As New DataView(dsbody.Tables(0))
dv.RowFilter = "Balanceamount <>0"
dts = dv.ToTable()


Dim str(dts.Rows.Count - 1) As String
Dim xc As Integer
Dim sum As Decimal
sum = 0
xc = 0

For Each dr As DataRow In dts.Rows
str(xc) = "<tr><td bgcolore=#B2B2CC>" + dr(1).ToString() + "</td><td bgcolore='#B2B2CC' align='center'>" + dr(2).ToString() + "</td></tr>"
sum = sum + Decimal.Parse(dr(2).ToString)
xc = xc + 1
Next

mail.Body = " <html><head><title>Payment details</title></head><table border=1><tr bgcolor=#6699FF ><th bgcolor=#6699FF>Client Name</th><th>Balance Amount</th></tr>" + String.Concat(str) + "<tr bgcolore='#FF0000'><font color='red'><b> Total Balance Amount </b></font> <td bgcolore=#FF0000 align='center'><font color='red'><b>" + sum.ToString + "</b></font></td></tr></html> "

mail.IsBodyHtml = True
SmtpServer.Port = 587
SmtpServer.Credentials = New System.Net.NetworkCredential(eaddress, passw)
SmtpServer.EnableSsl = True
SmtpServer.SendMailAsync(mail)
MessageBox.Show("Total Invoice Due Details Mail Sent")
Catch ex As Exception
MsgBox(ex.ToString)
Finally
conn.Close()
End Try
End Sub