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

Posted on 2011-10-30
Medium Priority
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
  • 4
  • 3
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37053607
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

Chris Bottomley earned 2000 total points
ID: 37053674
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

ID: 37053731
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.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 59

Expert Comment

by:Chris Bottomley
ID: 37053743
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

ID: 37134094
Thank You Chris

Author Comment

ID: 37860681

Can you help me on the following question:


Thank you very much

Author Comment

ID: 38325276
Hi Chris,

Can you answer the following question:




Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This article will help to fix the below errors for MS Exchange Server 2016 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
Suggested Courses

807 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