Send Email with New inserted Records Details.

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 609 Views - Last Post: 14 February 2020 - 04:18 PM Rate Topic: -----

#1 Naveed Akhter   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 28-September 08

Send Email with New inserted Records Details.

Posted 13 February 2020 - 12:09 PM

Hi Good Evening Everyone!
I am preparing one application facing some challenges, the scenrio is to send email when a user enter a complaint in the system to rhe assigned user the assigned user have email address already in the system the emal notification should go to assigned user alongwith Complaint ID and complaint detail. then if after 30 minutes no one updated the status the system should send another notification to next level after 1 hour if still status not updated the system should send email notification to final level. could you please help me with code sample if possible. i am able to send notification using below code but unable to figure out how to send complaint details.

Sub email_send() 
On Error Resume Next 
Dim mail As New MailMessage()
Dim SmtpServer As New SmtpClient SmtpServer.Credentials = New Net.NetworkCredential("[email protected]", "mypassword") 
SmtpServer.Port = 587 
SmtpServer.Host = "smtp.gmail.com" 
SmtpServer.EnableSsl = True 
mail.To.Add("[email protected]") 
mail.From = New MailAddress("[email protected]") 
mail.Subject = "Subject" 
mail.Body = "Body" 
SmtpServer.Send(mail) 
End Sub 


Thanks in Advance for youe great help always

Is This A Good Question/Topic? 0
  • +

Replies To: Send Email with New inserted Records Details.

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15497
  • View blog
  • Posts: 62,056
  • Joined: 12-June 08

Re: Send Email with New inserted Records Details.

Posted 13 February 2020 - 12:47 PM

You would add your text in the line 11, body, part.
Was This Post Helpful? 0
  • +
  • -

#3 Naveed Akhter   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 28-September 08

Re: Send Email with New inserted Records Details.

Posted 13 February 2020 - 01:00 PM

View Postmodi123_1, on 13 February 2020 - 12:47 PM, said:

You would add your text in the line 11, body, part.


Hi Thanks for reply yes I know this method is working instead of manual text against big field I want to put the currently inserted record in the db as I am calling this function right after insertion operation.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15497
  • View blog
  • Posts: 62,056
  • Joined: 12-June 08

Re: Send Email with New inserted Records Details.

Posted 13 February 2020 - 01:16 PM

You are not clear what the issue is then.

If you know where to put the text, and you know what you are inserting into the db, then what?
Was This Post Helpful? 0
  • +
  • -

#5 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3069
  • View blog
  • Posts: 11,774
  • Joined: 03-December 12

Re: Send Email with New inserted Records Details.

Posted 13 February 2020 - 01:28 PM

You are also going to need either a scheduled task or a service that is actively monitoring if you will require automatic escalation based on response vs time.
Was This Post Helpful? 0
  • +
  • -

#6 Naveed Akhter   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 28-September 08

Re: Send Email with New inserted Records Details.

Posted 13 February 2020 - 10:10 PM

View Postmodi123_1, on 13 February 2020 - 01:16 PM, said:

You are not clear what the issue is then.

If you know where to put the text, and you know what you are inserting into the db, then what?


I am clear about it, I am sorry may be I am not able to explain well let me try one more time.

In the code provided above on line 11 I will put text please note that a new record is inserted in the database and after I want to show record Id and detail which is in the database and I need to put it in the email body before the system will send email.
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15497
  • View blog
  • Posts: 62,056
  • Joined: 12-June 08

Re: Send Email with New inserted Records Details.

Posted 13 February 2020 - 10:20 PM

Okay.. so use SCOPE_IDENTITY to get the last ID inserted for that scope/session, and tack it into the body text.

https://docs.microso...ql-server-ver15
Was This Post Helpful? 0
  • +
  • -

#8 Naveed Akhter   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 28-September 08

Re: Send Email with New inserted Records Details.

Posted 14 February 2020 - 02:02 AM

View Postmodi123_1, on 13 February 2020 - 10:20 PM, said:

Okay.. so use SCOPE_IDENTITY to get the last ID inserted for that scope/session, and tack it into the body text.

https://docs.microso...ql-server-ver15



     con.ConnectionString = ConnectionString
            Try
                dsOle.Clear()
                dtOle.Clear()
                cmdOle = con.CreateCommand
                da.SelectCommand = cmdOle
                Dim eaddress As String
                cmdOle = New OracleCommand("SELECT EMAIL_ADDRESS FROM USERS WHERE USERNAME = '" & ComboBox2.Text & "'", con)
                da.SelectCommand = cmdOle
                da.Fill(dsOle, "USERS")
                eaddress = (dsOle.Tables(0).Rows(0).Item(0))
                Dim mail As New MailMessage()
                Dim SmtpServer As New SmtpClient("smtp.office365.com")
                mail.From = New MailAddress("[email protected]")
                mail.[To].Add(eaddress)
                mail.Subject = "Complaint Details"
    
                Dim qry As String
                Dim dsbody As New DataSet
                qry = "select comp_type,LOCATION,COMPLAINT_DETAIL,REMARKS from complaint"
                cmdOle1 = New OracleCommand(qry, con)
                dsbody.Clear()
                da1 = New OracleDataAdapter(cmdOle1)
                da1.Fill(dsbody, "Complaint")
                Dim dts As New DataTable
                Dim dv As New DataView(dsbody.Tables(0))
                dts = dv.ToTable()
    
                mail.Body = " <html><head><title>Complaint details</title></head><table border=1><tr bgcolor=#6699FF ><th <th bgcolor=#6699FF>Complaint Location><th bgcolor=#6699FF>Complaint Type<th bgcolor=#6699FF><th bgcolor=#6699FF>Complaint Remarks</th></tr></html>"
                mail.IsBodyHtml = True
                SmtpServer.Port = 587
                SmtpServer.Credentials = New Net.NetworkCredential("[email protected]", "PWD")
                SmtpServer.EnableSsl = True
                SmtpServer.Send(mail)
                MessageBox.Show("The Complaint Sent to Respective department")
            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                con.Close()
            End Try




I figure out a way how to select specific email for the assigned complaint handler and able to send email also but data is not coming only headings actually i need to send the currently inserted record as notification to the user.

Could you please help me out to adjust this code.

Thanks in advance.

View PostNaveed Akhter, on 14 February 2020 - 02:00 AM, said:

View Postmodi123_1, on 13 February 2020 - 10:20 PM, said:

Okay.. so use SCOPE_IDENTITY to get the last ID inserted for that scope/session, and tack it into the body text.

https://docs.microso...ql-server-ver15



     con.ConnectionString = ConnectionString
            Try
                dsOle.Clear()
                dtOle.Clear()
                cmdOle = con.CreateCommand
                da.SelectCommand = cmdOle
                Dim eaddress As String
                cmdOle = New OracleCommand("SELECT EMAIL_ADDRESS FROM USERS WHERE USERNAME = '" & ComboBox2.Text & "'", con)
                da.SelectCommand = cmdOle
                da.Fill(dsOle, "USERS")
                eaddress = (dsOle.Tables(0).Rows(0).Item(0))
                Dim mail As New MailMessage()
                Dim SmtpServer As New SmtpClient("smtp.office365.com")
                mail.From = New MailAddress("[email protected]")
                mail.[To].Add(eaddress)
                mail.Subject = "Complaint Details"
    
                Dim qry As String
                Dim dsbody As New DataSet
                qry = "select comp_type,LOCATION,COMPLAINT_DETAIL,REMARKS from complaint"
                cmdOle1 = New OracleCommand(qry, con)
                dsbody.Clear()
                da1 = New OracleDataAdapter(cmdOle1)
                da1.Fill(dsbody, "Complaint")
                Dim dts As New DataTable
                Dim dv As New DataView(dsbody.Tables(0))
                dts = dv.ToTable()
    
                mail.Body = " <html><head><title>Complaint details</title></head><table border=1><tr bgcolor=#6699FF ><th <th bgcolor=#6699FF>Complaint Location><th bgcolor=#6699FF>Complaint Type<th bgcolor=#6699FF><th bgcolor=#6699FF>Complaint Remarks</th></tr></html>"
                mail.IsBodyHtml = True
                SmtpServer.Port = 587
                SmtpServer.Credentials = New Net.NetworkCredential("[email protected]", "PWD")
                SmtpServer.EnableSsl = True
                SmtpServer.Send(mail)
                MessageBox.Show("The Complaint Sent to Respective department")
            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                con.Close()
            End Try




I figure out a way how to select specific email for the assigned complaint handler and able to send email also but data is not coming only headings actually i need to send the currently inserted record as notification to the user.

Could you please help me out to adjust this code.

Thanks in advance.


Also the database is Oracle
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15497
  • View blog
  • Posts: 62,056
  • Joined: 12-June 08

Re: Send Email with New inserted Records Details.

Posted 14 February 2020 - 09:08 AM

Quote

but data is not coming only headings actually

This doesn't make sense.


No where in the 'mail.body' assignment do you actually add data you pulled from the database. Seems you should look into that.
29
           mail.Body = " <html><head><title>Complaint details</title></head><table border=1><tr bgcolor=#6699FF ><th <th bgcolor=#6699FF>Complaint Location><th bgcolor=#6699FF>Complaint Type<th bgcolor=#6699FF><th bgcolor=#6699FF>Complaint Remarks</th></tr></html>"

Was This Post Helpful? 0
  • +
  • -

#10 Naveed Akhter   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 28-September 08

Re: Send Email with New inserted Records Details.

Posted 14 February 2020 - 11:04 AM

View Postmodi123_1, on 14 February 2020 - 09:08 AM, said:

Quote

but data is not coming only headings actually

This doesn't make sense.


No where in the 'mail.body' assignment do you actually add data you pulled from the database. Seems you should look into that.
29
           mail.Body = " <html><head><title>Complaint details</title></head><table border=1><tr bgcolor=#6699FF ><th <th bgcolor=#6699FF>Complaint Location><th bgcolor=#6699FF>Complaint Type<th bgcolor=#6699FF><th bgcolor=#6699FF>Complaint Remarks</th></tr></html>"


Yes you are right I am working on the same mentioned above code the mentioned column names are heading only want to pull out the data against these headings struggling with this. Trying to figure out a way if you can review my code above and advise what should I put there to pull the data from db into these columns.
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15497
  • View blog
  • Posts: 62,056
  • Joined: 12-June 08

Re: Send Email with New inserted Records Details.

Posted 14 February 2020 - 11:05 AM

You look to have data adapters filling data sets and data tables. Why are you not using those?

Ex: https://docs.microso...etframework-4.8
Was This Post Helpful? 0
  • +
  • -

#12 Naveed Akhter   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 28-September 08

Re: Send Email with New inserted Records Details.

Posted 14 February 2020 - 11:18 AM

View Postmodi123_1, on 14 February 2020 - 11:05 AM, said:

You look to have data adapters filling data sets and data tables. Why are you not using those?

Ex: https://docs.microso...etframework-4.8


Sorry my mistake I am using them I didn't posted the updated code, I will post it back.


Thanks.
Was This Post Helpful? 0
  • +
  • -

#13 Naveed Akhter   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 28-September 08

Re: Send Email with New inserted Records Details.

Posted 14 February 2020 - 11:27 AM

View PostNaveed Akhter, on 14 February 2020 - 11:18 AM, said:

View Postmodi123_1, on 14 February 2020 - 11:05 AM, said:

You look to have data adapters filling data sets and data tables. Why are you not using those?

Ex: https://docs.microso...etframework-4.8


Sorry my mistake I am using them I didn't posted the updated code, I will post it back.


Thanks.


Private Sub email_send()
        con.ConnectionString = ConnectionString
        Try
            dsOle.Clear()
            dtOle.Clear()
            cmdOle = con.CreateCommand
            da.SelectCommand = cmdOle
            Dim eaddress As String
            cmdOle = New OracleCommand("SELECT EMAIL_ADDRESS FROM USERS WHERE USERNAME = '" & ComboBox2.Text & "'", con)
            da.SelectCommand = cmdOle
            da.Fill(dsOle, "USERS")
            eaddress = (dsOle.Tables(0).Rows(0).Item(0))
            Dim mail As New MailMessage()
            Dim SmtpServer As New SmtpClient("smtp.office365.com")
            mail.From = New MailAddress("[email protected]")
            mail.[To].Add(eaddress)
            mail.Subject = "Complaint Details"

            Dim qry As String
            Dim dsbody As New DataSet
            qry = "select comp_type,LOCATION,COMPLAINT_DETAIL from complaint"
            cmdOle1 = New OracleCommand(qry, con)
            dsbody.Clear()
            da1 = New OracleDataAdapter(cmdOle1)
            da1.Fill(dsbody, "Complaint")
            Dim dts As New DataTable
            Dim dv As New DataView(dsbody.Tables(0))
            dts = dv.ToTable()

            For Each Row As DataRow In dtOle1.Rows
                Dim comp_id As String = Row("COMP_ID").ToString
                Dim comp_type As String = Row("COMP_type").ToString
                Dim comp_type As String = Row("COMP_Detail").ToString
            Next

            mail.Body = String.Format(" <html><head><title>Complaint details</title></head><table border=1><tr bgcolor=#6699FF ><th <th bgcolor=#6699FF>Complaint Location><th bgcolor=#6699FF>Complaint Type<th bgcolor=#6699FF><th bgcolor=#6699FF>Complaint Remarks</th></tr></html>")
            mail.IsBodyHtml = True
            SmtpServer.Port = 587
            SmtpServer.Credentials = New Net.NetworkCredential("[email protected]", "PWD")
            SmtpServer.EnableSsl = True
            SmtpServer.Send(mail)
            MessageBox.Show("The Complaint Sent to Respective department")
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            con.Close()
        End Try


Was This Post Helpful? 0
  • +
  • -

#14 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15497
  • View blog
  • Posts: 62,056
  • Joined: 12-June 08

Re: Send Email with New inserted Records Details.

Posted 14 February 2020 - 11:31 AM

Why are you not appending that data to your mail.body?!

30
            For Each Row As DataRow In dtOle1.Rows
31
                Dim comp_id As String = Row("COMP_ID").ToString
32
                Dim comp_type As String = Row("COMP_type").ToString
33
                Dim comp_type As String = Row("COMP_Detail").ToString
34
            Next

Was This Post Helpful? 0
  • +
  • -

#15 Naveed Akhter   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 28-September 08

Re: Send Email with New inserted Records Details.

Posted 14 February 2020 - 11:41 AM

View Postmodi123_1, on 14 February 2020 - 11:31 AM, said:

Why are you not appending that data to your mail.body?!

30
            For Each Row As DataRow In dtOle1.Rows
31
                Dim comp_id As String = Row("COMP_ID").ToString
32
                Dim comp_type As String = Row("COMP_type").ToString
33
                Dim comp_type As String = Row("COMP_Detail").ToString
34
            Next



I did it by declaring them as string , if there is any mistake kindly modify the mail.body line for guidance so that I can check if it works for me.

Thanks.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2