Announcement

Collapse
No announcement yet.

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

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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, 01:23.

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


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

              Comment


              • #8
                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, 11:57. Reason: wrong code tag

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      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
                      will paste each line into a new row.

                      Comment


                      • #12
                        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

                        Comment


                        • #13
                          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()

                          Comment


                          • #14
                            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

                            Comment


                            • #15


                              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

                              Comment

                              Working...
                              X