How can i get formatted data from outlook to excel?
 

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!

How can i get formatted data from outlook to excel?
 

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



wrote in message
ups.com...
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!

How can i get formatted data from outlook to excel?
 

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


"Joerg" wrote in message
...
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



wrote in message
ups.com...
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!