get information from email into excel

[deviation]

Hello all,

I have searched the board for awhile now and I am unable to locate anything that is similar to my situation..

I have an excel file that generates an email... (other users will use this).

Now what I would like to do is search my inbox for the messages (they all have the same subject line).. and import the messages into excel (preferrably into a userform) but to import into an excel sheet would be acceptable as well (I think this would be the easiest option)

Right now the email that is generated by the sendmail macro creates a table and enters the information in it (this could be modified if necessary)

Name: Smoe, Joe
field2: Number
Field3: data

etc (there are 18 rows in the table and 2 columns)

i would like to extract the data from the email and place it all on one row in an excel sheet (looping thru the inbox for each message)..

any suggestions?

Thanks in advance for your help

 

[deviation]

I tried (just to make sure that I didn't do anything to it on my side), I copied the send mail procedure and then the get mail procedure..

once I make the tweaks (getting partial subject and the left OLmsg.sender)

I tried to run it again

Got the same error (never passed the IF statement).. so tweked it with your recommendation..

and back to the error on the NewCol.add.

The error is a new one to me..

Run Time Error '457'
"The key is already associated with an element of this collection"

 

[Zack Barresse]

The error pops up when a duplicate arises.

Let's try a new approach. Check your email. It's using a new ParseCell routine that is ..


<font face=Tahoma New><SPAN style="color:#00007F">Sub</SPAN> ParseCell(celRef <SPAN style="color:#00007F">As</SPAN> Range, <SPAN style="color:#00007F">Optional</SPAN> NewWs <SPAN style="color:#00007F">As</SPAN> Worksheet)
    <SPAN style="color:#007F00">'get data</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> DataCol() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, DataRow() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Pos <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    DataCol = Split(Trim(celRef), Chr(13))
    <SPAN style="color:#00007F">If</SPAN> NewWs <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> NewWs = Worksheets.Add
    <SPAN style="color:#007F00">'write data</SPAN>
    <SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(DataCol) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(DataCol)
        <SPAN style="color:#00007F">With</SPAN> NewWs.Cells(i + 1, 1)
            Pos = InStr(1, DataCol(i), Chr(9))
            <SPAN style="color:#00007F">If</SPAN> Pos = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> SkipDataVal
            .value = Application.WorksheetFunction.Clean(Left(DataCol(i), Pos - 1))
            .Offset(0, 1).value = Application.WorksheetFunction.Clean( _
                Right(DataCol(i), Len(DataCol(i)) - Len(.value)))
SkipDataVal:
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> i
    NewWs.Cells.EntireColumn.AutoFit
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


This was also done on Excel 2003 with Outlook Exchange Server (ver?) running on a Windows 2000 server.

 

[deviation]

Sorry for my lack of response on this thread.. been away from the office.

I tried the file you sent me and still no good.. I really don't know what the issue is exactly..

It runs thru the procedures without issue, says "complete", but then does not post anything to the sheet..

I would really like to figure this one out but I fear that with the differences in systems that it may be difficult..

I will respond to your email with an email that I created using the sendmail procedure.. to avoid the possibility of it being different than what you are working with.

I did try this thru another email account (POP3) to see if the exchange server was doing something to it. Still no dice.

 

[Zack Barresse]

Try stepping through the code with the F8 key. Keep the VBE window only about half maximized so you can see what's going on in the sheet when you do it.

I'll look for the email. This is intriguing.

 

[deviation]

when stepping thru the code it creates this in column a on sheet("Tempinfo") Cell A1 ONLY..

Your sheets show the information broken into mulitple cells A1 - A16 (refrencing you workbook all worksheets).

This maybe where the disconnect is occuring

other than that it apears as though everthing is going well

 

[deviation]

that is the only thing I can think of when watching this procedure run.. watching the code reveils no other evidence of not working.. it never seems to hit the parse routine (the parse routine loops but doesn't result in any out put)

 

[deviation]

found something interesting over at VBA Express... it does import everything from the message however doesn't format it the way I am looking for.

it places an email in a sheet then provides a link to it in a TOC sheet.

It also works for emails that are selected not searching by subject..

http://www.vbaexpress.com/forum/showpost.php?p=9749&postcount=56

I am still working on it.. but I fel I am in way over my head here[/url]

 

[Zack Barresse]

when stepping thru the code it creates this in column a on sheet("Tempinfo") Cell A1 ONLY..

Your sheets show the information broken into mulitple cells A1 - A16 (refrencing you workbook all worksheets).

Yes, it should do that. It then takes that value and uses the Split function to create an array seperated by the line seperator (character 13). When you step through your code, try setting a watch for the array and viewing it's elements. Alternatively, you can just view the locals window instead of adding a seperate watch.

 

[deviation]

send you an email with a screen shot.

Alos played around with putting chr(10) in instead of chr(9) and it put everything in column B

B1 = Blank
B2 = Blank
B3 = Test1
B4 = Blank
B5 = Field2:
B6 = blank
B7 = Test2
B8 = blank
B9 = Field3:
B10 = balnk
B11= Test3

Not sure what to make of that... but its' something (I think)