Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 27

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

  1. #1
    Join Date
    1st June 2011
    Posts
    12

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    30th May 2011
    Location
    CH
    Posts
    283

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

    Try this:

    VB:
    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, “[COLOR="green"]Criteria[/COLOR]”) > 0 Then 
                [COLOR="green"]ThisWorkbook[/COLOR].Sheets("[COLOR="green"]YourSheet[/COLOR]").Cells(i, 1).Value = outMail.Body 
                i = i + 1 
            End If 
        Next olMail 
         
        Set olFldr = Nothing 
        Set olNs = Nothing 
        Set olApp = Nothing 
         
    End Sub 
    
    
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    1st June 2011
    Posts
    12

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    30th May 2011
    Location
    CH
    Posts
    283

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    1st June 2011
    Posts
    12

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    30th May 2011
    Location
    CH
    Posts
    283

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    1st June 2011
    Posts
    12

    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    6th September 2007
    Location
    Palmerston North New Zealand
    Posts
    360

    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"

    VB:
    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 
    
    
    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.
    Last edited by richadj4; June 7th, 2011 at 12:57. Reason: wrong code tag

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    30th May 2011
    Location
    CH
    Posts
    283

    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.

    VB:
    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 
    
    
    "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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    1st June 2011
    Posts
    12

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Send Email & Write to Email Body
    By jillyb in forum EXCEL HELP
    Replies: 2
    Last Post: November 11th, 2006, 04:21
  2. email active range on worksheet to body of outlook 2003 email
    By mjschukas in forum Excel and/or Email Help
    Replies: 4
    Last Post: January 31st, 2006, 02:49
  3. Replies: 3
    Last Post: April 22nd, 2004, 08:16
  4. Email: Paste into Email Body
    By privatesub in forum Excel and/or Email Help
    Replies: 4
    Last Post: November 27th, 2003, 18:42
  5. Email: Formating Email Body w/ VBA excel / Outlook
    By JJacob in forum Excel and/or Email Help
    Replies: 2
    Last Post: October 27th, 2003, 09:25

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno