Solved

Receiving  email through SQL server

Posted on 2013-12-23
3
259 Views
Last Modified: 2014-01-16
I am receiving a email every day from my  vendor with CSV file attachment.
The CSV file has a vendor products list.  Every day I import the CSV file in to the SQL sever table (vendor_Products)  manually.

Is it possible to automate the process using SQL job ?
0
Comment
Question by:Varshini S
  • 2
3 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 39736331
You can create an Outlook AddIn that when you receive an email from the vendor then check if the email has an attachment. If the attachment is a list then run the store procedure on the sql that import this into the list or import the list directly from the email attachment.

I  Will send you the example to you in a second
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 39736353
I agree with the above Expert.  I had a comment typed up, but got distracted; therefore, I will let you two continue.  However, it would be good to mention that SQL integration services could be a good resource for this.  PragmaticWorks has an adapter that may be useful:
http://pragmaticworks.com/Products/Task-Factory/Features/EmailSource.aspx

--Kevin
0
 
LVL 13

Accepted Solution

by:
Jesus Rodriguez earned 250 total points
ID: 39736390
This Will Be the code more or less. Has to be reviewed just in case.
Is and Outlook Addin in  VB.net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports System.Net
Imports System.ComponentModel

Public Class AddIn_CheckMail_from_Vendor
  Public BgWk As BackgroundWorker = New BackgroundWorker

      Private Sub AddIn_CheckMail_from_Vendor_Startup() Handles Me.Startup
        AddHandler BgWkDialer.RunWorkerCompleted, AddressOf BgwDialer_WorkCompleted
        AddHandler BgWkDialer.ProgressChanged, AddressOf BgwDialer_ProgressChanged
        AddHandler BgWkDialer.DoWork, AddressOf BgWDialer_Working
    End Sub

   Private Sub Application_NewMail() Handles Application.NewMail
        Dim tempApp As Outlook.Application
        Dim tempInbox As Outlook.MAPIFolder
        Dim InboxItems As Outlook.Items
        tempApp = CreateObject("Outlook.Application")
        tempInbox = tempApp.GetNamespace("Mapi").GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
        InboxItems = tempInbox.Items
        Dim newMail As Outlook.MailItem
        Dim inBox As Outlook.MAPIFolder = Me.Application.ActiveExplorer().Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
        Dim DestinationFolder As Outlook.MAPIFolder = inBox.Folders("Processed") 'Folder that you will move the email after imported
        For Each newMail In InboxItems
            Dim EmailDesc As String = newMail.Body
            If InStr(UCase(newMail.SenderEmailAddress), "yourvendor.com", CompareMethod.Text) > 0 Then 'Checking for contains the vendor in the email
                Procces_Email(newMail)
                newMail.UnRead=False 
                newMail.Move(DestinationFolder)
            End If
        Next
  End Sub   

   
  Private Sub Procces_Email(NewMail As Outlook.MailItem)
        Dim Attch As Outlook.Attachment
        Dim AttchCount As Integer = 0
        AttchCount = NewMail.Attachments.Count
        If AttchCount > 0 Then
         For I As Integer = 1 To AttchCount
                Attch = NewMail.Attachments.Item(I)
                'Don't forget to check here
                'If the attachment is vendor list and if is a vendor list 
                Dim WholeFile As System.IO.TextReader = System.IO.File.OpenText(Attch )
                Dim TextLines() As String = Split(WholeFile.ReadToEnd, vbCrLf)
                For Each LineOfVendor as String in TextLines
                   Dim Qry as string="INSERT INTO YOUR_VENDOR_TABLE(VENDOR_NAME) VALUES('" & Replace(LineOfVendor,"'","''") & "')"
                   DIM P AS NEW DATATABLE
                   DIM ADP as NEW SLQDATADAPTER(Qry,YOUR_CONEXTION_STRING)
                   P.CLEAR
                   Try 
                     ADP.Fill(P)
                   Catch ex as Exception
                   End Try
                Next
                WholeFile.Close()
                WholeFile.Dispose()
         Next 
        End IF
  End Sub
                      
End Class

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question