Post reply

using email as a dts data source

  • December 9, 2005 at 8:55 am

    #66853

    hi,

    this is a sketchy question so... all answers appreciated...

    We have this client who sends us some records in a plain text email.

    I'm wondering if I could make a dts package that would take each email out of an exchange mailbox and process it.. ie insert a record in a table with the bits of data in the email....

    Its like, this guy runs our help desk, and gets support requests from customers.. and we'd like to automatically insert these support request in our bug tracking database... he's not interested in modifying his software or using any of ours.. just wants to keep firing emails at us...

    So.. I dunno.. probably not possible but thought I'd ask...

    Thanks

    Vida

  • December 9, 2005 at 9:48 am

    #608728

    It is possible to do.

    Open up the mailbox. Select [Export to a file]

    Select something like [Microsoft excel].

    Select the folder you want to export

    Give the filename and location. You can map files or take the defaults.

    Hit next to complete.

    You are left with an excel file with standard columns. Just create a dts package to import the file.

    You should be able to automate the process if you get hold of the outlook object model. Whether or not you can do this with vb script in an activex task, I doubt. I would take the root of writing the code in the excel document that recieves the data. That way you always point the dts to the same excel file to import.

    If I had the time I would help with the code. Try out some vb sites and I'm sure someone has written something out there.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • December 9, 2005 at 9:57 am

    #608737

    take a look at this:

     

    http://www.informit.com/guides/content.asp?g=sqlserver&SeqNum=31&rl=1

  • December 9, 2005 at 10:10 am

    #608747

    Oh wow guys ! This is great!

    I'll go hoover up all I can find about SQL Mail so...

    Thanks very much!

    Vida

  • December 13, 2005 at 8:29 am

    #609224

    For anyone interested the code to get outlook details is as follows:

    Function fnGetMailDetails()

    Dim olApp As New Outlook.Application

    Dim objName As Outlook.NameSpace

    Dim folder As Outlook.MAPIFolder

    Dim objMailItem As Outlook.MailItem

    Set olApp = Outlook.Application

    Set objName = olApp.GetNamespace("MAPI")

    Set folder = objName.GetDefaultFolder(olFolderInbox)

    For x = 1 To folder.Items.Count

    Set objMailItem = folder.Items.Item(x)

    Debug.Print objMailItem.Body & "," & objMailItem.Subject & "," & objMailItem.SenderName _

    & "," & objMailItem.ReceivedTime

    Next x

    Set olApp = Nothing

    Set objName = Nothing

    Set folder = Nothing

    Set objMailItem = Nothing

    End Function

    ------------------------------
    The Users are always right - when I'm not wrong!

  • December 13, 2005 at 8:48 am

    #609235

    Oh NICE ONE Jonathan!

    Thanks very much!

    Vida

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply