damoncf1234
asked on
Search subfolder within Outlook 2010 Inbox for specific keywords in subject, and export certain fields to Excel, using VBScript
Hello,
We're trying to collect some information from Outlook 2010 about meetings that are sent to us via e-mail. Basically, we want to search a subfolder of the inbox for a combination of certain words, and if they exist, export the subject line, the date the e-mail was sent to us, the user who sent it to us, and -- the date written in the subject line. The first 3 items seem farily 'easy' -- I found the "MailItem object' properties, and it seems like those could be exported to excel.
mailItem.Subject
mailItem.SentOn
mailItem.SenderName
There's also an issue where we need to export just the 'original' email sent to us about each meeting --those always have attachments. There might be 2 or 3 responses to the original e-mail - those will not have attachments. So I figure we can 'restrict' the items found to just those that have attachments by using
mailitem.Attachment
All of our meeting e-mails come into a subfolder under each user's inbox - for example:
cdamon@somewhere.gov, Inbox, Meetings, WASH
In that folder, we need to search for just the following emails:
Items with attachments, that also have both 'WASH and 'AC', 'aWASH' and 'AC', 'WASH' and 'BC', or 'aWASH' and 'BC' in the subject line (four combinations of two of those terms).
Anyway, using previous VBScripts that BlueDevilFan has helped me on, I worked in this today. One stumbling block I have is how to tell the script where to search for these items (previous scripts have looked in the public folders, not in a user's inbox). (Here's a previous script that BlueDevilFan and Zack Barresse helped me with.)
Looking at the previous scripts that pull info from the public folders, it seems that I could just modify the following line:
To have it search in a subfolder of the inbox instead of a public folder location, maybe change it to something like:
I tried that, but the excel spreadsheet came up blank (I'm thinking it wasn't formatted correctly) - so how do I change that line to "point to" the subfolder under a user's inbox (Office 2010)?
Besides figuring out the correct way to search the user's inbox subfolder, the subject field, sender's name, and date & time sent to us has to be exported to an Excel spreadsheet. Again, going off a previous script that BlueDevilFan helped me with, I'm thinking the following snippet would do the trick:
Now here's (to me) the most confusing part - we need to search the subject field, and pull out the manually-typed date that the sender typed into the subject field (not the date they sent us the e-mail, but the date they typed in the subject field -- and the subject field is formatted like this: "Please WASH the following: BC on Some Topic 6/24/14"
This part seems trickier than the others - since it's not a system-generated date/time - it's 'inside' the subject line that the sender types in - it could be in the middle, or at the end of the subject line - and could be in the format of 6/24, 6/24/2014, 24JUN14, etc. I'm wondering if there's a "function" that could search the whole subject field for a any type of date format, then put it in a 'variable', and put that in "cell 4"/wherever in the excel spreadsheet as it processes each e-mail.
There's one last item -- we occasionally receive e-mails that would match the other prerequisites - but they are basically a list of these meetings coming up in the next week/month. They have a specific term in the subject - like "Upcoming List." To me, it would seem possible to 'restrict' items as they're searched to only e-mail items that do not include "Upcoming List" in the subject line.
Sorry for the long post - and for the multiple requirements. I can probably figure 'some' of this out -- the main things that have me 'hanging' are:
- What's the proper way to point the script to a specific subfolder in a user's Inbox (Outlook 2010)
- Verify that I'm using the "MailItem" properties correctly for populating the spreadsheet
- search the subject line field and pull-out a manually-typed date, and send it to one of the cells in the Excel document
Thank you,
Chris
We're trying to collect some information from Outlook 2010 about meetings that are sent to us via e-mail. Basically, we want to search a subfolder of the inbox for a combination of certain words, and if they exist, export the subject line, the date the e-mail was sent to us, the user who sent it to us, and -- the date written in the subject line. The first 3 items seem farily 'easy' -- I found the "MailItem object' properties, and it seems like those could be exported to excel.
mailItem.Subject
mailItem.SentOn
mailItem.SenderName
There's also an issue where we need to export just the 'original' email sent to us about each meeting --those always have attachments. There might be 2 or 3 responses to the original e-mail - those will not have attachments. So I figure we can 'restrict' the items found to just those that have attachments by using
mailitem.Attachment
All of our meeting e-mails come into a subfolder under each user's inbox - for example:
cdamon@somewhere.gov, Inbox, Meetings, WASH
In that folder, we need to search for just the following emails:
Items with attachments, that also have both 'WASH and 'AC', 'aWASH' and 'AC', 'WASH' and 'BC', or 'aWASH' and 'BC' in the subject line (four combinations of two of those terms).
Anyway, using previous VBScripts that BlueDevilFan has helped me on, I worked in this today. One stumbling block I have is how to tell the script where to search for these items (previous scripts have looked in the public folders, not in a user's inbox). (Here's a previous script that BlueDevilFan and Zack Barresse helped me with.)
Looking at the previous scripts that pull info from the public folders, it seems that I could just modify the following line:
Set olkFolder = olkSes.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("Conference Rooms").Folders("Conference Room - One")To have it search in a subfolder of the inbox instead of a public folder location, maybe change it to something like:
Set olkFolder = olkSes.GetDefaultFolder(cdamon@somewhere.gov).Folders("Inbox").Folders("Meetings").Folders("WASH")I tried that, but the excel spreadsheet came up blank (I'm thinking it wasn't formatted correctly) - so how do I change that line to "point to" the subfolder under a user's inbox (Office 2010)?
Besides figuring out the correct way to search the user's inbox subfolder, the subject field, sender's name, and date & time sent to us has to be exported to an Excel spreadsheet. Again, going off a previous script that BlueDevilFan helped me with, I'm thinking the following snippet would do the trick:
(other stuff above this point to open Outlook and a Excel template)
Set olkFolder = olkSes.GetDefaultFolder(a subfolder under the user's inbox)
Set olkItems = olkFolder.Items
For Each olkMessage in olkItems
excSht.Cells(lngRow, 1) = mailItem.Subject
excSht.Cells(lngRow, 2) = mailItem.SenderName
excSht.Cells(lngRow, 3) = mailItem.SentOn
excSht.Cells(lngRow, 4) = figure out some way to 'pull' the hand-typed date from the subject field
lngRow = lngRow + 1
NextNow here's (to me) the most confusing part - we need to search the subject field, and pull out the manually-typed date that the sender typed into the subject field (not the date they sent us the e-mail, but the date they typed in the subject field -- and the subject field is formatted like this: "Please WASH the following: BC on Some Topic 6/24/14"
This part seems trickier than the others - since it's not a system-generated date/time - it's 'inside' the subject line that the sender types in - it could be in the middle, or at the end of the subject line - and could be in the format of 6/24, 6/24/2014, 24JUN14, etc. I'm wondering if there's a "function" that could search the whole subject field for a any type of date format, then put it in a 'variable', and put that in "cell 4"/wherever in the excel spreadsheet as it processes each e-mail.
There's one last item -- we occasionally receive e-mails that would match the other prerequisites - but they are basically a list of these meetings coming up in the next week/month. They have a specific term in the subject - like "Upcoming List." To me, it would seem possible to 'restrict' items as they're searched to only e-mail items that do not include "Upcoming List" in the subject line.
Sorry for the long post - and for the multiple requirements. I can probably figure 'some' of this out -- the main things that have me 'hanging' are:
- What's the proper way to point the script to a specific subfolder in a user's Inbox (Outlook 2010)
- Verify that I'm using the "MailItem" properties correctly for populating the spreadsheet
- search the subject line field and pull-out a manually-typed date, and send it to one of the cells in the Excel document
Thank you,
Chris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, there is another way. It requires more code though and I'm never a fan of adding code to duplicate a built-in capability. Before we start adding code, let's try the GetDefaultFolder method one more try this time with one slight modification to the command I originally gave. Please try
When I posted earlier I wasn't thinking about the fact that you're running this from outside of Outlook using VBScript. I wrote that line for VBA which runs inside of Outlook. The difference is that "olFolderInbox" is a constant that's define inside Outlook but not outside of it.
Set olkFolder = olkSes.GetDefaultFolder(6).Folders("Meetings").Folders("WASH")When I posted earlier I wasn't thinking about the fact that you're running this from outside of Outlook using VBScript. I wrote that line for VBA which runs inside of Outlook. The difference is that "olFolderInbox" is a constant that's define inside Outlook but not outside of it.
ASKER
BlueDevilFan,
Yes - that worked great - adding that (6) made it work! Amazing... How did you "know" to do that?
The function couldn't find the dates in the subject lines, though (my fault - people put dates like "Meeting on June 24th topic xyZ" - so the date in the subject isn't necessarily 6/24 - it may be a more human form). Is it possible to modify that function to include date formats like "June 24th, 24JUN, etc.?"
Thank you again,
Chris
Yes - that worked great - adding that (6) made it work! Amazing... How did you "know" to do that?
The function couldn't find the dates in the subject lines, though (my fault - people put dates like "Meeting on June 24th topic xyZ" - so the date in the subject isn't necessarily 6/24 - it may be a more human form). Is it possible to modify that function to include date formats like "June 24th, 24JUN, etc.?"
Thank you again,
Chris
Chris,
Are all the dates in the same format? If not, then I need to know every possible format. Even then this may not work well. The code is using pattern matching to find the date. If the patterns aren't unique, then the code may generate false positives. Let's start with the patterns though. Let me know what they are and I'll modify the code accordingly.
Are all the dates in the same format? If not, then I need to know every possible format. Even then this may not work well. The code is using pattern matching to find the date. If the patterns aren't unique, then the code may generate false positives. Let's start with the patterns though. Let me know what they are and I'll modify the code accordingly.
ASKER
BlueDevilFan,
The dates may not always be in the same format - the ones I looked at yesterday were in the format of " May 21st" or June 3rd" - burried in the subject line.
I'll look and see if there are other date formats used in the subjects.
Thank you,
Chris
The dates may not always be in the same format - the ones I looked at yesterday were in the format of " May 21st" or June 3rd" - burried in the subject line.
I'll look and see if there are other date formats used in the subjects.
Thank you,
Chris
Ok. I'll wait to hear back from you.
ASKER
BlueDevilFan,
I just searched through the folder - it looks like most of the subject lines contain dates in the format of:
May 21st
June 2, 2014
5 June
10 JUN 14
Thank you,
Chris
I just searched through the folder - it looks like most of the subject lines contain dates in the format of:
May 21st
June 2, 2014
5 June
10 JUN 14
Thank you,
Chris
ASKER
BlueDevilFan,
In an effort to 'standardize' the date formats in the subject lines, I added a section into the script that searches for each month, then replaces it with that month's "number" --
month1 = Replace((Trim(olkMessage.S
(all the way down to December.)
That seems to work - it replaces the month name and the following space with the month number and a forward slash... I ran that, and it seems to work.
So now, most dates in the subject line should be in the 9/1, 10/12 format. But the function that looks for date formats in the subject line doesn't find any - and all we get is "not found."
Why do you think this is happening?
Thank you,
Chris
In an effort to 'standardize' the date formats in the subject lines, I added a section into the script that searches for each month, then replaces it with that month's "number" --
month1 = Replace((Trim(olkMessage.S
(all the way down to December.)
That seems to work - it replaces the month name and the following space with the month number and a forward slash... I ran that, and it seems to work.
So now, most dates in the subject line should be in the 9/1, 10/12 format. But the function that looks for date formats in the subject line doesn't find any - and all we get is "not found."
Why do you think this is happening?
Thank you,
Chris
If the date is now just month and day without a year, then we need to change this line
strDate = FindString(olkMessage.Subj
to
strDate = FindString(olkMessage.Subj
strDate = FindString(olkMessage.Subj
to
strDate = FindString(olkMessage.Subj
ASKER
Yes! I just did that - that works great.
Now the only ones left that come back with 'Not found' are the subject with dates in the format of "17June" or '25 JUL'
Is there a way to run another "FindString" that can search for a pattern of 0-9 and (month)
(dates like 5 June, or 15 JUN)?
That would work great.
Thanks
Now the only ones left that come back with 'Not found' are the subject with dates in the format of "17June" or '25 JUL'
Is there a way to run another "FindString" that can search for a pattern of 0-9 and (month)
(dates like 5 June, or 15 JUN)?
That would work great.
Thanks
ASKER
BlueDevilFan,
Hey - I just looked at the "FindString" line, and modified it like you said to above -- and also found a way to (just about) catch all of the DD MMM -style dates in the subject lines:
'all dates that are in mm/dd format
strdate = FindString(month12, "[0-9]{1-2}/[0-9]{1-2}")
if strDate = "Not found" then
'all dates that are in dd mmm format - (case-insensitive)
strdate = FindString(olkMessage.Subj
Else
End If
That seemed to do the trick - finds all of the mm/dd dates, and the dd Mmm dates...
One issue - This catches 99% of the dates -- except when there's a number followed by a letter in the subject line before the actual date - example:
subject line: "Missing Page 3 of Tab C for previous meeting on 12 June Discussion"
the 2nd "FindString" will just find the first number, followed by the next 3 characters - in this case, it will find "3 of" - instead of the "12 Jun" further along in the subject line
Is there a way to have "FindString" only search for a list of possible months (rather than just [A-Z;a-z]{1-3} ?
(Not really sure what the {1-3] part is for, but I figured the 3 was for 3 characters?
Thanks again,
Chris
Hey - I just looked at the "FindString" line, and modified it like you said to above -- and also found a way to (just about) catch all of the DD MMM -style dates in the subject lines:
'all dates that are in mm/dd format
strdate = FindString(month12, "[0-9]{1-2}/[0-9]{1-2}")
if strDate = "Not found" then
'all dates that are in dd mmm format - (case-insensitive)
strdate = FindString(olkMessage.Subj
Else
End If
That seemed to do the trick - finds all of the mm/dd dates, and the dd Mmm dates...
One issue - This catches 99% of the dates -- except when there's a number followed by a letter in the subject line before the actual date - example:
subject line: "Missing Page 3 of Tab C for previous meeting on 12 June Discussion"
the 2nd "FindString" will just find the first number, followed by the next 3 characters - in this case, it will find "3 of" - instead of the "12 Jun" further along in the subject line
Is there a way to have "FindString" only search for a list of possible months (rather than just [A-Z;a-z]{1-3} ?
(Not really sure what the {1-3] part is for, but I figured the 3 was for 3 characters?
Thanks again,
Chris
ASKER
Is there also a way to change the mailbox that is being searched? Currently, it's a subfolder of my personal inbox...
All of us here have 3 accounts setup under the same profile in Outlook - and they each have their own inboxes...
If I wanted to search the inbox in an account called "WASH" (so anyone with the "WASH" account added to their Outlook profile could run the same script), could I put something different in-place of GetDefaultFolder(6)?
Set olkFolder = olkSes.GetDefaultFolder(6)
Thanks,
Chris
All of us here have 3 accounts setup under the same profile in Outlook - and they each have their own inboxes...
If I wanted to search the inbox in an account called "WASH" (so anyone with the "WASH" account added to their Outlook profile could run the same script), could I put something different in-place of GetDefaultFolder(6)?
Set olkFolder = olkSes.GetDefaultFolder(6)
Thanks,
Chris
Chris,
The {1,3} part is telling the search to look for between 1 and 3 characters. If you're going to use FindString to search for the three characters month abbreviations, then change {1-3} to {3}. Yes, you can change the search to look for specific three character strings. Something like this
FindString(olkMsg.Subject,
If you change
.IgnoreCase = False
to
.IgnoreCase = True
then that search will find 5Jun, 5jun, or 5JUN.
Is there a way to have "FindString" only search for a list of possible months (rather than just [A-Z;a-z]{1-3} ?
(Not really sure what the {1,3] part is for, but I figured the 3 was for 3 characters?
The {1,3} part is telling the search to look for between 1 and 3 characters. If you're going to use FindString to search for the three characters month abbreviations, then change {1-3} to {3}. Yes, you can change the search to look for specific three character strings. Something like this
FindString(olkMsg.Subject,
If you change
.IgnoreCase = False
to
.IgnoreCase = True
then that search will find 5Jun, 5jun, or 5JUN.
Chris,
Yes, we can change the code to work against a folder in another mailbox. First, add this code to what you already have.
Once you've done that, change
Set olkFolder = olkSes.GetDefaultFolder(6)
to something like
Set olkFolder = OpenOutlookFolder("Mailbox
where Mailbox_Name is the name of the mailbox and Folder_Name is the folder within that mailbox.
Yes, we can change the code to work against a folder in another mailbox. First, add this code to what you already have.
Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
' Purpose: Opens an Outlook folder from a folder path.'
' Written: 4/24/2009'
' Author: BlueDevilFan'
' Outlook: All versions'
Dim arrFolders As Variant, _
varFolder As Variant, _
bolBeyondRoot As Boolean
On Error Resume Next
If strFolderPath = "" Then
Set OpenOutlookFolder = Nothing
Else
Do While Left(strFolderPath, 1) = "\"
strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
Loop
arrFolders = Split(strFolderPath, "\")
For Each varFolder In arrFolders
Select Case bolBeyondRoot
Case False
Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
bolBeyondRoot = True
Case True
Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
End Select
If Err.Number <> 0 Then
Set OpenOutlookFolder = Nothing
Exit For
End If
Next
End If
On Error GoTo 0
End FunctionOnce you've done that, change
Set olkFolder = olkSes.GetDefaultFolder(6)
to something like
Set olkFolder = OpenOutlookFolder("Mailbox
where Mailbox_Name is the name of the mailbox and Folder_Name is the folder within that mailbox.
ASKER
BlueDevilFan,
Great, I'll update the FindString line tomorrow. That looks like it will solve the "meeting date" issue. Thank you
I'll also add your OpenOutlook Function and modify that other line so we can search one of our shared mailboxes.
Thank you for your help once again!
I'll post back tomorrow with the results.
Thanks,
Chris
Great, I'll update the FindString line tomorrow. That looks like it will solve the "meeting date" issue. Thank you
I'll also add your OpenOutlook Function and modify that other line so we can search one of our shared mailboxes.
Thank you for your help once again!
I'll post back tomorrow with the results.
Thanks,
Chris
ASKER
BlueDevilFan,
I just updated the "findString" line, and that seems to work (it caught one line that wasn't a month - I'm trying to figure that out).
For the OpenOutlookFolder function, it comes up with an error saying 'Expected ')'"
I looked around, and am wondering if it's possible if that function is written for VBA instead of a VBScript.
I found a similar type function on another website that has options for VBA and VBScript - but that doesn't seem to work either:
http://www.outlookcode.com/d/code/getfolder.htm#vbscript
Is it possible to convert yours to VBScript?
Thank you,
Chris
I just updated the "findString" line, and that seems to work (it caught one line that wasn't a month - I'm trying to figure that out).
For the OpenOutlookFolder function, it comes up with an error saying 'Expected ')'"
I looked around, and am wondering if it's possible if that function is written for VBA instead of a VBScript.
I found a similar type function on another website that has options for VBA and VBScript - but that doesn't seem to work either:
http://www.outlookcode.com/d/code/getfolder.htm#vbscript
Is it possible to convert yours to VBScript?
Thank you,
Chris
ASKER
BlueDevilFan,
I was wondering if you had a chance to look at your OpenOutlookFolder function and see if it's vbscript - I can't get it to work in the script. Thank you,
Chris
I was wondering if you had a chance to look at your OpenOutlookFolder function and see if it's vbscript - I can't get it to work in the script. Thank you,
Chris
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BlueDevilFan,
Excellent - that updated function did the trick. We're now able to search other 'shared' mailboxes.
One more quick question - is it possible to search a mailbox, and any subfolders contained in the mailbox (or subfolders within a particular subfolder)?
(This is the last question I'll ask on this post - thank you).
Thank you,
Chris
Excellent - that updated function did the trick. We're now able to search other 'shared' mailboxes.
One more quick question - is it possible to search a mailbox, and any subfolders contained in the mailbox (or subfolders within a particular subfolder)?
(This is the last question I'll ask on this post - thank you).
Thank you,
Chris
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
David,
Hi, sorry for the delay - my wife and I were in WV at her family reunion (didn't have cell or internet service) for the past week.
Your update to search all subfolders worked well. Thank you (as always) for all of your feedback. It's much appreciated.
Thanks,
Chris
Hi, sorry for the delay - my wife and I were in WV at her family reunion (didn't have cell or internet service) for the past week.
Your update to search all subfolders worked well. Thank you (as always) for all of your feedback. It's much appreciated.
Thanks,
Chris
ASKER
BlueDevilFan has provided an excellent solution once again (and gone beyond the original question to customize the results). Thank you
You're welcome, Chris!
ASKER
Thank you for the quick response - I modified the script and tried to run it - and it came back with "Invalid procedure call or argument: 'GetDefaultFolder'"
It seems like it can't "figure out" what the default folder is -- after that, I changed that line around (took the 'GetDefaultFolder' out, and randomly put in things like the actual name of my account, or olkFolder = olkSes.Folders(olFlderInbo
Then it comes back with "Array index out of bounds", or "Object required: 'Folders(...)(...)"
Is there a way to specify the 'actual path' to the folder we'd like to search (without using the 'GetDefaultFolder'?
One piece I should've mentioned (which might explain why it can't decide where the default folder is) is that we have our individual account/inbox, and we also have two "shared" accounts in our profiles (that we add in outlook properties) - and each one of those accounts also have an 'inbox' folder.
So, is there another way to specify the 'actual path' to the folder (without using the 'GetDefaultFolder'?
Thank you,
Chris