Solved

Need to get all the details in the mail to an excel with a macro in row wise

Posted on 2007-11-20
13
224 Views
Last Modified: 2010-04-21
Hi,

I send a mail to a vendor.for any printer problems that i have.Now as the mails have reched 100's it becomes difficult to see all.
I have the mail content like this.
=========================
Hi ,
                   
                   The  below  mentioned   printer  having the problem .The printout is coming with black patches .So please do the needful   as soon as possible..

Details:

printer model                --  Kyocera FS-3800            
printer s.no                   --  AFT44007867419            
customer name            --  (SB-3F)        
problem type                --  Black Pages
contact person name   -- SHarath.
contact number            -- 3073186349345234

Regards,
Sharath
=================================

So need all data to row wise in a excel sheet.

Any help...

Regards
Sharath
0
Comment
Question by:bsharath
  • 9
  • 4
13 Comments
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, bsharath.

That's easy enough to do if those lines are consistent (that is, if the always have the same format).  Do they?
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
This content is going to be same

printer model                --  Kyocera FS-3800            
printer s.no                   --  AFT44007867419            
customer name            --  (SB-3F)        
problem type                --  Black Pages
contact person name   -- SHarath.
contact number            -- 3073186349345234

Some time some extra fields...
If the mail has extra fields then create a new sheet and place them there...Or put it in the same sheet so that i can sort them later
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
Any help...
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Sorry to be so slow.  This should do it.  Select as many messages as you want, so long as they all match the pattern you gave, and run the macro.  It'll process all of them and put them into an Excel spreadsheet, one row per message.




Sub ParseMessage()

    Dim olkMessage As Outlook.MailItem, _

        arrLines As Variant, _

        varLine As Variant, _

        arrLine As Variant, _

        varKey As Variant, _

        excApp As Object, _

        excBook As Object, _

        excSheet As Object, _

        intIndex As Integer

    Set excApp = CreateObject("Excel.Application")

    Set excBook = excApp.Workbooks.Add()

    Set excSheet = excBook.Sheets(1)

    intIndex = 1

    For Each olkMessage In Application.ActiveExplorer.Selection

        arrLines = Split(olkMessage.Body, vbCrLf)

        For Each varLine In arrLines

            arrLine = Split(varLine, "--")

            If UBound(arrLine) = 1 Then

                varKey = Replace(arrLine(0), Chr(160), "")

                varKey = Trim(varKey)

                Select Case varKey

                    Case "printer model"

                        excSheet.Cells(intIndex, 1) = arrLine(1)

                    Case "printer s.no"

                        excSheet.Cells(intIndex, 2) = arrLine(1)

                    Case "customer name"

                        excSheet.Cells(intIndex, 3) = arrLine(1)

                    Case "problem type"

                        excSheet.Cells(intIndex, 4) = arrLine(1)

                    Case "contact person name"

                        excSheet.Cells(intIndex, 5) = arrLine(1)

                    Case "contact number"

                        excSheet.Cells(intIndex, 6) = Chr(34) & arrLine(1) & Chr(34)

                End Select

            End If

        Next

        intIndex = intIndex + 1

    Next

    'Change the path/filename on the following line as needed

    excBook.SaveAs "C:\eeTesting\BSharath.xls"

    excBook.Close

    Set excSheet = Nothing

    Set excBook = Nothing

    Set excApp = Nothing

    Set olkMessage = Nothing

End Sub

Open in new window

0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
Thanks a lot Can i get the headers also.
Is it possible to find which is left out on the selected mails if any.In sheet 2.Because of header mismatch.
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
Sorry for another addition.I need to get the Mail sent date also to the excel.Only then the whole file is ready.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 11

Author Comment

by:bsharath
Comment Utility
A little more help please...
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Where do you want the date to go, the begining, the end, somewhere in the middle?
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
The begining....
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
Comment Utility
Try this.
Sub ParseMessage()

    Dim olkMessage As Outlook.MailItem, _

        arrLines As Variant, _

        varLine As Variant, _

        arrLine As Variant, _

        varKey As Variant, _

        excApp As Object, _

        excBook As Object, _

        excSheet As Object, _

        intIndex As Integer

    Set excApp = CreateObject("Excel.Application")

    Set excBook = excApp.Workbooks.Add()

    Set excSheet = excBook.Sheets(1)

    intIndex = 1

    For Each olkMessage In Application.ActiveExplorer.Selection

        excSheet.Cells(intIndex, 1) = olkMessage.ReceivedTime

        arrLines = Split(olkMessage.Body, vbCrLf)

        For Each varLine In arrLines

            arrLine = Split(varLine, "--")

            If UBound(arrLine) = 1 Then

                varKey = Replace(arrLine(0), Chr(160), "")

                varKey = Trim(varKey)

                Select Case varKey

                    Case "printer model"

                        excSheet.Cells(intIndex, 2) = arrLine(1)

                    Case "printer s.no"

                        excSheet.Cells(intIndex, 3) = arrLine(1)

                    Case "customer name"

                        excSheet.Cells(intIndex, 4) = arrLine(1)

                    Case "problem type"

                        excSheet.Cells(intIndex, 5) = arrLine(1)

                    Case "contact person name"

                        excSheet.Cells(intIndex, 6) = arrLine(1)

                    Case "contact number"

                        excSheet.Cells(intIndex, 7) = Chr(34) & arrLine(1) & Chr(34)

                End Select

            End If

        Next

        intIndex = intIndex + 1

    Next

    'Change the path/filename on the following line as needed

    excBook.SaveAs "C:\eeTesting\BSharath.xls"

    excBook.Close

    Set excSheet = Nothing

    Set excBook = Nothing

    Set excApp = Nothing

    Set olkMessage = Nothing

End Sub

Open in new window

0
 
LVL 11

Author Closing Comment

by:bsharath
Comment Utility
Thanks a lot....For this excellent help....
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
0
 
LVL 11

Author Comment

by:bsharath
Comment Utility
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Find out how to use dynamic social media in email signatures with this top 10 DOs & DON’Ts.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now