How to Copy ID from body of Outlook Inbox Email into Excel Spreadsheet

Posted on 2011-10-30
Last Modified: 2012-08-23
Hi Everyone,

I am looking for a Macro so that emails already received in my outlook 2007 inbox, would copy the ID located in the inbox into excel spreadsheet.

I have in the incoming emails IDs that will always have following in the body: NCXXXXXXX where X can be any number.  I want to copy NCXXXXXXX into a row in a spreadsheet (MS Excel 2007).

The emails are located in a folder in outlook.

Thank you for your assistance.
Question by:amriska
    LVL 59

    Expert Comment

    by:Chris Bottomley
    Which excel file ... i.e. where in the excel file is the data to be copied and will it be open already or can it be opened by outlook?

    LVL 59

    Accepted Solution

    The following OUTLOOK code, demonstrates the processing on the inbox and the output of the data to the file:


    Sub processFolder()
    Dim itm As Object
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlWS As Object
    Dim str As String
    Const xlup = -4162
        Set xlApp = CreateObject("excel.application")
        On Error Resume Next
        Set xlWB = xlApp.workbooks.Open("c:\deleteme\test.xls")
        On Error GoTo 0
        If xlWB Is Nothing Then
            Set xlWB = xlApp.workbooks.Add
            xlWB.SaveAs ("c:\deleteme\test.xls"), -4143
        End If
        Set xlWS = xlWB.sheets(1)
        For Each itm In Application.Session.GetDefaultFolder(olFolderInbox).Items
            str = NC_String(itm.Body)
            If str <> "" Then
                xlWS.Range("a" & xlWS.Rows.Count).End(xlup).Offset(1, 0).Value = str
            End If
    End Sub
    Function NC_String(str As String) As String
    Dim regex As Object
    Dim matches As Object
    Dim arr() As String
    Dim cnt As Integer
    Dim dirColon As Boolean
    'NC_String "sommit NC123456 as well as Nc1234569"
        NC_String = ""
        Set regex = CreateObject("vbscript.regexp")
        With regex
            .Global = True
            .IgnoreCase = True
            .Pattern = "NC[0-9]{7}"
        End With
        If regex.Test(str) Then
            NC_String = regex.Execute(str)(0)
        End If
    End Function

    Open in new window


    Author Comment

    Hi Chris,

    The data to be copied into a sheet of a spreadsheet.  Also, please note that the ID NCXXXXXX is located in the body of the email, and there are hundreds of emails which has inside the body this ID.  I want to make a list of all IDs that are located inside the body of the emails.

    LVL 59

    Expert Comment

    by:Chris Bottomley
    The script copies the data into a spreadsheet, it looks for the string in the body of the email and it processes every email in the folder to see if the string is present ... it is what you seemed to ask for hence I am awaiting to see if you need any extra tweaking to the macro.


    Author Closing Comment

    Thank You Chris

    Author Comment


    Can you help me on the following question:

    Thank you very much

    Author Comment

    Hi Chris,

    Can you answer the following question:



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Find out how to use dynamic social media in email signatures with this top 10 DOs & DON’Ts.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    761 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

    14 Experts available now in Live!

    Get 1:1 Help Now