VBA code to open specific email and copy the email body to excel and save it.

SarahYeoh · Jun 2nd 2011

Hi all,
I am a newbie in VBA and macro. I recently found out that VBA and macro can do wonders. I will be receiving email with same subject name and format everyday. what i will normally do is opening my outlook and search for that email, using 'Ctrl + A' and 'Ctrl +C' to copy everything(because the data is kind of lenghty) and open an excel and paste it in and save it. that will automatically update in my macro in excel. but this gv me alot of troubles because i got alot of emails to go through. it will be great is VBA and macro can do this automatically for me everyday. i only wanted it to have the normal copy paste function and not inserting the whole email body to 1 small column. Since i am new in programming, step by step instructions will be needed. Thanks for reading my post and hope to get some reply soon. Good Day.

i am currently using microsoft office 2007.

Best Regards,
SarahYeoh

Attilas · Jun 2nd 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Try this:

Code

Sub GetFromInbox()


    Dim olApp As Outlook.Application
    Dim olNs As Outlook.Namespace
    Dim olFldr As Outlook.MAPIFolder
    Dim olItms As Outlook.Items
    Dim olMail As Variant
    Dim i As Long


    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace(”MAPI”)
    Set olFldr = olNs.GetDefaultFolder(olFolderInbox)
    Set olItms = olFldr.Items
    
    olItms.Sort “Subject”
    
    i = 1


    For Each olMail In olItms
        If InStr(olMail.Subject, “Criteria") > 0 Then
            ThisWorkbook.Sheets("YourSheet").Cells(i, 1).Value = outMail.Body
            i = i + 1
        End If
    Next olMail


    Set olFldr = Nothing
    Set olNs = Nothing
    Set olApp = Nothing


End Sub

Display More

I got this directly from Dick Kusleikas site:
http://www.dicks-clicks.com/excel/olRetrieving.htm
I haven't had the time to test it, but it should work.

You can paste this into a standard code module. Make sure you adjust the parameters you will search for, the workbook and sheet you want to paste it into (all in green in the code). For the moment this will go through your inbox and check the subject of emails for a certain string (which you specify) and it will paste the code into cell A1 (1st email) then A2 (2nd email), A3 (3rd) etc. in your worksheet.

Make sure you activate the reference to Microsoft Oulook 12.0 Object library in the VBE (Tools > References)

Tell me how you get along

Regards

Attila

SarahYeoh · Jun 3rd 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Hey, thanks for the quick reply. what can i do if my subject name is long? eg:"SBN Auto Generation Report". And also that i do not want the email body to squeeze into one column. What can i do if i only wanted it to hv the normal copy and paste function. When i copy my data to excel, it will only fill column A but from row 1 to row 3343 which is kind of lenghty. i will be moving the mail to a specific folder after i copy the data to my excel macro. Any help? or should i elebrate more? Thanks and good day.

Regards,
SarahYeoh

Attilas · Jun 3rd 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

You can change "Criteria" marked in green in the above code and replace it with "SBN Auto Generation Report".
Could you elaborate more? Maybe if you upload a sample workbook with what you want to be done it would be easier for me to follow you

Cheers

Attila

SarahYeoh · Jun 3rd 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Hi,
i try to change to "SBN Auto Generation Report" but an error occurs and thats why i ask for help in that issue again. the data is kinda personal so i dun think i can upload to here, i'm sorry. How about thinking my data as pages of different short sentences, eg:

date and time: 01-06-2011 06:30am
place: SBN
Rate: A 100 12 13 106 177
B 102 25 18 27 177
C 10 15 33 106 177
( X 200)

date and time: 01-06-2011 08:30am
place: SBN
Rate: A 100 12 13 106 177
B 102 25 18 27 177
C 10 15 33 106 177
( X 200)

As you can see, every sentence end with "Enter" so when we copy and paste all of this to excel it will fill up rows of column A only. i will recieve this mail once a day even at saturday and sunday. so i do hope i can find a solution to solve it so that i can rest at weekend. Thanks alot for willing to help and your help is deeply appreciated.

Regards,
SarahYeoh

Attilas · Jun 3rd 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Is "SBN Auto Generation Report" always in the subject?
I'll take look a it again a bit later. In the meantime maybe someone would like to chip in?

Regards

Attila

SarahYeoh · Jun 7th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Hi all,

Any help would be good. Thanks. Hoping to get some reply soon.

Regards,
SarahYeoh

richadj4 · Jun 7th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Couple of "suggestions".

Firstly you will find this a lot easier if the emails you are attempting to copy are in a specific location. so for example if you create a folder called "temp" in your inbox, and an outlook rule to move all message with the subject "whatever" into this folder, you can run a macro to copy the information you need from every email in this folder, which has the advantage of speed (It won't have to check your entire inbox to look for the relevant emails).

If the emails are all in a folder (called "temp" in this instance) then the following code will copy the information from each email to "Sheet1"

Code

Function EmailText() As String
Dim ObjOutlook As Object
Dim MyNamespace As Object
Dim i As Integer
Set ObjOutlook = GetObject(, "Outlook.Application")
Set MyNamespace = ObjOutlook.GetNamespace("MAPI")
For i = 1 To MyNamespace.getdefaultfolder(6).Folders("temp").items.Count
Sheet1.Cells(i, 1).Value = MyNamespace.getdefaultfolder(6).Folders("temp").items(i).body
Next
Set ObjOutlook = Nothing
Set MyNamespace = Nothing
End Function

Display More

however, you still have a couple of "issues".
Issue 1 - Although the lines are separated by an "enter", and copy and paste will correctly fill columns like this, vba will not. It will put the entire contents of the email body into cell A1 (which is probably not what you want)
Issue 2 - when you run this, it will ask you if you will allow excel to talk to your outlook, which means you will have to be there to push the ok button
Issue 3 - This will always copy every email in the folder, so you will have to delete or move them after you have the relvant information
Issue 4 - You probably don't want the information always going into A1, but without more information, I don't know where you do want it.

Attilas · Jun 7th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

With this addition, you don't have to worry about "Issue 3". This code works, given that you have a folder named "Processed", where all the processed emails will go.

Code

Function EmailText() As String
Dim ObjOutlook As Object
Dim MyNamespace As Object
Dim i As Integer
Set ObjOutlook = GetObject(, "Outlook.Application")
Set MyNamespace = ObjOutlook.GetNamespace("MAPI")
For i = 1 To MyNamespace.GetDefaultFolder(6).Folders("temp").Items.Count
Sheet1.Cells(i, 1).Value = MyNamespace.GetDefaultFolder(6).Folders("temp").Items(i).Body
[COLOR=darkred]MyNamespace.GetDefaultFolder(6).Folders("temp").Items(i).Move MyNamespace.GetDefaultFolder(6).Folders("Processed")
[/COLOR]Next
Set ObjOutlook = Nothing
Set MyNamespace = Nothing
End Function

Display More

"Issue 1" is solveable if you could provide following answers:
-Is the layout of the emails always the same?
-Are there always the same number of characters on each line?
"Issue 2" is solveable with extensive coding, but not worth it as the code could be misinterpreted by certain antivirus programs
"Issue 3" solved with above code
"Issue 4" is solveable, given that you supply us with a few more details Sarah

Regards

Attila

SarahYeoh · Jun 9th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Hi,

Thanks for all the reply.
"Issue 1":
- the layout will always be the same
-always same numbers of character for the titles but the figure will be change
"Issue 2":
- which means no automated work in reading the mails??
"Issue 4":
- i need the data to be paste line by line in excel b'cuz in the macro i did for excel, there are tons of equation to calculate out all the final data that i need and each line contain different type of calculation.

sorry if i cant gv you more details cause i am not sure wut i need to provide (since i am lousy in VBA) i am trying to learn up all the command as quikly as i can. Sorry for the inconvinient.

If it cant paste line by line in excel, will copying the email body and saving it as .csb file instead of .txt file help?? juz to save the email body in its origin format. Really thanks for lending me ur time to solve my problem.

Regards,
SarahYeoh

richadj4 · Jun 9th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Issue 4:

Code

Option Explicit
Function EmailText() As String
Dim ObjOutlook As Object
Dim MyNamespace As Object
Dim i As Integer
Dim j As Long
Dim abody() As String
Set ObjOutlook = GetObject(, "Outlook.Application")
Set MyNamespace = ObjOutlook.GetNamespace("MAPI")
For i = 1 To MyNamespace.GetDefaultFolder(6).Folders("temp").Items.Count
  abody = Split(MyNamespace.GetDefaultFolder(6).Folders("temp").Items(i).Body, Chr(13) & Chr(10))
  For j = 0 To UBound(abody)
    Sheet1.Cells(65000, 1).End(xlUp).Offset(1, 0).Value = abody(j)
  Next
  MyNamespace.GetDefaultFolder(6).Folders("temp").Items(i).Move MyNamespace.GetDefaultFolder(6).Folders("Processed")
Next
Set ObjOutlook = Nothing
Set MyNamespace = Nothing
End Function

Display More

will paste each line into a new row.

SarahYeoh · Jun 13th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Hi,

i'm sorry for the late reply. There are a few stupid questions that i wanted to ask you all. the code that u supply, i cant understand fully that how it runs so when there are error occurs, i cant manage to handle it myself. i have been studying days on VBA coding but i stil cant solve it. if can, can u guide me through ur code? really sorry for the trouble.

the following is the error occur when i try to run the code.
compile error: variable not define
Function EmailText() As String

Best Regards,
SarahYeoh

richadj4 · Jun 13th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Sorry, my bad, copied from something else I was using. Just change that line to

Code

Sub EmailText()

richadj4 · Jun 13th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Code re-posted with comments for explanation

Code

Option Explicit
Sub EmailText()
Dim ObjOutlook As Object
Dim MyNamespace As Object
Dim i As Integer
Dim j As Long
Dim abody() As String
'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("temp").Items.Count
'loop through all the items in the temp folder, defaultfolder(6) is the inbox.
  abody = Split(MyNamespace.GetDefaultFolder(6).Folders("temp").Items(i).Body, Chr(13) & Chr(10))
  'for each item, "split" the body of the email by linebreak into an array
  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
    Sheet1.Cells(65000, 1).End(xlUp).Offset(1, 0).Value = abody(j)
  Next
  MyNamespace.GetDefaultFolder(6).Folders("temp").Items(i).Move MyNamespace.GetDefaultFolder(6).Folders("Processed")
  'Move the email to the processing folder
Next
Set ObjOutlook = Nothing
Set MyNamespace = Nothing
'Clear the object variables.  I don't think this is needed, but it's good practice.
End Sub

Display More

SarahYeoh · Jun 15th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Hi richadj4,

Thanks for your patients and helps. but i still get some problem when i try to run it.

Compile error:
Variable not defined

Sub EmailText() --> it was highlight in yellow. ( does it means that this line cause all the problem)

Sheet1 --> does not get highlight in yellow but it did seem cause some problem too.

Best Regards,
SarahYeoh

SarahYeoh · Jun 17th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Hi all,

Sorry to repost~ but my question cant be solve yet so i hope to get some help here. Thanks alot. Waiting for the kind reply.

Best Regards,
SarahYeoh

richadj4 · Jun 17th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

sorry, missed your earlier response,

I think the highlight on the sub indicates there is a problem with the sub, not that that line is the problem. Sheet1 is the codename for the first worksheet (usually) you have presumably changed it to something else, or deleted sheet1 at some point. In the righthand pane of the the vba editor under your project you will have a list of all the objects. some of them will be worksheets, and will have code names, followed by shett names in brackets. The sheet name is what you can see on the tab in excel, the code name is what excel uses internally to identify that sheet. Find the sheet that you actually want stuff to on and change "sheet1" to the code name of this this sheet.

SarahYeoh · Jun 20th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Hi richadj4,

Thanks for ur reply. Does it means that the code did not help in opening a fix excel (with fixed name)?? but my code cant run, stil the same problem.

Best Regards,
SarahYeoh

richadj4 · Jun 20th 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

In the VBA editor, choose debug - Compile.

This will identify exactly where the problem is. If it is with the "Sheet1" variable, then you need to change this variable name. either follow the instructions in the post above, or replace with this

Code

thisworkbook.sheets(1)

If you are still having no luck, upload an example workbook.

SarahYeoh · Jun 21st 2011

Re: VBA code to open specific email and copy the email body to excel and save it.

Hi richadj4,

Thanks alot for your quick reply.
Two more things i am interested to add in the code:-
a) can i fix the font and font size when we copy the email body?
b) can i copy also the space between paragraph?
c) can this macro runs automatically?
d) can the text that i copy to excel save by it's own?

Best Regards,
SarahYeoh

*In happy mode cause learn new commands and also a new and interesting language* Thanks alot for all the help given to me!!

VBA code to open specific email and copy the email body to excel and save it.

Participate now!

Share