How to copy Outlook mail message into excel using VBA or Macros

Since you have not mentioned what needs to be copied, I have left that section empty in the code below.

Also you don't need to move the email to the folder first and then run the macro in that folder. You can run the macro on the incoming mail and then move it to the folder at the same time.

This will get you started. I have commented the code so that you will not face any problem understanding it.

First paste the below mentioned code in the outlook module.

Then

1. Click on Tools~~>Rules and Alerts
2. Click on "New Rule"
3. Click on "start from a blank rule"
4. Select "Check messages When they arrive"
5. Under conditions, click on "with specific words in the subject"
6. Click on "specific words" under rules description.
7. Type the word that you want to check in the dialog box that pops up and click on "add".
8. Click "Ok" and click next
9. Select "move it to specified folder" and also select "run a script" in the same box
10. In the box below, specify the specific folder and also the script (the macro that you have in module) to run.
11. Click on finish and you are done.

When the new email arrives not only will the email move to the folder that you specify but data from it will be exported to Excel as well.

UNTESTED

Const xlUp As Long = -4162Sub ExportToExcel(MyMail As MailItem)    Dim strID As String, olNS As Outlook.Namespace    Dim olMail As Outlook.MailItem    Dim strFileName As String    '~~> Excel Variables    Dim oXLApp As Object, oXLwb As Object, oXLws As Object    Dim lRow As Long    strID = MyMail.EntryID    Set olNS = Application.GetNamespace("MAPI")    Set olMail = olNS.GetItemFromID(strID)    '~~> Establish an EXCEL application object    On Error Resume Next    Set oXLApp = GetObject(, "Excel.Application")    '~~> If not found then create new instance    If Err.Number <> 0 Then        Set oXLApp = CreateObject("Excel.Application")    End If    Err.Clear    On Error GoTo 0    '~~> Show Excel    oXLApp.Visible = True    '~~> Open the relevant file    Set oXLwb = oXLApp.Workbooks.Open("C:\Sample.xls")    '~~> Set the relevant output sheet. Change as applicable    Set oXLws = oXLwb.Sheets("Sheet1")    lRow = oXLws.Range("A" & oXLApp.Rows.Count).End(xlUp).Row + 1    '~~> Write to outlook    With oXLws        '        '~~> Code here to output data from email to Excel File        '~~> For example        '        .Range("A" & lRow).Value = olMail.Subject        .Range("B" & lRow).Value = olMail.SenderName        '    End With    '~~> Close and Clean up Excel    oXLwb.Close (True)    oXLApp.Quit    Set oXLws = Nothing    Set oXLwb = Nothing    Set oXLApp = Nothing    Set olMail = Nothing    Set olNS = NothingEnd Sub

FOLLOWUP

To extract the contents from your email body, you can split it using SPLIT() and then parsing out the relevant information from it. See this example

Dim MyAr() As StringMyAr = Split(olMail.body, vbCrLf)For i = LBound(MyAr) To UBound(MyAr)    '~~> This will give you the contents of your email    '~~> on separate lines    Debug.Print MyAr(i)Next i

New introduction 2

In the previous version of macro "SaveEmailDetails" I used this statement to find Inbox:

Set FolderTgt = CreateObject("Outlook.Application"). _              GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)

I have since installed a newer version of Outlook and I have discovered that it does not use the default Inbox. For each of my email accounts, it created a separate store (named for the email address) each with its own Inbox. None of those Inboxes is the default.

This macro, outputs the name of the store holding the default Inbox to the Immediate Window:

Sub DsplUsernameOfDefaultStore()  Dim NS As Outlook.NameSpace  Dim DefaultInboxFldr As MAPIFolder  Set NS = CreateObject("Outlook.Application").GetNamespace("MAPI")  Set DefaultInboxFldr = NS.GetDefaultFolder(olFolderInbox)  Debug.Print DefaultInboxFldr.Parent.NameEnd Sub

On my installation, this outputs: "Outlook Data File".

I have added an extra statement to macro "SaveEmailDetails" that shows how to access the Inbox of any store.

New introduction 1

A number of people have picked up the macro below, found it useful and have contacted me directly for further advice. Following these contacts I have made a few improvements to the macro so I have posted the revised version below. I have also added a pair of macros which together will return the MAPIFolder object for any folder with the Outlook hierarchy. These are useful if you wish to access other than a default folder.

The original text referenced one question by date which linked to an earlier question. The first question has been deleted so the link has been lost. That link was to Update excel sheet based on outlook mail (closed)

Original text

There are a surprising number of variations of the question: "How do I extract data from Outlook emails to Excel workbooks?" For example, two questions up on [outlook-vba] the same question was asked on 13 August. That question references a variation from December that I attempted to answer.

For the December question, I went overboard with a two part answer. The first part was a series of teaching macros that explored the Outlook folder structure and wrote data to text files or Excel workbooks. The second part discussed how to design the extraction process. For this question Siddarth has provided an excellent, succinct answer and then a follow-up to help with the next stage.

What the questioner of every variation appears unable to understand is that showing us what the data looks like on the screen does not tell us what the text or html body looks like. This answer is an attempt to get past that problem.

The macro below is more complicated than Siddarth’s but a lot simpler that those I included in my December answer. There is more that could be added but I think this is enough to start with.

The macro creates a new Excel workbook and outputs selected properties of every email in Inbox to create this worksheet:

Near the top of the macro there is a comment containing eight hashes (#). The statement below that comment must be changed because it identifies the folder in which the Excel workbook will be created.

All other comments containing hashes suggest amendments to adapt the macro to your requirements.

How are the emails from which data is to be extracted identified? Is it the sender, the subject, a string within the body or all of these? The comments provide some help in eliminating uninteresting emails. If I understand the question correctly, an interesting email will have Subject = "Task Completed".

The comments provide no help in extracting data from interesting emails but the worksheet shows both the text and html versions of the email body if they are present. My idea is that you can see what the macro will see and start designing the extraction process.

This is not shown in the screen image above but the macro outputs two versions on the text body. The first version is unchanged which means tab, carriage return, line feed are obeyed and any non-break spaces look like spaces. In the second version, I have replaced these codes with the strings [TB], [CR], [LF] and [NBSP] so they are visible. If my understanding is correct, I would expect to see the following within the second text body:

Activity[TAB]Count[CR][LF]Open[TAB]35[CR][LF]HCQA[TAB]42[CR][LF]HCQC[TAB]60[CR][LF]HAbst[TAB]50 45 5 2 2 1[CR][LF] and so on

Extracting the values from the original of this string should not be difficult.

I would try amending my macro to output the extracted values in addition to the email’s properties. Only when I have successfully achieved this change would I attempt to write the extracted data to an existing workbook. I would also move processed emails to a different folder. I have shown where these changes must be made but give no further help. I will respond to a supplementary question if you get to the point where you need this information.

Good luck.

Latest version of macro included within the original text

Option ExplicitPublic Sub SaveEmailDetails()  ' This macro creates a new Excel workbook and writes to it details  ' of every email in the Inbox.  ' Lines starting with hashes either MUST be changed before running the  ' macro or suggest changes you might consider appropriate.  Dim AttachCount As Long  Dim AttachDtl() As String  Dim ExcelWkBk As Excel.Workbook  Dim FileName As String  Dim FolderTgt As MAPIFolder  Dim HtmlBody As String  Dim InterestingItem As Boolean  Dim InxAttach As Long  Dim InxItemCrnt As Long  Dim PathName As String  Dim ReceivedTime As Date  Dim RowCrnt As Long  Dim SenderEmailAddress As String  Dim SenderName As String  Dim Subject As String  Dim TextBody As String  Dim xlApp As Excel.Application  ' The Excel workbook will be created in this folder.  ' ######## Replace "C:\DataArea\SO" with the name of a folder on your disc.  PathName = "C:\DataArea\SO"  ' This creates a unique filename.  ' #### If you use a version of Excel 2003, change the extension to "xls".  FileName = Format(Now(), "yymmdd hhmmss") & ".xlsx"  ' Open own copy of Excel  Set xlApp = Application.CreateObject("Excel.Application")  With xlApp    ' .Visible = True         ' This slows your macro but helps during debugging    .ScreenUpdating = False ' Reduces flash and increases speed    ' Create a new workbook    ' #### If updating an existing workbook, replace with an    ' #### Open workbook statement.    Set ExcelWkBk = xlApp.Workbooks.Add    With ExcelWkBk      ' #### None of this code will be useful if you are adding      ' #### to an existing workbook.  However, it demonstrates a      ' #### variety of useful statements.      .Worksheets("Sheet1").Name = "Inbox"    ' Rename first worksheet      With .Worksheets("Inbox")        ' Create header line        With .Cells(1, "A")          .Value = "Field"          .Font.Bold = True        End With        With .Cells(1, "B")          .Value = "Value"          .Font.Bold = True        End With        .Columns("A").ColumnWidth = 18        .Columns("B").ColumnWidth = 150      End With    End With    RowCrnt = 2  End With  ' FolderTgt is the folder I am going to search.  This statement says  ' I want to seach the Inbox.  The value "olFolderInbox" can be replaced  ' to allow any of the standard folders to be searched.  ' See FindSelectedFolder() for a routine that will search for any folder.  Set FolderTgt = CreateObject("Outlook.Application"). _              GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)  ' #### Use the following the access a non-default Inbox.  ' #### Change "Xxxx" to name of one of your store you want to access.  Set FolderTgt = Session.Folders("Xxxx").Folders("Inbox")  ' This examines the emails in reverse order. I will explain why later.  For InxItemCrnt = FolderTgt.Items.Count To 1 Step -1    With FolderTgt.Items.Item(InxItemCrnt)      ' A folder can contain several types of item: mail items, meeting items,      ' contacts, etc.  I am only interested in mail items.      If .Class = olMail Then        ' Save selected properties to variables        ReceivedTime = .ReceivedTime        Subject = .Subject        SenderName = .SenderName        SenderEmailAddress = .SenderEmailAddress        TextBody = .Body        HtmlBody = .HtmlBody        AttachCount = .Attachments.Count        If AttachCount > 0 Then          ReDim AttachDtl(1 To 7, 1 To AttachCount)          For InxAttach = 1 To AttachCount            ' There are four types of attachment:            '  *   olByValue       1            '  *   olByReference   4            '  *   olEmbeddedItem  5            '  *   olOLE           6            Select Case .Attachments(InxAttach).Type              Case olByValue            AttachDtl(1, InxAttach) = "Val"              Case olEmbeddeditem            AttachDtl(1, InxAttach) = "Ebd"              Case olByReference            AttachDtl(1, InxAttach) = "Ref"              Case olOLE            AttachDtl(1, InxAttach) = "OLE"              Case Else            AttachDtl(1, InxAttach) = "Unk"            End Select            ' Not all types have all properties.  This code handles            ' those missing properties of which I am aware.  However,            ' I have never found an attachment of type Reference or OLE.            ' Additional code may be required for them.            Select Case .Attachments(InxAttach).Type              Case olEmbeddeditem                AttachDtl(2, InxAttach) = ""              Case Else                AttachDtl(2, InxAttach) = .Attachments(InxAttach).PathName            End Select            AttachDtl(3, InxAttach) = .Attachments(InxAttach).FileName            AttachDtl(4, InxAttach) = .Attachments(InxAttach).DisplayName            AttachDtl(5, InxAttach) = "--"            ' I suspect Attachment had a parent property in early versions            ' of Outlook. It is missing from Outlook 2016.            On Error Resume Next            AttachDtl(5, InxAttach) = .Attachments(InxAttach).Parent            On Error GoTo 0            AttachDtl(6, InxAttach) = .Attachments(InxAttach).Position            ' Class 5 is attachment.  I have never seen an attachment with            ' a different class and do not see the purpose of this property.            ' The code will stop here if a different class is found.            Debug.Assert .Attachments(InxAttach).Class = 5            AttachDtl(7, InxAttach) = .Attachments(InxAttach).Class          Next        End If        InterestingItem = True      Else        InterestingItem = False      End If    End With    ' The most used properties of the email have been loaded to variables but    ' there are many more properies.  Press F2.  Scroll down classes until    ' you find MailItem.  Look through the members and note the name of    ' any properties that look useful.  Look them up using VB Help.    ' #### You need to add code here to eliminate uninteresting items.    ' #### For example:    'If SenderEmailAddress <> "JohnDoe@AcmeSoftware.co.zy" Then    '  InterestingItem = False    'End If    'If InStr(Subject, "Accounts payable") = 0 Then    '  InterestingItem = False    'End If    'If AttachCount = 0 Then    '  InterestingItem = False    'End If    ' #### If the item is still thought to be interesting I    ' #### suggest extracting the required data to variables here.    ' #### You should consider moving processed emails to another    ' #### folder.  The emails are being processed in reverse order    ' #### to allow this removal of an email from the Inbox without    ' #### effecting the index numbers of unprocessed emails.    If InterestingItem Then      With ExcelWkBk        With .Worksheets("Inbox")          ' #### This code creates a dividing row and then          ' #### outputs a property per row.  Again it demonstrates          ' #### statements that are likely to be useful in the final          ' #### version          ' Create dividing row between emails          .Rows(RowCrnt).RowHeight = 5          .Range(.Cells(RowCrnt, "A"), .Cells(RowCrnt, "B")) _                                      .Interior.Color = RGB(0, 255, 0)          RowCrnt = RowCrnt + 1          .Cells(RowCrnt, "A").Value = "Sender name"          .Cells(RowCrnt, "B").Value = SenderName          RowCrnt = RowCrnt + 1          .Cells(RowCrnt, "A").Value = "Sender email address"          .Cells(RowCrnt, "B").Value = SenderEmailAddress          RowCrnt = RowCrnt + 1          .Cells(RowCrnt, "A").Value = "Received time"          With .Cells(RowCrnt, "B")            .NumberFormat = "@"            .Value = Format(ReceivedTime, "mmmm d, yyyy h:mm")          End With          RowCrnt = RowCrnt + 1          .Cells(RowCrnt, "A").Value = "Subject"          .Cells(RowCrnt, "B").Value = Subject          RowCrnt = RowCrnt + 1          If AttachCount > 0 Then            .Cells(RowCrnt, "A").Value = "Attachments"            .Cells(RowCrnt, "B").Value = "Inx|Type|Path name|File name|Display name|Parent|Position|Class"            RowCrnt = RowCrnt + 1            For InxAttach = 1 To AttachCount              .Cells(RowCrnt, "B").Value = InxAttach & "|" & _                                           AttachDtl(1, InxAttach) & "|" & _                                           AttachDtl(2, InxAttach) & "|" & _                                           AttachDtl(3, InxAttach) & "|" & _                                           AttachDtl(4, InxAttach) & "|" & _                                           AttachDtl(5, InxAttach) & "|" & _                                           AttachDtl(6, InxAttach) & "|" & _                                           AttachDtl(7, InxAttach)              RowCrnt = RowCrnt + 1            Next          End If          If TextBody <> "" Then            ' ##### This code was in the original version of the macro            ' ##### but I did not find it as useful as the other version of            ' ##### the text body.  See below            ' This outputs the text body with CR, LF and TB obeyed            'With .Cells(RowCrnt, "A")            '  .Value = "text body"            '  .VerticalAlignment = xlTop            'End With            'With .Cells(RowCrnt, "B")            '  ' The maximum size of a cell 32,767            '  .Value = Mid(TextBody, 1, 32700)            '  .WrapText = True            'End With            'RowCrnt = RowCrnt + 1            ' This outputs the text body with NBSP, CR, LF and TB            ' replaced by strings.            With .Cells(RowCrnt, "A")              .Value = "text body"              .VerticalAlignment = xlTop            End With            TextBody = Replace(TextBody, Chr(160), "[NBSP]")            TextBody = Replace(TextBody, vbCr, "[CR]")            TextBody = Replace(TextBody, vbLf, "[LF]")            TextBody = Replace(TextBody, vbTab, "[TB]")            With .Cells(RowCrnt, "B")              ' The maximum size of a cell 32,767              .Value = Mid(TextBody, 1, 32700)              .WrapText = True            End With            RowCrnt = RowCrnt + 1          End If          If HtmlBody <> "" Then            ' ##### This code was in the original version of the macro            ' ##### but I did not find it as useful as the other version of            ' ##### the html body.  See below            ' This outputs the html body with CR, LF and TB obeyed            'With .Cells(RowCrnt, "A")            '  .Value = "Html body"            '  .VerticalAlignment = xlTop            'End With            'With .Cells(RowCrnt, "B")            '  .Value = Mid(HtmlBody, 1, 32700)            '  .WrapText = True            'End With            'RowCrnt = RowCrnt + 1            ' This outputs the html body with NBSP, CR, LF and TB            ' replaced by strings.            With .Cells(RowCrnt, "A")              .Value = "Html body"              .VerticalAlignment = xlTop            End With            HtmlBody = Replace(HtmlBody, Chr(160), "[NBSP]")            HtmlBody = Replace(HtmlBody, vbCr, "[CR]")            HtmlBody = Replace(HtmlBody, vbLf, "[LF]")            HtmlBody = Replace(HtmlBody, vbTab, "[TB]")            With .Cells(RowCrnt, "B")              .Value = Mid(HtmlBody, 1, 32700)              .WrapText = True            End With            RowCrnt = RowCrnt + 1          End If        End With      End With    End If  Next  With xlApp    With ExcelWkBk      ' Write new workbook to disc      If Right(PathName, 1) <> "\" Then        PathName = PathName & "\"      End If      .SaveAs FileName:=PathName & FileName      .Close    End With    .Quit   ' Close our copy of Excel  End With  Set xlApp = Nothing       ' Clear reference to ExcelEnd Sub

Macros not included in original post but which some users of above macro have found useful.

Public Sub FindSelectedFolder(ByRef FolderTgt As MAPIFolder, _                              ByVal NameTgt As String, ByVal NameSep As String)  ' This routine (and its sub-routine) locate a folder within the hierarchy and  ' returns it as an object of type MAPIFolder  ' NameTgt   The name of the required folder in the format:  '              FolderName1 NameSep FolderName2 [ NameSep FolderName3 ] ...  '           If NameSep is "|", an example value is "Personal Folders|Inbox"  '           FolderName1 must be an outer folder name such as  '           "Personal Folders". The outer folder names are typically the names  '           of PST files.  FolderName2 must be the name of a folder within  '           Folder1; in the example "Inbox".  FolderName2 is compulsory.  This  '           routine cannot return a PST file; only a folder within a PST file.  '           FolderName3, FolderName4 and so on are optional and allow a folder  '           at any depth with the hierarchy to be specified.  ' NameSep   A character or string used to separate the folder names within  '           NameTgt.  ' FolderTgt On exit, the required folder.  Set to Nothing if not found.  ' This routine initialises the search and finds the top level folder.  ' FindSelectedSubFolder() is used to find the target folder within the  ' top level folder.  Dim InxFolderCrnt As Long  Dim NameChild As String  Dim NameCrnt As String  Dim Pos As Long  Dim TopLvlFolderList As Folders  Set FolderTgt = Nothing   ' Target folder not found  Set TopLvlFolderList = _          CreateObject("Outlook.Application").GetNamespace("MAPI").Folders  ' Split NameTgt into the name of folder at current level  ' and the name of its children  Pos = InStr(NameTgt, NameSep)  If Pos = 0 Then    ' I need at least a level 2 name    Exit Sub  End If  NameCrnt = Mid(NameTgt, 1, Pos - 1)  NameChild = Mid(NameTgt, Pos + 1)  ' Look for current name.  Drop through and return nothing if name not found.  For InxFolderCrnt = 1 To TopLvlFolderList.Count    If NameCrnt = TopLvlFolderList(InxFolderCrnt).Name Then      ' Have found current name. Call FindSelectedSubFolder() to      ' look for its children      Call FindSelectedSubFolder(TopLvlFolderList.Item(InxFolderCrnt), _                                            FolderTgt, NameChild, NameSep)      Exit For    End If  NextEnd SubPublic Sub FindSelectedSubFolder(FolderCrnt As MAPIFolder, _                      ByRef FolderTgt As MAPIFolder, _                      ByVal NameTgt As String, ByVal NameSep As String)  ' See FindSelectedFolder() for an introduction to the purpose of this routine.  ' This routine finds all folders below the top level  ' FolderCrnt The folder to be seached for the target folder.  ' NameTgt    The NameTgt passed to FindSelectedFolder will be of the form:  '               A|B|C|D|E  '            A is the name of outer folder which represents a PST file.  '            FindSelectedFolder() removes "A|" from NameTgt and calls this  '            routine with FolderCrnt set to folder A to search for B.  '            When this routine finds B, it calls itself with FolderCrnt set to  '            folder B to search for C.  Calls are nested to whatever depth are  '            necessary.  ' NameSep    As for FindSelectedSubFolder  ' FolderTgt  As for FindSelectedSubFolder  Dim InxFolderCrnt As Long  Dim NameChild As String  Dim NameCrnt As String  Dim Pos As Long  ' Split NameTgt into the name of folder at current level  ' and the name of its children  Pos = InStr(NameTgt, NameSep)  If Pos = 0 Then    NameCrnt = NameTgt    NameChild = ""  Else    NameCrnt = Mid(NameTgt, 1, Pos - 1)    NameChild = Mid(NameTgt, Pos + 1)  End If  ' Look for current name.  Drop through and return nothing if name not found.  For InxFolderCrnt = 1 To FolderCrnt.Folders.Count    If NameCrnt = FolderCrnt.Folders(InxFolderCrnt).Name Then      ' Have found current name.      If NameChild = "" Then        ' Have found target folder        Set FolderTgt = FolderCrnt.Folders(InxFolderCrnt)      Else        'Recurse to look for children        Call FindSelectedSubFolder(FolderCrnt.Folders(InxFolderCrnt), _                                            FolderTgt, NameChild, NameSep)      End If      Exit For    End If  Next  ' If NameCrnt not found, FolderTgt will be returned unchanged.  Since it is  ' initialised to Nothing at the beginning, that will be the returned value.End Sub