How can i get formatted data from outlook to excel?

S

syoung.chung

Hi, I found people here are really good at this..

I got hundreds of emails contain data in regular form,

and I wanna get this data into excel automatically.

Actually, right now I copy data from every email and paste to the txt
file by myself,

and then run VBA(excel) to get data in each cells in excel.

But i found it really hard to copy and paste hundreds of emails
everyday.

Plz help me!!

Thank you!
 
J

Joerg

I'm not sure what you want to achieve, but here's a start:
This Excel macro writes all mails of an Outlook folder "DataMails" into a
TXT file. You will get an error message if the mails are not plain text,
since VBA can't write binary files. It might be a better idea to tweak the
macro and import the mails directly into an Excel sheet.

Cheers
Joerg Mochikun

Sub WriteOutlookItemsToTextfile()
Dim ap, ns, mi, fd, outputfile
Dim x As Integer
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const fpath = "C:\temp\outputfile.txt" '<= Adapt name

Set fs = CreateObject("Scripting.FileSystemObject")
Set ap = CreateObject("Outlook.Application")
Set ns = ap.GetNameSpace("MAPI")
Set fd = ns.folders("Mailbox - MyName").folders("DataMails") '<= Adapt
names
fs.CreateTextFile fpath
Set outputfile = fs.GetFile(fpath).OpenAsTextStream(ForWriting,
TristateUseDefault)
For x = 1 To fd.items.Count
outputfile.Write fd.items(x).Body
Next x
End Sub
 
J

Joerg

Maybe even better approach: Use the export function of Outlook and export
all mails of a folder into a single textfile. Import this file to Excel,
delete unnecessary lines and keep what you need .... and you are almost
done.

Joerg Mochikun
 
Top