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

  • 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

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


    Try this:



    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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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"



    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.

  • 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.



    "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

  • 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

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


    Issue 4:


    will paste each line into a new row.

  • 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

  • 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()
  • Re: VBA code to open specific email and copy the email body to excel and save it.


    Code re-posted with comments for explanation

  • 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

  • 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

  • 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.

  • 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

  • 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.

  • 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!! :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!