Hi,

I receive everyday email with data...
I want to import this data into my excel sheet..


How can I do this?

Thanks for help!


Carl

Charlie

Start here

www.dicks-clicks.com

and post back with more specific questions if you have them.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Charlie" wrote in message
...


Hi,

I receive everyday email with data...
I want to import this data into my excel sheet..


How can I do this?

Thanks for help!


Carl

On Thu, 6 Nov 2003 12:27:18 -0600, "Dick Kusleika"
wrote:

Charlie

Start here

www.dicks-clicks.com

and post back with more specific questions if you have them.


Dick,

The "retrieving EMail" code worked great for me with XP.

How should I change the "olMail.ReceivedTime" in the line

ActiveSheet.Cells(i, 1).Value = olMail.ReceivedTime

to copy the whole body of the email.

TIA

Ron

Ron

ActiveSheet.Cells(i, 1).Value = olMail.Body

will copy the entire body of the email into that cell. I don't know if
there are limits on how much text you can bring over.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Ron" wrote in message
...
On Thu, 6 Nov 2003 12:27:18 -0600, "Dick Kusleika"
wrote:

Charlie

Start here

www.dicks-clicks.com

and post back with more specific questions if you have them.


Dick,

The "retrieving EMail" code worked great for me with XP.

How should I change the "olMail.ReceivedTime" in the line

ActiveSheet.Cells(i, 1).Value = olMail.ReceivedTime

to copy the whole body of the email.

TIA

Ron

Dick,




The email code also worked fine for me...

Except I want the email body not in 1 cell, but I want to have each
line of the body in a different row. Is that possible?

Thanks




"Dick Kusleika" wrote in message ...
Ron

ActiveSheet.Cells(i, 1).Value = olMail.Body

will copy the entire body of the email into that cell. I don't know if
there are limits on how much text you can bring over.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Ron" wrote in message
...
On Thu, 6 Nov 2003 12:27:18 -0600, "Dick Kusleika"
wrote:

Charlie

Start here

www.dicks-clicks.com

and post back with more specific questions if you have them.


Dick,

The "retrieving EMail" code worked great for me with XP.

How should I change the "olMail.ReceivedTime" in the line

ActiveSheet.Cells(i, 1).Value = olMail.ReceivedTime

to copy the whole body of the email.

TIA

Ron

charelke

The Body property is just a string, so you can manipulate it like any other
stirng. Here's an example

Sub emaillines()

Dim ol As Outlook.Application
Dim ns As Outlook.NameSpace
Dim fldr As Outlook.MAPIFolder
Dim mi As Outlook.MailItem
Dim stBody As String
Dim LineBreak As Long
Dim i As Long

Set ol = New Outlook.Application
Set ns = ol.GetNamespace("MAPI")
Set fldr = ns.GetDefaultFolder(olFolderInbox)
Set mi = fldr.Items(78)

stBody = mi.Body
LineBreak = 1
i = 1

Do
ActiveSheet.Cells(i, 1).Value = _
Mid(stBody, LineBreak, InStr(LineBreak, stBody, Chr(10)) -
LineBreak)
LineBreak = InStr(LineBreak + 1, stBody, Chr(10)) + 1
i = i + 1
Loop Until LineBreak = 0 Or LineBreak Len(stBody)

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"charelke" wrote in message
om...
Dick,




The email code also worked fine for me...

Except I want the email body not in 1 cell, but I want to have each
line of the body in a different row. Is that possible?

Thanks




"Dick Kusleika" wrote in message
...
Ron

ActiveSheet.Cells(i, 1).Value = olMail.Body

will copy the entire body of the email into that cell. I don't know if
there are limits on how much text you can bring over.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Ron" wrote in message
...
On Thu, 6 Nov 2003 12:27:18 -0600, "Dick Kusleika"
wrote:

Charlie

Start here

www.dicks-clicks.com

and post back with more specific questions if you have them.


Dick,

The "retrieving EMail" code worked great for me with XP.

How should I change the "olMail.ReceivedTime" in the line

ActiveSheet.Cells(i, 1).Value = olMail.ReceivedTime

to copy the whole body of the email.

TIA

Ron

Dick,

In XP and with the body of the email in cell "AI" I tried the
"emaillines" sub but ran into "Run-time error '-2147352567
(80020009)':

Array index out of bounds."

How to fix please.

TIA

Ron

On Wed, 12 Nov 2003 08:13:28 -0600, "Dick Kusleika"
wrote:

charelke

The Body property is just a string, so you can manipulate it like any other
stirng. Here's an example

Sub emaillines()

Dim ol As Outlook.Application
Dim ns As Outlook.NameSpace
Dim fldr As Outlook.MAPIFolder
Dim mi As Outlook.MailItem
Dim stBody As String
Dim LineBreak As Long
Dim i As Long

Set ol = New Outlook.Application
Set ns = ol.GetNamespace("MAPI")
Set fldr = ns.GetDefaultFolder(olFolderInbox)
Set mi = fldr.Items(78)

stBody = mi.Body
LineBreak = 1
i = 1

Do
ActiveSheet.Cells(i, 1).Value = _
Mid(stBody, LineBreak, InStr(LineBreak, stBody, Chr(10)) -
LineBreak)
LineBreak = InStr(LineBreak + 1, stBody, Chr(10)) + 1
i = i + 1
Loop Until LineBreak = 0 Or LineBreak Len(stBody)

End Sub

Ron

On which line?

If it was this one

Set mi = fldr.Items(78)

then you may not have 78 emails in your inbox. You need to set mi equal to
the email that you want. For tips on this see

http://www.dicks-clicks.com/excel/olRetrieving.htm

This sub takes the body from the email and parses into lines on a sheet.
Having the body in A1, doesn't matter, because it's not taking the body from
the worksheet, rather, straight from the email.

Post back if that doesn't answer your question.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Ron" wrote in message
...
Dick,

In XP and with the body of the email in cell "AI" I tried the
"emaillines" sub but ran into "Run-time error '-2147352567
(80020009)':

Array index out of bounds."

How to fix please.

TIA

Ron

On Wed, 12 Nov 2003 08:13:28 -0600, "Dick Kusleika"
wrote:

charelke

The Body property is just a string, so you can manipulate it like any
other
stirng. Here's an example

Sub emaillines()

Dim ol As Outlook.Application
Dim ns As Outlook.NameSpace
Dim fldr As Outlook.MAPIFolder
Dim mi As Outlook.MailItem
Dim stBody As String
Dim LineBreak As Long
Dim i As Long

Set ol = New Outlook.Application
Set ns = ol.GetNamespace("MAPI")
Set fldr = ns.GetDefaultFolder(olFolderInbox)
Set mi = fldr.Items(78)

stBody = mi.Body
LineBreak = 1
i = 1

Do
ActiveSheet.Cells(i, 1).Value = _
Mid(stBody, LineBreak, InStr(LineBreak, stBody, Chr(10)) -
LineBreak)
LineBreak = InStr(LineBreak + 1, stBody, Chr(10)) + 1
i = i + 1
Loop Until LineBreak = 0 Or LineBreak Len(stBody)

End Sub