Copy e-mail body from outlook and insert into excel

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#1
I have an e-mail and want to copy its body into excel into separate cells divided by ";".

So I have a mail that looks like this:

hello; Bye Bye

And I want to copy into to excel so it looks like this:

A1 B1
Hello Bye Bye

Although this seems like a very generic problem I cannot find any help online.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#2
It should work - but will depend on how the Text to Columns feature is working. In my experience, you need to convert once manually and it will usually use the same conversion for future pastes.

Depending on how often you need to do this, you could use an Excel macro to paste it correctly.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#3
So my e-mail looks like this:

------------------------------------------------------------------------
Bla
bla
bla
bla
bla

------------------------------------------------------------------------
Code 0001:

Number Cust-Nr. ]Data Val.-Date Gesamtbetrag Belegw{hrung
===================================================================================
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR


Code 0002:

AVIS-Nummer Kunden-Nr. ]bertragungsdatum Val.-Datum Gesamtbetrag Belegw{hrung
===================================================================================
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR


The code I use is the following:

Option Explicit
Sub EmailText()
Dim ObjOutlook As Object
Dim MyNamespace As Object
Dim i As Integer
Dim j As Long
Dim abody() As String
Dim rstart As Range
'Declare Variables
Set ObjOutlook = GetObject(, "Outlook.Application")
'Find the outlook application
Set MyNamespace = ObjOutlook.GetNamespace("MAPI")
'Find the "NameSpace" -Current user environment from outlook
For i = 1 To MyNamespace.GetDefaultFolder(6).Folders("CP").Items.Count
'loop through all the items in the temp folder, defaultfolder(6) is the inbox.
abody = Split(MyNamespace.GetDefaultFolder(6).Folders("CP").Items(i).Body, Chr(13) & Chr(10))
'for each item, "split" the body of the email by linebreak into an array
Set rstart = ThisWorkbook.Sheets(1).Cells(65000, 1).End(xlUp).Offset(1, 0)
For j = 0 To UBound(abody)
'For each item in the array (i.e. each line) add the line to the first empty cell in column A of sheet1
rstart.Offset(j + 1, 0).Value = abody(j)
Next
Next

MyNamespace.GetDefaultFolder(6).Folders("temp").Items(i).Move MyNamespace.GetDefaultFolder(6).Folders("Processed")
'Move the email to the processing folder
Set ObjOutlook = Nothing
Set MyNamespace = Nothing
'Clear the object variables. I don't think this is needed, but it's good practice.
End Sub




the problem is that the code only copies this part into excel:

------------------------------------------------------------------------
Bla
bla
bla
bla
bla

------------------------------------------------------------------------
Code 0001:

Number Cust-Nr. ]Data Val.-Date Gesamtbetrag Belegw{hrung

Then a bug appears in line

rstart.Offset(j + 1, 0).Value = abody(j)
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#4
So the problem is solved by deleting Chr(10)

However, I have another question. Would it be possible to copy this part of the e-mail:

Code 0001:

Number Cust-Nr. ]Data Val.-Date Gesamtbetrag Belegw{hrung
===================================================================================
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR

into a specific range within an existing excel file

and the other part of the e-mail


Code 0002:

Number Cust-Nr. ]Data Val.-Date Gesamtbetrag Belegw{hrung
===================================================================================
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR
helo _000000 0000000000 06.03.2018 12.03.2018 1.000.000,00 EUR

into another rather of the same excel file?
 
Top