+ Reply to Thread
Results 1 to 11 of 11

Export outlook body data to excel

Hybrid View

  1. #1
    Registered User
    Join Date
    12-31-2003
    Posts
    14

    Export outlook body data to excel

    I have been looking for a macro which will extract data from the currently highlighted email in outlook and append it to a specified worksheet in an Excel workbook.

    If the incoming body contains

    Name: Joe Bloggs
    Age:32

    then the macro should send the data to the next empty row in the sheet.

    Does anyone know how I can do this or provide a link with some examples to work from.

    tia

  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Export outlook body data to excel

    is this for a personal solution for or has it got to work within a corporate network and exchange server?

    How will the workbook be identified?
    Is it always the same data?
    Is the incoming email a fixed format?

    It all affects how to tackle the solution.


    click on the * Add Reputation if this was useful or entertaining.

  3. #3
    Registered User
    Join Date
    12-31-2003
    Posts
    14

    Re: Export outlook body data to excel

    The solution is a personal one on a stand alone PC.
    The data is always in the same format and the only changes are in the values of the data.

  4. #4
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Export outlook body data to excel

    OK,
    but I can only be half helpful as I don't have outlook installed on this PC.

    Basically there are two options: 1. write an excel macro to get the data from Outlook and 2. write an Outlook macro to push the data to Excel.

    in your case you will be better off with an Outlook Macro. You will need to add Excel as a reference in tools. and makesure you decalre Range object with the full class name or you will have problems.

    I think when you install Outlook it doesn't install all the programming part unless you specifically request it.



    If you are still stuck in a week ask me again I might be back with another PC.

  5. #5
    Registered User
    Join Date
    03-31-2010
    Location
    Chicag, United States
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Export outlook body data to excel

    I have a similar issue but have never created a macro for Outlook or Excel.

    I have 2 forms on the web that are sent to an email box/folder. When I export them chosing only the body as a Windows Tab Delimited text file there is a lot of other information that goes into the cells not just the form field information. Here are the two forms.

    Form 1
    First Name:
    Last Name:
    Your e-Mail Address:
    Program Confirmation Number:


    Form 2
    First Name:
    Last Name:
    Your e-Mail Address:
    Participation Verification Code:

    It would be great to be able to append the information into Excel but I would settle for just getting the field information cleanly into columns. If anyone has a macro that I can use as a starting point it would be very helpful.

    Thanks,

    Denny

  6. #6
    Registered User
    Join Date
    12-31-2003
    Posts
    14

    Re: Export outlook body data to excel

    I have trawled the net looking for anything that would let me move just one item of data.
    It would give me a start and I would have something to work with.

    I can't believe that so few people would find this useful that no-one would create such a macro.

  7. #7
    Registered User
    Join Date
    11-09-2008
    Location
    germany
    Posts
    74

    Re: Export outlook body data to excel

    If you do this from Outlook you will not run into security issues. If you do this from Excel or some other program Office will throw up warning messages every time you try to read the body of the email.

    This would work from Outlook - but you could change it to work from excel as well

    'Sub sendtoaccess()
    
    'Dim out_app As Outlook.Application
    'Dim out_Exp As Outlook.Explorer
    'Dim out_Sel As Outlook.Selection
    'Dim out_mail As Outlook.MailItem
    
    Set out_app = Application
    
    'get the active explorer
    Set out_Exp = out_app.ActiveExplorer
    '
    If out_Exp.CurrentFolder.WebViewOn = False Then
            Set out_Sel = out_Exp.Selection
        Else
           MsgBox "wrong item type"
            Set out_Exp = Nothing
            Exit Sub
       End If
    
    ''first make sure they have only choosen one item if not exit sub
    If out_Sel.Count > 1 Then
        MsgBox "more than one item"
        Exit Sub
    Else
        If out_Sel.Count = 0 Then
        MsgBox "nothing choosen"
        Exit Sub
        End If
    End If
    '
    ''check that it is in fact an email that has been choosen
    If Not out_Sel.Item(1).Class = olMail Then
        MsgBox "not an email"
        Exit Sub
    End If
    '
    '
    'next try to get the email item - if there is an error 13 then it is encrypted - you may not need this one
    On Error Resume Next
    
    Set out_mail = out_Sel.Item(1)
    
    If Err.Number = 13 Then
        Err.Clear
        MsgBox "email encrypted"
        Exit Sub
    End If
    '
    '
    ''***********************************
    ''now you have your email item so you can get any property you want
    ''I will just show an example
    ''
    '
    Dim str_emailBody As String
    Dim str_emailSubject As String
    '
    str_emailBody = out_mail.Body
    str_emailSubject = out_mail.Subject
    'add your others here.  Then you need to decide how to get this to excel.  
    '
    you could even add this as a button in outlook. Then just select a message and press the button.

  8. #8
    Registered User
    Join Date
    04-04-2013
    Location
    New York, United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Export outlook body data to excel

    We are holding an event. To register members will fill out a form online which will be emailed to me. I am looking for a macro code to automatically export the registration information into a excel spreadsheet. Here is a copy of what the email looks like when I receive it.
    The submitted value of field named "Member Name:" is:
    -------------------------------------------------------
    Test Run
    -------------------------------------------------------

    The submitted value of field named "Member #:" is:
    -------------------------------------------------------
    m115
    -------------------------------------------------------

    The submitted value of field named "Club:" is:
    -------------------------------------------------------
    PRC
    -------------------------------------------------------

    The submitted value of field named "Phone:" is:
    -------------------------------------------------------
    555-555-555
    -------------------------------------------------------

    The submitted value of field named "Email:" is:
    -------------------------------------------------------
    test@test.com
    -------------------------------------------------------

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,937

    Re: Export outlook body data to excel

    kjm1, Welcome to the Forum.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

  10. #10
    Registered User
    Join Date
    06-05-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Export outlook body data to excel

    see if this helps..
    Sub Mail_Selection_Range_Outlook_Body()
    
        Dim rng As Range
        Dim OutApp As Object
        Dim OutMail As Object
    
        Set rng = Nothing
        On Error Resume Next
        
        Set rng = Sheets("Sheet1").Range("B4:H5").SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    
        If rng Is Nothing Then
            MsgBox "The selection is not a range or the sheet is protected" & _
                   vbNewLine & "please correct and try again.", vbOKOnly
            Exit Sub
        End If
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
        
    
    Dim toEmail As String
    
    toEmail = Range("K4").Value
    
    
    Dim ccEmail As String
    
    ccEmail = Range("K5").Value
    
    
    
    
    Dim StrBody As String
    
    StrBody = Sheets("Quality Scorecard").Range("C2").Value & "," & "<br>" & _
    "Your case quality audit has been completed. Please find below the feedback." & "<br>" & "<br>" & "<br>"
    
    
    
    
    With OutMail
            .To = toEmail
            .CC = ccEmail
            .BCC = ""
            .Subject = "Exception!"
            .HTMLBody = StrBody & RangetoHTML(rng)
    
            '.Send   'or use .Display
             .Display
        End With
        On Error GoTo 0
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    Function RangetoHTML(rng As Range)
    ' Changed by Ron de Bruin 28-Oct-2006
    ' Working in Office 2000-2010
        Dim fso As Object
        Dim ts As Object
        Dim TempFile As String
        Dim TempWB As Workbook
     
        TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
     
        'Copy the range and create a new workbook to past the data in
        rng.Copy
        Set TempWB = Workbooks.Add(1)
        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial xlPasteValues, , False, False
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
     
        'Publish the sheet to a htm file
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
     
        'Read all data from the htm file into RangetoHTML
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
     
        'Close TempWB
        TempWB.Close savechanges:=False
     
        'Delete the htm file we used in this function
        Kill TempFile
     
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function
    Last edited by arlu1201; 06-05-2013 at 06:55 AM.

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Export outlook body data to excel

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1