Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
13 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 20319647
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
ID: 20319817
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
ID: 20348390
Any help...
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 76

Expert Comment

by:David Lee
ID: 20355449
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
ID: 20355487
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
ID: 20355519
Sorry for another addition.I need to get the Mail sent date also to the excel.Only then the whole file is ready.
0
 
LVL 11

Author Comment

by:bsharath
ID: 20379910
A little more help please...
0
 
LVL 76

Expert Comment

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

Author Comment

by:bsharath
ID: 20387222
The begining....
0
 
LVL 76

Accepted Solution

by:
David Lee earned 2000 total points
ID: 20400275
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
ID: 31410093
Thanks a lot....For this excellent help....
0
 
LVL 11

Author Comment

by:bsharath
ID: 21093142
0
 
LVL 11

Author Comment

by:bsharath
ID: 21093146
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
New style of hardware planning for Microsoft Exchange server.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

722 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