Extracting Hail Report Data from Outlook Messages


A reader named Mohammed Syed asked if I could put together a script to parse information from messages he receives containing hail reports, and write it to a spreadsheet. Each message body contains one or more reports that look like this

1.00″ reported @ 04/10/2013 15:10 CST
IL, BRIGHTON – Zip Code: 62012 Zip Pop: 6261 Zip Homes: 2517
County: MACOUPIN
Remarks: DELAYED REPORT. NEAR THE INTERSECTION OF MACCOUPIN… JERSEY… AND MADISON COUNTIES.

For each report, Mohammed wants to extract the date/time of the report, the number of inches, and the zip code the report came from. Next, he needs the solution to create a report ID consisting of the alert date/time, the number of inches, and the zip code. The ID will be a concatenation of the elements with each element separated by an underscore. Mohammed wants the data written to an existing spreadsheet with a new row for each report. Finally, he wants to be able to do this from an Outlook rule he already has.

Here’s my solution. Hopefully this is just what Mohammed is looking for.

Instructions.

Follow these instructions to add the code to Outlook.

  1. Start Outlook
  2. Press ALT + F11 to open the Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. If not already expanded, expand Modules
  5. Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting InsertModule.
  6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Click the diskette icon on the toolbar to save the changes
  8. Close the VB Editor
Sub ParseHailMessage(olkMsg As Outlook.MailItem)
    'Create some constants
    'On the next line edit the path and name of the spreadsheet the data will go in
    Const WORKBOOK_PATH = "C:\SomeFilename.xlsx"

    'Create some variables
    Dim excApp As Object, excWkb As Object, excWks As Object
    Dim arrLines As Variant, varLine As Variant
    Dim strAlertID As String, strAlertTime As String, strInches As String, strZip As String
    Dim lngRow As Long

    'Connect to Excel
    Set excApp = CreateObject("Excel.Application")
    Set excWkb = excApp.Workbooks.Open(WORKBOOK_PATH)
    Set excWks = excWkb.Worksheets(1)

    'Get the last row in the worksheet
    lngRow = excWks.UsedRange.Rows.Count + 1

    'Split the body of the message into an array of lines
    arrLines = Split(olkMsg.Body, vbCrLf)

    'Read each line
    For Each varLine In arrLines
        'If the line is blank, then we must be entering a new report block (i.e. group of 4 lines)
        If varLine = "" Then
            'Write the last report block to the spreadsheet
            With excWks
                .Cells(lngRow, 1) = strAlertID
                .Cells(lngRow, 2) = strAlertTime
                .Cells(lngRow, 3) = strInches
                .Cells(lngRow, 4) = strZip
            End With
            'Increment the row counter
            lngRow = lngRow + 1
            'Initialize relevant variables for a new report
            strAlertID = ""
            strAlertTime = ""
            strInches = ""
            strZip = ""
        Else
            'Is this the first line of a report block?
            If InStr(1, varLine, "reported @") > 0 Then
                strInches = Left(varLine, InStr(1, varLine, "reported @") - 3)
                strAlertTime = Mid(varLine, InStr(1, varLine, "@") + 2, 16)
            Else
                'Is this the second line of a report block?
                If InStr(1, varLine, "Zip Code:") > 0 Then
                    strZip = Mid(varLine, InStr(1, varLine, "Zip Code:") + 10, 5)
                    strAlertID = strAlertTime & "_" & strInches & "_" & strZip
                End If
            End If
        End If
    Next

    'Save the spreadsheet and exit Excel
    excWkb.Close True

    'Disconnect from Excel
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excWks = Nothing
End Sub

Once Mohammed has added the code to Outlook, he will need to edit his rule and add a new action, run a script, to it. He’ll set ParseHailMessage as the script to run.

Advertisements

3 comments on “Extracting Hail Report Data from Outlook Messages

  1. Hi David! Thank you so much for getting this script so quickly! I tried it out on a PC running Exchange Server and Outlook 2010 and it worked perfectly with my rule. The rule moved my email to a subfolder, the script fired, and it exported the data to Excel without any issue.

    However, I did encounter something strange in my next scenario that I’m hoping you can shed some light on. This is actually the scenario in which I will use the script above.

    When I tried this script on a different PC with a Gmail IMAP account linked to Outlook 2010, my rule didn’t seem to run until I manually clicked on the subfolder that is referenced in the rule. But, only part of my rule ran which moves an email into a subfolder.

    The other part of the rule that runs the script above didn’t fire. In fact, it wont run the script unless I manually run the rule, which then exports the data incorrectly to Excel and looks like the example below. Would you happen to have any idea of why this wont work in this scenario? Thanks again for all of your help!

    Data Exported to Excel in Gmail Scenario (for each report):
    column1: column2: column3: column4:
    row1: data data
    row2: data data

    • Hi, Mohammed.

      You’re welcome.

      I don’t use IMAP much myself so I can’t answer off the top of my head. I’ll have to do some research and get back to you.

    • Mohammed,

      IMAP is the problem. It’s only downloading the headers not the full item. You can try following these instructions that describe changing a setting that is supposed to force Outlook to download the full item instead of just the header. I tried that and no matter what I do I still only get the header. Maybe it will work for you.

      As to the data not appearing in the right columns, I haven’t been able to duplicate that. Once my system has downloaded the full item the data always appears in the correct columns. Sorry, I don’t know what to suggest.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s