Extract lines 3 and 4 from an email

Question

I need to export line 3 and line 4 from an email. I have a VBA code stored in the Microsoft Outlook Session. It works well, but it exports the entire mail body. I need only lines 3 and 4.

Here is the sample code:

Option Explicit

Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()

    Dim olApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Set olApp = Outlook.Application
    Set objNS = olApp.GetNamespace("MAPI")


    Set Items = objNS.GetDefaultFolder(olFolderInbox).Folders("Leads").Items

End Sub
Private Sub Items_ItemAdd(ByVal item As Object)

    On Error GoTo ErrorHandler

    Dim Msg As Outlook.MailItem

    Dim oXL As Object
    Dim oWS As Object
    Dim lngRow As Long

    Set oXL = CreateObject("Excel.Application")

    oXL.Workbooks.Open FileName:="C:\temp\Mail_Export.xlsx", AddTOMRU:=False, UpdateLinks:=False


    Set oWS = oXL.Sheets("Sheet1")
    lngRow = oWS.Range("A" & oXL.Rows.Count).End(-4162).Offset(1).Row

    If TypeName(item) = "MailItem" Then
        Set Msg = item

        With oWS
            .cells(lngRow, 1).Value = Msg.SenderName
            .cells(lngRow, 2).Value = Msg.SenderEmailAddress
            .cells(lngRow, 3).Value = Msg.Body



        End With

        With oXL
            .activeworkbook.Save
            .activeworkbook.Close SaveChanges:=2 '
            .Application.Quit
        End With

        Set oXL = Nothing
        Set oWS = Nothing

    End If

ExitPoint:

    Exit Sub

ErrorHandler:
    MsgBox Err.Number & " - " & Err.Description
    Resume ExitPoint


    Resume

End Function

Mail_Export.xlsx is auto aving the emails exported from "Leads" folder in MS Outlook. However, I need only lines 3 and 4.

This is the sample mail body:

Hi Team
Following people contacted your helpdesk:-
Name: ABC DEF
Email: abc.def@gmail.com
Thank you
XYZ Consultants

score 3 · Accepted Answer · edited May 23 '17 at 10:26

3

Msg.Body is probably of the form:

Hi Team[CR][LF]Following people contacted your helpdesk:-[CR][LF]Name: ABC DEF[CR][LF]Email: abc.def@gmail.com[CR][LF]Thank you[CR][LF]XYZ Consultants

Where [CR] represents Carriage return and [LF] represents Linefeed.

The following would split Msg.Body into lines:

Dim Lines() As String

Lines = Split(Msg.Body, vbCR & vbLF)

You could then extracts lines 3 and 4 with:

.cells(lngRow, 3).Value = Lines(LBound(Lines)+2) & vbLF & _
                          Lines(LBound(Lines)+3)

My answer to this question How to copy Outlook mail message into excel using VBA or Macros will give you a fuller idea of what an Outlook mail item looks like to a VBA macro.

edited May 23 '17 at 10:26

Community

1
1

answered Sep 13 '15 at 12:02

Tony Dallimore

12,335
7
32
61

I am pleased that worked for you. However, I am concerned that the heading is " ... the following people ... ". That implies the name and email lines can repeat. Extracting multiple line pairs would be a little (but only a little) more complicated. – Tony Dallimore Sep 13 '15 at 15:34
++ Nicely done. BTW Tony, If the format is constant then multiple lines also shouldn't make a difference :) You do not need `SPLIT` actually. You can use `INSTR` with "Name: " , "Email: ", "Thank you" and finally `MID` to extract the details... – Siddharth Rout Sep 13 '15 at 19:31
@SiddharthRout. Thanks for the +. If I was coding for myself I would have done things differently. I would probably have gone for one line per person with name in column A and email in column B so the data was fully decoded in one go. However, the OP asked for the 2nd and 3rd line so that was what I provided. I was taught to provide what the client requested and not what you thought they needed. – Tony Dallimore Sep 13 '15 at 21:51

Extract lines 3 and 4 from an email

1 Answers1