Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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:

    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
    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
    Last edited by StephenR; September 29th, 2015 at 02:23.

    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
    384

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

    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
    MyNamespace.GetDefaultFolder(6).Folders("temp").Items(i).Move MyNamespace.GetDefaultFolder(6).Folders("Processed")
    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