Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi
Im new in VBA and need help.

I have in Outlook a folder with Statusmails.

Every day must every employee send a Statusmail end of the day, what they did on that day.

I want to export this Emails to Excel for a better View.


The Email has always the same format.


The Table has two columns and several times in the email

Task          |  Export Excel
Planed-date   |  02.05.2013
deadline      |  01.05.2013
finished      |  no
time effort   |  3.5h
description   |  sdfjl fs dfjsf df aslfj sfdlk 
              |  f djasfsdkfsdjfldjfsj
              |  fas dfas sf a

Task          |  Computer
Planed-date   |  02.05.2013
deadline      |  01.05.2013
finished      |  no
time effort   |  3.5h
description   |  sdfjl fs dfjsf df aslfj sfdlk
              |  f djasfsdkfsdjfldjfsj
              |  fas dfas sf a


In Subject is always the date from today e.g. "20.03.2013"

For every employee should it make a separete Excelsheet

Thanks & regards chendu
Posted
v2
Comments
Maciej Los 20-Mar-13 16:37pm    
Which version of Excel, Outlook?
Where do you want to place your code: Excel?
chenduran10 20-Mar-13 16:45pm    
Outlook 2010, Excel 2010 and 64-bit version, if it plays a role
I thought the code should be in Outlook
Maciej Los 20-Mar-13 16:49pm    
See my answer, although my answer relates to MS Office 2003 Outlook objects. Change a reference to Outlook 2010.
In my opinion, you should place code in MS Excel.
chenduran10 22-Mar-13 4:38am    
I can now export it. Can you see my answer.
Maciej Los 22-Mar-13 5:53am    
I can't see it...
Where it is?

I have now this code to export it to Excel.
This code exports from the current Folder every Mail to Excel, but it pastes the body in 1 cell.
I would to take specified Items from the body, How can I do this?

VB
Sub Extract()
 On Error Resume Next
 Set myOlApp = Outlook.Application
 Set mynamespace = myOlApp.GetNamespace("mapi")
 Set myfolder = myOlApp.ActiveExplorer.CurrentFolder
 
Set xlobj = CreateObject("excel.application.14")
 xlobj.Visible = True
 xlobj.Workbooks.Add
 xlobj.Worksheets("Sheet1").Name = "Statusmail"
 
'Set the header
 xlobj.Range("a" & 1).Value = "Absender"
 xlobj.Range("a" & 1).Font.Bold = "True"
 xlobj.Range("b" & 1).Value = "Date"
 xlobj.Range("b" & 1).Font.Bold = "True"
 xlobj.Range("c" & 1).Value = "Task"
 xlobj.Range("c" & 1).Font.Bold = True
 xlobj.Range("d" & 1).Value = "Planed-date"
 xlobj.Range("d" & 1).Font.Bold = True
 xlobj.Range("e" & 1).Value = "deadline"
 xlobj.Range("e" & 1).Font.Bold = True
 xlobj.Range("f" & 1).Value = "finished"
 xlobj.Range("f" & 1).Font.Bold = True
 xlobj.Range("g" & 1).Value = "time effort"
 xlobj.Range("g" & 1).Font.Bold = True
 xlobj.Range("h" & 1).Value = "description"
 xlobj.Range("h" & 1).Font.Bold = True
 
For i = 1 To myfolder.Items.Count
  Set myitem = myfolder.Items(i)
  msgtext = myitem.Body
   
  xlobj.Range("a" & i + 1).Value = myitem.To
  xlobj.Range("b" & i + 1).Value = myitem.ReceivedTime
  xlobj.Range("c" & i + 1).Value = msgtext


 Next
 End Sub
 
Share this answer
 
Comments
Maciej Los 22-Mar-13 11:26am    
I'm glad that you found a way to export data from Outlook to Excel, but indeed, it's partial answer. That's why i give a 4.
I suggest you to post another question: how to recognize and read table content inside outlook's email (body of email). You need to provide more details about outlook email's format (html, rtf, text), because it's very important. Before you'll do that, try to read about:
BodyFormat property[^]
HTMLBody property[^]

Ending my comment, rate my answer and mark both answers as "solved" ;)
Start here: Welcome to MS Outlook 2003 VBA Language Reference[^], but before you'll start do anything, read about security (left window). Next, have a look here: MailItem Object[^].
Example code from above site:
VB
Set myOlApp = CreateObject("Outlook.Application")
Set myNamespace = myOlApp.GetNamespace("MAPI")
Set myFolder = myNamespace.GetDefaultFolder(olFolderInbox)
myFolder.Display
Set myItem = myFolder.Items(2)
myItem.Display
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900