Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 20-Mar-13 2:09am
v2
Comments
Maciej Los at 20-Mar-13 16:37pm
   
Which version of Excel, Outlook?
Where do you want to place your code: Excel?
chenduran10 at 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 at 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 at 22-Mar-13 4:38am
   
I can now export it. Can you see my answer.
Maciej Los at 22-Mar-13 5:53am
   
I can't see it...
Where it is?
chenduran10 at 22-Mar-13 9:47am
   
sorry, the solution was not visible. its the Solution 3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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?
 
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
  Permalink  
Comments
Maciej Los at 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" ;)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:
Set myOlApp = CreateObject("Outlook.Application")
Set myNamespace = myOlApp.GetNamespace("MAPI")
Set myFolder = myNamespace.GetDefaultFolder(olFolderInbox)
myFolder.Display
Set myItem = myFolder.Items(2)
myItem.Display
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 428
1 Maciej Los 249
2 BillWoodruff 199
3 /\jmot 180
4 Suraj Sahoo | Coding Passion 150
0 OriginalGriff 8,484
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,639
3 Maciej Los 5,159
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100