none
Extracting Body From Outlook to Excel - trouble with two fields having the same name? RRS feed

  • Question

  • Hi i'm new to VBA.

    I have some code to extract data from outlook emails and send to excel. The emails are from a web form. 

    The form fields include

    Company_Name:

    Name:

    Address:

    Town:

    etc...

    I don't need to extract Company_Name: but because i have Name: it picks up Company_Name info instead as it is higher up on the form.

    This is my code i would be grateful for a workaround as i'm a complete newby and have no idea how to get around it:

    Sub CopyToExcel()
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlSheet As Object
    Dim olItem As Outlook.MailItem
    Dim vText As Variant
    Dim sText As String
    Dim vItem As Variant
    Dim i As Long
    Dim rCount As Long
    Dim bXStarted As Boolean
    Dim bob As Long


    Const strPath As String = "C:\Users\Allen\Documents\newleads.xlsx" 'the path of the workbook

    If Application.ActiveExplorer.Selection.Count = 0 Then
    MsgBox "No Items selected!", vbCritical, "Error"
    Exit Sub
    End If
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
    Application.StatusBar = "Please wait while Excel source is opened ... "
    Set xlApp = CreateObject("Excel.Application")
    bXStarted = True
    End If
    On Error GoTo 0
    'Open the workbook to input the data
    Set xlWB = xlApp.Workbooks.Open(strPath)
    Set xlSheet = xlWB.Sheets("Sheet1")

    'Process each selected record
    rCount = xlSheet.UsedRange.Rows.Count
    For Each olItem In Application.ActiveExplorer.Selection
        sText = olItem.Body
        vText = Split(sText, Chr(13))
        'Find the next empty line of the worksheet
        rCount = rCount + 1



    'Check each line of text in the message body
    For i = UBound(vText) To 0 Step -1
    If InStr(1, vText(i), "Name:") > 0 Then
    vItem = Split(vText(i), Chr(58))
    xlSheet.Range("A" & rCount) = Trim(vItem(1))
    End If

    If InStr(1, vText(i), "Address:") > 0 Then
    vItem = Split(vText(i), Chr(58))
    xlSheet.Range("B" & rCount) = Trim(vItem(1))
    End If

    If InStr(1, vText(i), "Town:") > 0 Then
    vItem = Split(vText(i), Chr(58))
    xlSheet.Range("C" & rCount) = Trim(vItem(1))
    End If

    If InStr(1, vText(i), "County:") > 0 Then
    vItem = Split(vText(i), Chr(58))
    xlSheet.Range("D" & rCount) = Trim(vItem(1))
    End If

    If InStr(1, vText(i), "Postcode:") > 0 Then
    vItem = Split(vText(i), Chr(58))
    xlSheet.Range("E" & rCount) = Trim(vItem(1))
    End If

    If InStr(1, vText(i), "Email:") > 0 Then
    vItem = Split(vText(i), Chr(58))
    xlSheet.Range("F" & rCount) = Trim(vItem(1))
    End If

    If InStr(1, vText(i), "Mobile_Tel:") > 0 Then
    vItem = Split(vText(i), Chr(58))
    xlSheet.Range("G" & rCount) = Trim(vItem(1))
    End If

    If InStr(1, vText(i), "Home_Tel:") > 0 Then
    vItem = Split(vText(i), Chr(58))
    xlSheet.Range("H" & rCount) = Trim(vItem(1))
    End If

    If InStr(1, vText(i), "Work_Tel:") > 0 Then
    vItem = Split(vText(i), Chr(58))
    xlSheet.Range("I" & rCount) = Trim(vItem(1))
    End If
    Next i
    xlWB.Save
    Next olItem
    xlWB.Close SaveChanges:=True
    If bXStarted Then
    xlApp.Quit
    End If
    Set xlApp = Nothing
    Set xlWB = Nothing
    Set xlSheet = Nothing
    Set olItem = Nothing
    End Sub

    Saturday, November 1, 2014 2:03 AM

Answers

  • Is the 'N' of 'Name' the first character on the line, or are there leading space characters?

    Instr reports the position of the text string "Name:" in the line that is vText(i)

    The line

    If InStr(1, vText(i), "Name:") = 1 Then

    checks to see if "Name:" starts at the first character position in that line. If it is not at Position 1 then the command will not find it. You could change the =1 to a value that is less its position in "Company_Name:" e.g. < 8 but if there are leading spaces, they will affect the results so the number of spaces must be taken into consideration. If the number is the same for both "Name and Company_Name, because of spacing issues, then you are going to have to use a nested condition e.g.

    If InStr(1, vText(i), "Name:") > 0 AND InStr(1, vText(i), "Company_Name:") = 0 Then


    Graham Mayor - Word MVP
    www.gmayor.com


    Tuesday, November 4, 2014 5:39 AM

All replies

  • As you have realised the item in question is

    If InStr(1, vText(i), "Name:") > 0 Then

    This looks for "Name:" in the string and of course Name is in both the Name line and the Company_Name line.

    However if instead of looking for Name: starting from the first character you start from another position e.g. 2  through to 8 (The end of "Company_") Name: alone will not be found, but Company_Name: will.

    If InStr(8, vText(i), "Name:") > 0 Then


    Graham Mayor - Word MVP
    www.gmayor.com

    Saturday, November 1, 2014 8:49 AM
  • Thanks for your reply Graham

    I only need Name: not Company_Name:

    What changes do i need to make to If InStr(1, vText(i), "Name:") > 0 Then

    If i only want to find Name: within the range 1-5 (So it doesn't find Company_Name:

    I have tried If InStr(1-5, vText(i), "Name:") > 0 which doesn't work.

    Whats the correct code to only search the first 5 characters for Name: ?

    Your help is much appreciated

    Thanks Again

    Saturday, November 1, 2014 9:28 PM
  • In that case you could use

    If InStr(1, vText(i), "Name:") = 1 Then


    Graham Mayor - Word MVP
    www.gmayor.com


    Sunday, November 2, 2014 3:10 PM
  • Hi Graham

    I tried you suggestion and still no luck

    Monday, November 3, 2014 8:25 PM
  • Is the 'N' of 'Name' the first character on the line, or are there leading space characters?

    Instr reports the position of the text string "Name:" in the line that is vText(i)

    The line

    If InStr(1, vText(i), "Name:") = 1 Then

    checks to see if "Name:" starts at the first character position in that line. If it is not at Position 1 then the command will not find it. You could change the =1 to a value that is less its position in "Company_Name:" e.g. < 8 but if there are leading spaces, they will affect the results so the number of spaces must be taken into consideration. If the number is the same for both "Name and Company_Name, because of spacing issues, then you are going to have to use a nested condition e.g.

    If InStr(1, vText(i), "Name:") > 0 AND InStr(1, vText(i), "Company_Name:") = 0 Then


    Graham Mayor - Word MVP
    www.gmayor.com


    Tuesday, November 4, 2014 5:39 AM