Page 1 of 2 12 LastLast
Results 1 to 10 of 17

OUTLOOK VBA - exporting body from incoming message to excel

This is a discussion on OUTLOOK VBA - exporting body from incoming message to excel within the Excel Questions forums, part of the Question Forums category; Hey All, Hopefuly somebody will be able to help. I would like to have a macro which is able to ...

  1. #1
    New Member Arthur.J's Avatar
    Join Date
    Jun 2009
    Location
    Dublin, Ireland
    Posts
    44

    Default OUTLOOK VBA - exporting body from incoming message to excel

    Hey All, Hopefuly somebody will be able to help.

    I would like to have a macro which is able to transfer some information from incoming email to existing excel workbook (add new line). My code is working fine but only for first email received after restarting outlook. For every next email macro is running, opening workbook and not adding any information.

    Anyone is able to help?
    Thanks in advance.


    Code:
    Option Explicit
    
    Private WithEvents olInboxItems As Items
    
    Private Sub Application_Startup()
      Dim objNS As NameSpace
      Set objNS = Application.Session
      ' instantiate objects declared WithEvents
      Set olInboxItems = objNS.GetDefaultFolder(olFolderInbox).Items
      Set objNS = Nothing
    End Sub
    
    
    Private Sub olInboxItems_ItemAdd(ByVal Item As Object)
    
        Dim myXLApp As Excel.Application
        Dim myXLWB As Excel.Workbook
        Dim StrBody As String
        Dim TotalRows, i As Long
    
      
      
      On Error Resume Next
      Item.BodyFormat = olFormatPlain
      
      MsgBox (Item.Subject)
      
      Set myXLApp = New Excel.Application
      myXLApp.Visible = True
      Set myXLWB = myXLApp.Workbooks.Open("Z:\Leads_replies.xls")
      
      
      'adding to excel
        'Workbooks(myXLWB).Activate
        
        TotalRows = Sheets(1).Range("A55000").End(xlUp).Row
        i = TotalRows + 1
        MsgBox (myXLWB)
        
       
        StrBody = Item.Body
        Range("A" & i).Value = Format(Item.SentOn, "mm/dd/yyyy")
        Range("B" & i).Value = Item.SenderName
        Range("C" & i).Value = Item.To
        
        Range("D" & i).Value = Item.Body
      
      myXLWB.Close SaveChanges:=True
      
      myXLApp.Quit
      
      Item.Save
      Set Item = Nothing
    
      
    End Sub

  2. #2
    New Member Arthur.J's Avatar
    Join Date
    Jun 2009
    Location
    Dublin, Ireland
    Posts
    44

    Default Re: OUTLOOK VBA - exporting body from incoming message to excel

    Anyone?

  3. #3
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,062

    Default Re: OUTLOOK VBA - exporting body from incoming message to excel

    I haven't added all the code required, but this will put the body text of each email in your inbox into the variable strBody. It'll do all emails, not just unread ones though.
    You'll need to set a reference to the Outlook library.

    Code:
    Public Sub GetBody()
    
        Dim myOlapp As Outlook.Application
        Dim myNameSpace As Outlook.Namespace
        Dim myFolder As Outlook.MAPIFolder
        Dim myItem As Outlook.MailItem
        Dim strBody As String
         
        Set myOlapp = New Outlook.Application
        Set myNameSpace = myOlapp.GetNamespace("MAPI")
        Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
        
        For Each myItem In myFolder.items
            strBody = myItem.body
        
        Next myItem
    
    End Sub
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  4. #4
    New Member Arthur.J's Avatar
    Join Date
    Jun 2009
    Location
    Dublin, Ireland
    Posts
    44

    Default Re: OUTLOOK VBA - exporting body from incoming message to excel

    Darren,

    Thanks for reply, but actually this is not what I need - My code is putting body into variable - what I need is to add new line to excel spreadsheet when new email arrive. It works only with first email after restarting outlook.

  5. #5
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,062

    Default Re: OUTLOOK VBA - exporting body from incoming message to excel

    Ah, that's why the WithEvents statement was in there.

    Not sure how to do it then. I would say it might be easier to program it in Outlook to push the data to Excel when a new email arrives, but programming in Outlook is scary - never could get the hang of it.

    The forums on http://www.outlookcode.com/ might be better.
    Edit - just looked at their forum, lots of questions not many answers.
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  6. #6
    New Member Arthur.J's Avatar
    Join Date
    Jun 2009
    Location
    Dublin, Ireland
    Posts
    44

    Default Re: OUTLOOK VBA - exporting body from incoming message to excel

    Quote Originally Posted by Darren Bartrup View Post
    Ah, that's why the WithEvents statement was in there.

    Not sure how to do it then. I would say it might be easier to program it in Outlook to push the data to Excel when a new email arrives, but programming in Outlook is scary - never could get the hang of it.

    The forums on http://www.outlookcode.com/ might be better.
    Edit - just looked at their forum, lots of questions not many answers.
    Thanks for help anyway. It's actually very strange and can't figure it out. Macro is getting information from email when it arrives, but after first use is not able to write data to excel - is only opening and closing file.

  7. #7
    Board Regular sous2817's Avatar
    Join Date
    Feb 2008
    Location
    Raleigh, NC
    Posts
    2,262

    Default Re: OUTLOOK VBA - exporting body from incoming message to excel

    Not sure if this helps but have a look here:

    http://www.eggheadcafe.com/software/...l-arrival.aspx

    As Darren Bartrup suggested, it would probably be easier to run macro from Outlook when a email arrives rather than from Excel.

    The link above should give you the basic framework for a "run a script" rule, which you should then be able to modify to suit.

    Hope that helps,
    sous
    "Even a blind pig finds an acorn now and again"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    - Search Mr. Excel using Google

    - Search Mr. Excel using Bing

  8. #8
    New Member Arthur.J's Avatar
    Join Date
    Jun 2009
    Location
    Dublin, Ireland
    Posts
    44

    Default Re: OUTLOOK VBA - exporting body from incoming message to excel

    This macro is located in outlook and run every time when macro arrives. I will check your link. Thanks for help.

  9. #9
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,062

    Default Re: OUTLOOK VBA - exporting body from incoming message to excel

    Got this one working (you need to set a reference in Outlook to Excel)

    Place it in the ThisOutlookSession module in Outlook:
    Code:
    Private Sub Application_NewMail()
    
        Dim objNS As NameSpace
        Dim objFolder As MAPIFolder
        Dim myItem As MailItem
    
        Dim myXLApp As Excel.Application
        Dim myXLWB As Excel.Workbook
        Dim StrBody As String
        Dim TotalRows As Long, i As Long
        
        Set objNS = GetNamespace("MAPI")
        Set objFolder = objNS.GetDefaultFolder(olFolderInbox)
        Set myItem = objFolder.Items(1)
        
        Set myXLApp = New Excel.Application
        myXLApp.Visible = True
        Set myXLWB = myXLApp.Workbooks.Add
        
        TotalRows = Sheets(1).Range("A65536").End(xlUp).Row
        i = TotalRows + 1
        
        With myXLWB.Worksheets(1)
            .Cells(i, 1) = Format(myItem.SentOn, "mm/dd/yyyy")
            .Cells(i, 2) = myItem.SenderName
            .Cells(i, 3) = myItem.To
            .Cells(i, 4) = myItem.Body
        End With
        
    End Sub
    It will fire whenever any new mail item arrives, and will need changing so that myXLWB opens your workbook rather than creating a new workbook for each mail item.

    Edit: Had a look at the link that sous2817 posted. Looks like you can add a rule for a script to run? i.e. If email arrives from a certain address then run the script? Going to have to look into this further.
    Last edited by Darren Bartrup; Jan 11th, 2011 at 12:45 PM.
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  10. #10
    Board Regular sous2817's Avatar
    Join Date
    Feb 2008
    Location
    Raleigh, NC
    Posts
    2,262

    Default Re: OUTLOOK VBA - exporting body from incoming message to excel

    Yeah, sorry both. I thought it was something you were doing in Excel...

    Outlook programing is one of those things that I always wanted to dabble in, but never got too motivated.

    Darren, if you're ever looking for a bit of a side project, a macro I'd love in Outlook would be the ability to automatically book x minutes before and after a meeting to keep me from getting booked back, to back,to back, to back....any ideas?
    "Even a blind pig finds an acorn now and again"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    - Search Mr. Excel using Google

    - Search Mr. Excel using Bing

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com