Results 1 to 10 of 10

Thread: Export email from Outlook 2013 to excel sheet

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    5

    Export email from Outlook 2013 to excel sheet

    Hi,

    I'd like to export the email subject title, the sender info and the email date from Outlook 2013 to an Excel sheet. In fact, I'd only like to pull emails from a certain sender with a certain format, but to start with the broader task of pulling all new emails, I found the following code at this location: https://www.datanumen.com/blogs/auto...nt-file-names/

    Unfortunately it's not doing anything! Can anyone help fix it?

    Thanks for your help

    Code:
    Code:
    Public WithEvents objMails As Outlook.Items
    
    Private Sub Application_Startup()
     Set objMails = Outlook.Application.Session.GetDefaultFolder(olFolderInbox).Items
    End Sub
    
    Private Sub objMails_ItemAdd(ByVal Item As Object)
        Dim objMail As Outlook.MailItem
        Dim objAttachments As Outlook.attachments
        Dim objAttachment As Outlook.Attachment
        Dim strAttachmentName As String
        Dim objInboxFolder As Outlook.Folder
        Dim objTargetFolder As Outlook.Folder
     
        'Ensure the incoming item is an email
        If TypeOf Item Is MailItem Then
           Set objMail = Item
           Set objAttachments = objMail.attachments
     
           'Check if the incoming email contains one or more attachments
           If objAttachments.Count > 0 Then
              For Each objAttachment In objAttachments
                  strAttachmentName = objAttachment.DisplayName
                  Set objInboxFolder = Application.Session.GetDefaultFolder(olFolderInbox)
                  'Check the names of all the attachments
                  'Specify the target folders
                  If InStr(LCase(strAttachmentName), "worklog") > 0 Then
                     Set objTargetFolder = objInboxFolder.Folders("WorkLog")
                  ElseIf InStr(LCase(strAttachmentName), "report") > 0 Then
                     Set objTargetFolder = objInboxFolder.Folders("Report")
                  ElseIf InStr(LCase(strAttachmentName), "statistics") > 0 Then
                     Set objTargetFolder = objInboxFolder.Folders("Statistics")
                  End If
             Next
             'Move the email to specific folder
              objMail.Move objTargetFolder
           End If
        End If
    End Sub
    Last edited by Shaggy Hiker; Mar 26th, 2018 at 11:09 AM. Reason: Added CODE tags.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Export email from Outlook 2013 to excel sheet

    is the code firing at all when an email arrives?
    if not
    is Public WithEvents objMails As Outlook.Items in a std module or an object module? needs to be in an object module, like
    thisoutlooksession, userform or class
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Export email from Outlook 2013 to excel sheet

    Hi Ron,

    if you have Access create a Linked Table, then you can work with the InBox Folder
    like any Database Table.
    much easier than the Code you posted, the other benefit is that it is always updated

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    5

    Re: Export email from Outlook 2013 to excel sheet

    Quote Originally Posted by westconn1 View Post
    is the code firing at all when an email arrives?
    if not
    is Public WithEvents objMails As Outlook.Items in a std module or an object module? needs to be in an object module, like
    thisoutlooksession, userform or class
    It's not firing at all. Unsure about the object module, sry I'm a noob. It looks like the attached though. Is that correct?

    Thanks for your help.

    Name:  outlookVB.jpg
Views: 137
Size:  13.1 KB

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    5

    Re: Export email from Outlook 2013 to excel sheet

    Quote Originally Posted by ChrisE View Post
    Hi Ron,

    if you have Access create a Linked Table, then you can work with the InBox Folder
    like any Database Table.
    much easier than the Code you posted, the other benefit is that it is always updated

    regards
    Chris
    Hi,

    Thanks for the response Chris, I'm investigating this solution. I have linked the table, although it's taking all information across, not just the subject/sender info/date/body. But it might be a runner.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Export email from Outlook 2013 to excel sheet

    It looks like the attached though
    it looks like most of the code is in the thisoutlooksession module, but i can not see the line i asked about, is that line above the code in the image?

    i do not see any reason why the code should not run when a new mail item is received into the inbox
    put a msgbox or debug.print at the start of the code so you can see if anything is happening at all
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    5

    Re: Export email from Outlook 2013 to excel sheet

    Quote Originally Posted by westconn1 View Post
    it looks like most of the code is in the thisoutlooksession module, but i can not see the line i asked about, is that line above the code in the image?

    i do not see any reason why the code should not run when a new mail item is received into the inbox
    put a msgbox or debug.print at the start of the code so you can see if anything is happening at all
    Nothing seems to output in the immediate window. This error appeared when I ran Debug, compile project.

    Might it be permissions for these developer options in outlook as I use a corporate account.


    Name:  outlook_code.jpg
Views: 126
Size:  21.0 KB

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Export email from Outlook 2013 to excel sheet

    looks like you need to add a reference to excel
    menu > tools > references
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    5

    Re: Export email from Outlook 2013 to excel sheet

    Quote Originally Posted by westconn1 View Post
    looks like you need to add a reference to excel
    menu > tools > references
    Brilliant. That worked! Thank you!

    It is now logging all email under columns for sender name, email address, subject and received date. Is it possible to edit the code so that it only pulls emails with certain content in the header and from certain people?

    Another option is if I set up an Outlook folder with rules so that I have the emails I want in one folder, and then just pull emails from this folder. Can I edit the code so it works just on that folder, not on my full Inbox?

    Thanks again for your help, much appreciated,

    Ronan

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Export email from Outlook 2013 to excel sheet

    It is now logging all email under columns for sender name, email address, subject and received date. Is it possible to edit the code so that it only pulls emails with certain content in the header and from certain people?
    yes, of course
    Code:
    if instr(objmail.subject, "text to check for") > 0 then     ' subject contains the string
      ' do stuff
    end if
    note instr is case sensitive as used above, it can be made to match for both upper and lowercase

    if I set up an Outlook folder with rules so that I have the emails I want in one folder, and then just pull emails from this folder
    if you are only moving messages to other folders, you can probably do the whole thing with rules
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width