Exporting messages from outlook to excell RRS feed

  • Question

  • Hi, I am hoping someone can help me with a problem. I am very new to VBA and machros so hoping I can get some help. I have a product which generates emails once per day with site information  and there are 35 units (so 35 emails comming in) The info comes in as 1 message in the body as follows:


     12/07/09 08:31


    Tank,1,Volume,                Tank1=+04820,           

    I am trying to have outlook auto write this info to a file, and then link this file to excell where it will be automatically updated every day. I would like the updated file to be copyied over the original sheet rather than creating new sheets and it is fine if it all goes into one cell as I will use excells formule to seperate the data into useable fields.

    I tried using the export to file and i could manulley import it but had no luck with the auto update, and it would be really good if i could have the whole process done automatically. Ie email comes in to inbox, then auto sent to file and auto imported into excel and auto saved. I have rules in place for outlook which grab the emails and put them in there own folder (levels)     

    Thanks in advance for any help                                    

    Monday, July 9, 2012 5:15 AM

All replies

  • Is that the full text of the e-mail?

    What parts of it do you want to record?

    How do you want the information presented?

    I take it that Riorden_Sorden_ is the 'unit'?

    Do you want to record the data in an Excel worksheet?

    Do you want to replace the data in the worksheet with the days new data or append it to the worksheet? If the latter do you want it appending to the same row(s) or by adding new rows at the bottom of the existing data?

    The following is an Outlook script that you can run from a rule on an incoming message, written to perform a similar task, albeit with a different e-mail format. As written it uses early binding to Excel so you would need to set a reference to the Excel object library.

    If you are familiar with vba you should be able to modify it to suit your e-mail format and grab the data that you want and direct it to the appropriate part of the worksheet. If not supply the information I requested at the top of the message.

    Sub CustomLogCheckIn(item As Outlook.MailItem)
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim bStarted As Boolean
    Dim strText() As String
    Dim strName As String
    Dim strStatus As String
    Dim strLocType As String
    Dim strLocName As String
    Dim strWell As String
    Dim strProject As String
    Dim i As Long, j As Long
    Const strPath As String = "C:\Path\MyLog.xlsx"        'the path of the workbook

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Application.StatusBar = "Please wait while Excel source is opened ... "
        Set xlApp = CreateObject("Excel.Application")
        bStarted = True
    End If

    On Error GoTo CleanUp
    'Open the workbook to input the data
    Set xlWB = xlApp.Workbooks.Open(strPath)
    Set xlSheet = xlWB.Sheets("Sheet1")
    With item
        'Get the text of the message
        'and split it by paragraph
        strText = Split(item.Body, Chr(13))
        'Examine each paragraph
        For i = 1 To UBound(strText)
            'and locate the text relating to the item required
            If InStr(1, strText(i), "Name") Then Exit For
        Next i
        strName = Right(strText(i), Len(strText(i)) - 9)
        strStatus = Right(strText(i + 1), Len(strText(i + 1)) - 16)
        strLocType = Right(strText(i + 2), Len(strText(i + 2)) - 18)
        strLocName = Right(strText(i + 3), Len(strText(i + 3)) - 18)
        strWell = Right(strText(i + 4), Len(strText(i + 4)) - 13)
        strProject = Right(strText(i + 5), Len(strText(i + 5)) - 17)
        With xlSheet
            For j = 5 To xlSheet.Range("A" & xlSheet.Rows.Count).End(xlUp).Row
                If Trim(LCase(xlSheet.Cells(j, 1))) = Trim(LCase(strName)) Then
                    xlSheet.Cells(j, 2) = strStatus
                    xlSheet.Cells(j, 3) = strLocType
                    xlSheet.Cells(j, 4) = strLocName
                    xlSheet.Cells(j, 5) = strWell
                    xlSheet.Cells(j, 6) = strProject
                End If
            Next j
        End With
        MsgBox strName & " check-in log updated"
    End With
    xlWB.Close SaveChanges:=True
    If bStarted Then
    End If
    Set xlApp = Nothing
    Set xlWB = Nothing
    Set xlSheet = Nothing
    End Sub

    Graham Mayor - Word MVP

    Monday, July 9, 2012 6:05 AM