Copy e-mail body from outlook and insert into excel

Status
Not open for further replies.
E

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.
 
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.
 
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)
 
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.
< Copying data from e-mail attachement to EXCEL file via macro | Appointment Delete/Change Recurrence >
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
Y Outlook 2016 (64-bit) Copy Local Cal. Events to Another Cal. with Modified Reminder time Using Outlook 2
Y Using The Non-Office Subscription Copy of Office Using Outlook 3
C Copy from one Profile to another Using Outlook 0
M "Attachment Detacher for Outlook" add in, does it update the server copy of the email? Using Outlook 1
C Outlook 365 Copy/Save Emails in Folder Outside Outlook to Show Date Sender Recipient Subject in Header Using Outlook 0
D Copy Appointment Body to Task Body Outlook VBA and Custom Forms 0
M copy field value to custom field Outlook VBA and Custom Forms 0
O In Agenda-view - How to copy an existing item months ahead or back? Using Outlook 0
C Move or copy from field to field Outlook VBA and Custom Forms 0
Z Copy specific email body text Outlook VBA and Custom Forms 0
B Need to Copy an email to a subfolder Outlook VBA and Custom Forms 2
O Outlook 365 - How to create / copy a new contact from an existing one? Using Outlook 5
S Copy Tasks/Reminders from Shared Mailbox to Personal Tasks/Reminders Outlook VBA and Custom Forms 0
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
Z 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 Using Outlook 0
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook Using Outlook 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

Similar threads

Back
Top