Receiving  email through SQL server

Posted on 2013-12-23
Medium Priority
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 ?
Question by:Varshini S
  • 2
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
LVL 61

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1000 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:

LVL 13

Accepted Solution

Jesus Rodriguez earned 1000 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
            End If
  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
                   Catch ex as Exception
                   End Try
        End IF
  End Sub
End Class

Open in new window


Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 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