Click here to Skip to main content
13,006,180 members (66,689 online)
Rate this:
Please Sign up or sign in to vote.

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 1:09am
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?
chenduran10 22-Mar-13 9:47am
sorry, the solution was not visible. its the Solution 3
Rate this: bad
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.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

 End Sub
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" ;)
Rate this: bad
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)
Set myItem = myFolder.Items(2)

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170628.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2017
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