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

 

[Zack Barresse]

Hello,

Exactly what information do you want to return from the mail messages found in your Inbox? Can you post a sample of the data layout you have in mind with the HTML Maker? (Link at bottom of page, read FAQ.)

 

[deviation]

right now there is nothing to creat with the HTML maker

I have an email that is generated from a user form and sent to me via email..

I would like to capture the information from the message body.

Currently the message body is in a HTML table (created by the macro), however I can change that..

The information layout in the email is as follows

18 Rows | 2 Columns

Field1 | Data1
Field2 | Data2
Field3 | Data3
Field4 | Data4
Field5 | Data5
Field6 | Data6
Field7 | Data7
Field8 | Data8
Field9 | Data9
Field10 | Data10
Field11 | Data11
Field12 | Data12
Field13 | Data13
Field14 | Data14
Field15 | Data15
Field16 | Data16
Field17 | Data17
Field18 | Data18

what I would like to do is grab the data points and copy them to a row in a sheet.. I can make headers for the columns.. kinda like a copy paste but since I want the information returned to a single row not exactly like a c/p.

I did find one macro here that is almost what I am looking for however I can not get it to work (don't know where I am going wrong on it (I have tried with the table and without)

I did manage to come across some code that is very close to what I am looking for, I tried to set up the email similar to the other users email (without tables), but I am still unable to return the information to the sheet (or userform).

http://www.mrexcel.com/board2/viewtopic.php?t=69520&highlight=createobject+outlook

I hope that helps

 

[Zack Barresse]

Currently the message body is in a HTML table (created by the macro)

Can you post that routine?

 

[deviation]

what routine are you referring to?

 

[deviation]

sorry.. here is the sendmail code

Sub SendMail()

'Dimension variables
    Dim oOutlookApp As Object, oOutlookMessage As Object
    Dim oFSObj As Object, oFSTextStream As Object
    Dim rngeSend As Range, strHTMLBody As String, strTempFilePath As String
    Dim myLastRow As Long
    Dim bodyText As String

    'Create an instance of Outlook (or use existing instance if it already exists
    Set oOutlookApp = CreateObject("Outlook.Application")

    'Create a mail item
    Set oOutlookMessage = oOutlookApp.CreateItem(0)
    With oOutlookMessage
        .To = "Some@some.com"
        .Subject = "Subject here: " & Format(Now, "mmmm dd, yyyy")
        
       
        bodyText = bodyText & "<table width=50%>"
        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field1:</td>"
        bodyText = bodyText & "<td>" & TextBox1.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field2:</td>"
        bodyText = bodyText & "<td>" & TextBox2.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field3:</td>"
        bodyText = bodyText & "<td>" & TextBox3.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field4:</td>"
        bodyText = bodyText & "<td>" & ComboBox1.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field5:</td>"
        bodyText = bodyText & "<td>" & ComboBox2.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field6:</td>"
        bodyText = bodyText & "<td>" & ComboBox3.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr></tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field8:</td>"
        bodyText = bodyText & "<td>" & ComboBox4.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field9:</td>"
        bodyText = bodyText & "<td>" & DTPicker1.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field10:</td>"
        bodyText = bodyText & "<td>" & DTPicker2.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field11:</td>"
        bodyText = bodyText & "<td>" & ComboBox5.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr></tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field13:</td>"
        bodyText = bodyText & "<td>" & TextBox11.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr></tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field15:</td>"
        bodyText = bodyText & "<td>" & TextBox12.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr></tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field17:</td>"
        bodyText = bodyText & "<td>" & TextBox13.value & "</td>"
        bodyText = bodyText & "</tr>"

        bodyText = bodyText & "<tr>"
        bodyText = bodyText & "<td>Field18:</td>"
        bodyText = bodyText & "<td>" & TextBox14.value & "</td>"
        bodyText = bodyText & "</tr>"


        bodyText = bodyText & "</table>

"
        
        .HTMLBody = bodyText    'Put all the information in the email
    End With
    oOutlookMessage.Display



End Sub

 

[deviation]

:: bump ::

 

[Davers]

Hmm...so, I assuming the e-mails you would like to import into Excel are formatted as html, and you'd like the entire table from the e-mail imported into a worksheet keeping the table structure from the e-mail?

Dave

 

[deviation]

That is not entirely correct.

Yes the email is formatted as HTMl and yes it is in a table (this structure can be changed).

however I do not want to keep the structure when bringit back into excel. I want the data to be entered on a row versus a column.

So if I have 4 emails in this structure.. I would end up with 4 rows of data in excel (row 1 of course woulf have the column headers and then rows 2-5 would contain the data extracted from the emails)

 

[deviation]

I have solved this on my own.. Thanks you everyone for your assistance with this