Solved

Receiving  email through SQL server

Posted on 2013-12-23
3
250 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now