RE: [O2007] -> How to search for a string in the body and get the line
Steve-
There may be a better way, but here is some code that might help. This code
takes an email and splits lines into an array. You could then loop from
lbound(arrayname) to ubound(arrayname) and use Instr to find the line with
your target string.
At that point, you would know from the loop what (line) number the string
was found on, and be able to go back to the source message and replace the
line n-3 with x = y & vbcrlf & x
where x is the original line, and you are inserting your target text
followed by the line that was already there.
For the following code, you can just ignore all the Excel stuff, and just
focus on the split statement and array.
Sub ParseEmailFolderToExcel()
Set objApp = Application
Set olns = Outlook.GetNamespace("MAPI")
Set myinbox = olns.PickFolder
'Set myItems = myinbox.Items
Dim XLApp As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim ExcelWasNotRunning As Boolean
On Error Resume Next
Set XLApp = GetObject(, "Excel.Application")
If Err Then
ExcelWasNotRunning = True
Set XLApp = New Excel.Application
XLApp.Visible = True
End If
Set wkb = XLApp.Workbooks.Add
Set wks = wkb.Sheets(1)
With wks
StartCount = 0
strEmailContents = ""
For Each outlookmessage In myinbox.Items
StartCount = StartCount + 1
> Range("A" & StartCount).Value = outlookmessage.ReceivedTime
> Range("B" & StartCount).Value = outlookmessage.SenderName
> Range("C" & StartCount).Value = outlookmessage.Subject
FullMsg = outlookmessage.Body
AllLines = Split(FullMsg, vbCrLf)
On Error Resume Next
eachval = Split(AllLines(2), """,""") 'I was just grabbing the
second line
> Range("D" & StartCount).Value = Right(eachval(0),
Len(eachval(0)) - 1)
> Range("E" & StartCount).Value = eachval(1)
> Range("F" & StartCount).Value = eachval(2)
> Range("G" & StartCount).Value = eachval(3)
> Range("H" & StartCount).Value = eachval(4)
> Range("I" & StartCount).Value = Left(eachval(5),
Len(eachval(5)) - 2)
On Error GoTo 0
'you'd need to use something like:
For eVal = lbound(eachVal) to ubound(eachval)
'the ucase and all caps are to find a match without being
case sensitive
'if you eliminate the ucase and try to match "Sincerely
Yours" then
'it would not find the line "Sincerely yours" for example.
FoundString = Instr(UCASE(eachVal(eVal)),"SINCERELY YOURS,")
If(Not(IsERROR(FoundString))) then
'Do your replacement code on line eVal-3 (or would that
be eVal -2?)
End If
Next
Next
End With
Set myOlApp = Nothing
Set olns = Nothing
Set myinbox = Nothing
Set myItems = Nothing
End Sub
"Steve JORDI" wrote:
> Hi,
> I'm trying to get a reply to a mail message and look for a specific
> substring.
> Then I need to know the position of the line.
> For example:
> search "Sincerely yours,"
> get the line number
> insert 3 lines BEFORE the "Sincerly Yours"
> I can't find a way. I know how to use Replace to change
> subtrings, but I can't get a way to just locate one and get
> the line number.
> How can I do this?
> Then, how can I insert text at that linePosition-3?
> Thanks for any help.
> Sincerely,
> Steve JORDI
> (Remove the K_I_L_LSPAM from my email address)
> ----------------------------------------------> 1197 Prangins Email: stevejordiK_I_L_LSPAM@hotmail.com
> Switzerland WWW: www.sjordi.com
> ----------------------------------------------> Volcanoes at www.sjordi.com/volcanoes
> MovieDB at www.sjmoviedb.com
> ----------------------------------------------> .
>
