Copy e-mail body from outlook and insert into excel

Status
Not open for further replies.

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
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
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
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
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?
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
K How to find specific header and copy the mail body Using Outlook 0
H Macro to Copy Specific content from Mail Body and Paste to Excel Outlook VBA and Custom Forms 4
L auto copy text in mail body to other part of the body Outlook VBA and Custom Forms 1
J Copy or Export Outlook Mail to Excel Outlook VBA and Custom Forms 6
F How can i copy the mail subject and the link to the mail to th clipboard? Outlook VBA and Custom Forms 3
L "sometimes" cant send mail, we have to copy and paste the message and resend? Using Outlook 2
G "on behalf of..." not showing on Sent e-mail copy, but is on Delivered copy Using Outlook 5
H Receive E-mail copy on multiple E-mail accounts Using Outlook 1
B Export from Outlook Express and Windows Live Mail fails to copy to Outlook 2010 Using Outlook 1
A Leave mail copy on server Exchange Server Administration 5
H Won't save a copy of every piece of sent mail? Using Outlook 7
A Cannot copy this folder because it may contain private items Using Outlook 0
C Copy Move item won't work Outlook VBA and Custom Forms 2
B VBA to convert email to task, insert text of email in task notes, and attach copy of original email Outlook VBA and Custom Forms 4
Commodore Move turns into "copy" Using Outlook 3
C Copy Outlook contact field value to another field Outlook VBA and Custom Forms 1
J Copy to calendar function no longer working in outlook 365 Using Outlook 5
F Copy and replace not update contact in another pst Using Outlook 0
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
Commodore Folders always closed in move/copy items dialog box Using Outlook 3
N Outlook rules don't create a copy for bcc'ed emails Using Outlook 3
geofferyh Outlook 2010 How to Copy Outlook Attachment to a Specific Folder? Outlook VBA and Custom Forms 3
S Custom Form, copy user field data to message body Outlook VBA and Custom Forms 12
R Copy Outlook Public Folders to a File Server Shared Folder Using Outlook 0
K Outlook Rules: Move a Copy Using Outlook 4
oliv- HOW TO COPY /USE FOLDERS ICONS Outlook VBA and Custom Forms 2
B Copy/Move Exchange inbox to Pop inbox Using Outlook 4
R Sending email copy (*.msg file) of sent email if subject line contains specific string. Outlook VBA and Custom Forms 1
O Copy mails from many subfolders to 1 foldr Using Outlook 2
K ind specific Subject line from outlook and copy the content of the email body to exce Outlook VBA and Custom Forms 0
G Copy Contact field to Appointment Custom Form Field Outlook VBA and Custom Forms 2
G How to Copy Multi Select Listbox Data to Appointment Outlook VBA and Custom Forms 3
Carrie Dickey Outlook 2016 created two calendars titled Calendar1 - appear to be a copy Using Outlook 2
P How to copy and append data from Outlook 2016 message into Excel 2016 workbook Using Outlook 0
Stilgar Relsik Create a rule to copy text from an email and paste it in the subject line. Using Outlook 1
R Macro to copy email to excel - Runtime Error 91 Object Variable Not Set Outlook VBA and Custom Forms 11
M How to keep reccurence during copy tasks to calendar? Using Outlook 1
Diane Poremsky Copy New Appointments to Another Calendar using VBA New Slipstick.com Articles 0
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook New Slipstick.com Articles 0
C Copy Task to Non-Microsoft PIM "Rainlendar" Using Outlook 0
G VBA Copy draft email to a new email - attachments not copided Using Outlook 7
C Copy email to excel runtime error 5020 Using Outlook 5
I Copy email from folder to folder - FAILS Using Outlook 5
M Copy new appointments created in multiple shared calendars to another exchange calendar Outlook VBA and Custom Forms 1
Q Why can't I copy image with embedded hyperlink from email to Word Using Outlook 0
I How to make a copy of a task Using Outlook 8
F copy data in Custom Field to other folder Outlook VBA and Custom Forms 2
A Outlook: copy & paste Outlook VBA and Custom Forms 9
V Copy and paste body and subject and send multiple emails Outlook VBA and Custom Forms 3
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook New Slipstick.com Articles 0

Similar threads

Top