-
Mar 26th, 2018, 03:27 AM
#1
Thread Starter
New Member
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.
-
Mar 26th, 2018, 04:26 AM
#2
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
-
Mar 26th, 2018, 05:07 AM
#3
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.
-
Mar 26th, 2018, 11:17 AM
#4
Thread Starter
New Member
Re: Export email from Outlook 2013 to excel sheet
Originally Posted by westconn1
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.
-
Mar 26th, 2018, 11:19 AM
#5
Thread Starter
New Member
Re: Export email from Outlook 2013 to excel sheet
Originally Posted by ChrisE
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.
-
Mar 26th, 2018, 03:21 PM
#6
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
-
Mar 27th, 2018, 06:16 AM
#7
Thread Starter
New Member
Re: Export email from Outlook 2013 to excel sheet
Originally Posted by westconn1
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.
-
Mar 27th, 2018, 06:54 AM
#8
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
-
Mar 27th, 2018, 07:43 AM
#9
Thread Starter
New Member
Re: Export email from Outlook 2013 to excel sheet
Originally Posted by westconn1
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
-
Mar 27th, 2018, 03:22 PM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|