locked
Save EML to SQL Database RRS feed

  • Question

  • User-465525849 posted

    Hi all,

    Hoping some can assist and point me in the right direction.  I am trying to store a copy of the EML that is sent by my application into a SQL Database.  Below is the current code that I am using however not getting the result I need.

    How do I get Dim EmailSent_Body = New SqlParameter("@EmailSent_Body", SqlDbType.VarChar) With {.Value = ""} to collect the Saved EML.  Currently it is only being saved in a folder on the server.

    Thank you

    Protected Sub Wizard_FinishButtonClick(sender As Object, e As WizardNavigationEventArgs)
    
            Dim msg_Confirmation As System.Net.Mail.MailMessage = CreateMessage_Confirmation()
    
            Try
                Dim sc As New SmtpClient
                sc.Send(msg_Confirmation)
    
                'Saves a copy of the Submission email to the specificed directoy
                Dim SaveEmail As New SmtpClient("SpecifiedPickupDirectory")
                SaveEmail.DeliveryMethod = SmtpDeliveryMethod.SpecifiedPickupDirectory
                SaveEmail.PickupDirectoryLocation = "D:\sentemails"
                SaveEmail.Send(msg_Confirmation)
    
            Catch ex As Exception
                'Response.Write(ex.ToString())
            End Try
    
    End Sub
    
    
    Protected Function CreateMessage_Confirmation() As System.Net.Mail.MailMessage
    
            Dim lblDateTime As Label
            lblDateTime = CType(Master.FindControl("lblDateTime"), Label)
    
            Dim md As MailDefinition = New MailDefinition
            Dim strRecipient As String = txtEmailAddress.Text
            md.BodyFileName = "confirmation-email.html"
    
            md.IsBodyHtml = True
            md.From = "emailaddress@email.com"
            md.Subject = "email subject"
            md.Priority = Net.Mail.MailPriority.Normal
    
            Dim replacements As ListDictionary = New ListDictionary 'this is where we define all the text in the html file that gets replaced with values from our form controls
            replacements.Add("<%txtState%>", txtState.Text)
            replacements.Add("<%txtRequestedBy%>", txtRequestedBy.Text)
            replacements.Add("<%txtComments%>", txtComments.Text)
            replacements.Add("<%lblDateTime%>", lblDateTime.Text)
    
            Dim fileMsg As System.Net.Mail.MailMessage
            fileMsg = md.CreateMailMessage(strRecipient, replacements, Me)
    
            '*** SAVE EMAIL RECORD TO DATABASE ***
            Try
                Dim Connection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString)
                Using cmd = New SqlCommand("INSERT INTO Portal_EmailSent (EmailSent_DateTime, EmailSent_To, EmailSent_Subject, EmailSent_Priority, EmailSent_Body) VALUES(@EmailSent_DateTime, @EmailSent_To, @EmailSent_Subject, @EmailSent_Priority, @EmailSent_Body)", Connection)
                    Connection.Open()
                    Dim EmailSent_DateTime = New SqlParameter("@EmailSent_DateTime", SqlDbType.DateTime) With {.Value = DateTime.Now}
                    Dim EmailSent_To = New SqlParameter("@EmailSent_To", SqlDbType.VarChar) With {.Value = strRecipient}
                    Dim EmailSent_Subject = New SqlParameter("@EmailSent_Subject", SqlDbType.VarChar) With {.Value = md.Subject}
                    Dim EmailSent_Priority = New SqlParameter("@EmailSent_Priority", SqlDbType.VarChar) With {.Value = md.Priority}
                    Dim EmailSent_Body = New SqlParameter("@EmailSent_Body", SqlDbType.VarChar) With {.Value = ""}
    
                    cmd.Parameters.Add(EmailSent_DateTime)
                    cmd.Parameters.Add(EmailSent_To)
                    cmd.Parameters.Add(EmailSent_Subject)
                    cmd.Parameters.Add(EmailSent_Priority)
                    cmd.Parameters.Add(EmailSent_Body)
                    cmd.ExecuteNonQuery()
                End Using
    
            Catch ex As Exception
                'Response.Write(ex.ToString())
            End Try
    
            Return fileMsg
    
        End Function

    Sunday, May 27, 2018 7:26 AM

All replies