Powershell Outlook Emails

I know this can be done because i have seen it on other sites. I would like to create a Powershell script that goes into Outlook and retrieves the subject line and time email came in from a folder I have called Alerts given a specified time range and that have the key word "PASS" or "FAIL" in the subject.

If you cant go into a folder I am with a filter sender email. For example sendEmail@Alert.com  

OK let me list requirements again.

1. Go into outlook and retrieve emails from a certain folder called Alerts.(IF this is not possible to do folder, I am OK with a filter by email sender called sendEmail@Alert.com)

2. Return all subject lines that have a "Pass" and "Fail" in the Subject and email came in between datetime X and X+3hours

3. The output should have the Email Subject and the Email Arrival time
LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo TorresSQL DeveloperAuthor Commented:
Here is as far as I got I know there are emails in this time span. But I am not getting any results when I enter the time range filter

Add-Type -assembly "Microsoft.Office.Interop.Outlook"
$Outlook = New-Object -comobject Outlook.Application
$namespace = $Outlook.GetNameSpace("MAPI")

$namespace.Folders.Item(1).Folders.Item('Inbox').Folders.Item('AlertSite').Items  | Where-Object {$_.CreationTime -ge '05/31/2015 3:00:00 AM' -and $_.CreationTime -lt '05/31/2015 5:00:00 AM'  }
  

Open in new window

0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I recommend to provide dates and times in ISO format, and military time. But your code should work, provided there are mails in that date range.
BTW, the Add-Type is unnecessary at this time, COM does not support those explicit Interop types anyway (sadly).

The following code is expecting the folder 'Alerts' located inside your InBox folder. But be aware that searching Outlook mails via COM Interop is significantly slower than doing it in Outlook VBA.
$startdate = '2015-05-31 08:00'
$enddate = $startdate.AddHours(3)

$Outlook = New-Object -Com Outlook.Application
$Outlook.Session.GetDefaultFolder('olFolderInbox'),Folders.Item('Alerts').Items | 
  ? { $_.Subject -match 'Fail|Pass' } |
  ? { $_CreationTime -ge $startdate -and $_.CreationTime -lt $enddate } |
  Select ReceivedTime. CreationTime, Subject

Open in new window

I've included two timestamps, as they might differ. The ReceivedTime is the time the mail arrived at the final mail server (e.g. Exchange, your smart host, ...), and CreationTime is the time the mail item was created (by Exchange (?) or Outlook).
0
Leo TorresSQL DeveloperAuthor Commented:
Great qlemo thank you i just shut down that pc. Will be on later tonight 1am est testing your code
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Leo TorresSQL DeveloperAuthor Commented:
I will consider the vba version depends on how long this script takes
0
Leo TorresSQL DeveloperAuthor Commented:
I ran code on a different box changed conditions a little bit to still meet the criteria but it returned nothing.

Here is code executed
Code
Here is the box I am testing it on
sample box
Again this is a different PC I am not around the original PC I requested data from. I ran this as a test. I believe with the parameters I provided data should be returned. FYI i changed condition to Like from Match neither returned any results
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Check if you get mail items at all. You can do so e.g. with
$Outlook.Session.GetDefaultFolder('olFolderInbox'),Folders.Count

Open in new window

which checks how many folders are in the inbox.
Next, remove the time range restriction of your test code to see if the subject restriction works.
0
Leo TorresSQL DeveloperAuthor Commented:
i get a count of 28

and i changed the comma for a period.
0
Leo TorresSQL DeveloperAuthor Commented:
OK I know whats wrong just don't know how to fix it.

In this bit of code I commented out the date range and its returned data.  
$startdate = '2015-05-29 08:00'
$enddate = '2015-05-31 20:00'

$Outlook = New-Object -Com Outlook.Application
$Outlook.Session.GetDefaultFolder('olFolderInbox').Folders.Item('Dataservices').Items | 
  ? { $_.Subject -like "*TRR*" } |
#  ? { $_CreationTime -ge $startdate -and $_.CreationTime -lt $enddate } |
  Select ReceivedTime, CreationTime, Subject

Open in new window


I also
ran this code:
$Outlook.Session.GetDefaultFolder('olFolderInbox').Folders.Item('Dataservices').Items

It gave me results results:
This is how the creationTime variable appeared. Just an FYI not sure if that helps.
CreationTime                      : 5/18/2015 1:04:49 PM

Open in new window

0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Great you spotted that typo ;-). There is another error in my script (not casting to datetime), and that's an issue if trying to add 3 hours to it. But the strings work with the date filter of where-object (as I've tested by providing dates as strings directly in my test case), so that cannot be the issue. Just to make sure the corrected code of http:a#40804833
[DateTime] $startdate = '2015-05-31 08:00'
$enddate = $startdate.AddHours(3)

$Outlook = New-Object -Com Outlook.Application
$Outlook.Session.GetDefaultFolder('olFolderInbox').Folders.Item('Alerts').Items | 
  ? { $_.Subject -match 'Fail|Pass' } |
  ? { $_CreationTime -ge $startdate -and $_.CreationTime -lt $enddate } |
  Select ReceivedTime. CreationTime, Subject

Open in new window


The last test shows that you have full access to Outlook. You should also get access to the correct folder, otherwise the commands would error out immediately. Either no subject matches (but we see they should), or the date restriction does not work. Please pay attention to the fact you showed the ReceivedTime in your screenshots, but we are checking for CreationTime. Might be different, though I don't think so. Whatsoever, you've the tools to try and check yourself. All I can say is that this simple test case works for me:
$Outlook.Session.GetDefaultFolder('olFolderInbox').Folders.Item('EE').Items | ? { $_.Subject -like '*You*' } | ? { $_.CreationTime -gt '2015-05-30' } | Select ReceivedTime, CreationTime, Subject

Open in new window

0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
What you see as output is the string representation of the DateTime objects. Using the explicit cast for $startdate, as shown in above (first) code snippet, might help.
0
Leo TorresSQL DeveloperAuthor Commented:
OK this code is working on this machine. The one I need it to work on is in the office. I will be in office later tonight 1AM EST.

CLS
[datetime] $startdate = '2015-05-30 04:00'
[datetime] $enddate = '2015-05-30 07:00'#$startdate.AddHours(3)

$Outlook = New-Object -Com Outlook.Application
$Outlook.Session.GetDefaultFolder('olFolderInbox').Folders.Item('Dataservices').Items | 
? { $_.Subject -like '*TRR*' } | 
? { ($_.CreationTime -gt $startdate -and $_.CreationTime -gt $enddate )} | Select ReceivedTime, CreationTime, Subject

Open in new window

Once I get it working on the other machine I will close ticket.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Then the where-object implicit converted dates to strings, and then compared them. Strange this works for me flawlessly (as far as I can see), but my date format is dd.mm.yyyy, and maybe just a more lucky one ;-). I should have known I need to be more careful if using date strings against Interop, as other rules apply as with .NET / PowerShell itself.
0
Leo TorresSQL DeveloperAuthor Commented:
OK, here is what we have.
current Code
CLS
[datetime] $startdate = '2015-05-30 04:00'
[datetime] $enddate = '2015-05-30 07:00'#$startdate.AddHours(3)

$Outlook = New-Object -Com Outlook.Application
$Outlook.Session.GetDefaultFolder('olFolderInbox').Folders.Item('AlertSite').Items | 
? { $_.Subject -match "Pass|Fail" } | 
? { ($_.CreationTime -gt $startdate -and $_.CreationTime -gt $enddate )} | Select ReceivedTime, CreationTime, Subject    

Open in new window


It does return data however something is still funny about the time range I got results outside my time range
samp
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You wrote
   $_.CreationTime -gt $enddate
but it needs to be
   $_.CreationTime -lt $enddate
and I would use -ge for the start date (one boundary should be inclusive, either start or end date).
0
Leo TorresSQL DeveloperAuthor Commented:
Silly mistake, Thanks running now usually take a few.
0
Leo TorresSQL DeveloperAuthor Commented:
OK this is ridiculous now.

I started the script at
Monday, June 01, 2015 3:56:25 AM
Monday, June 01, 2015 5:11:05 AM

Open in new window


Those are the start and End times

Modified COde
CLS
Get-date
[datetime] $startdate = '2015-05-30 04:00'
[datetime] $enddate = '2015-05-30 07:00'#$startdate.AddHours(3)

$Outlook = New-Object -Com Outlook.Application
$Outlook.Session.GetDefaultFolder('olFolderInbox').Folders.Item('AlertSite').Items | 
? { $_.Subject -match "Pass|Fail" } | 
? { ($_.CreationTime -ge $startdate -and $_.CreationTime -lt $enddate )} | Select ReceivedTime, CreationTime, Subject 

Get-date

Open in new window

0
Leo TorresSQL DeveloperAuthor Commented:
Wow even this script

CLS
Get-date
[datetime]$startdate = '2015-06-01 03:00'
[datetime]$enddate = '2015-06-01 05:00'#$startdate.AddHours(3)

$Outlook = New-Object -Com Outlook.Application
$Outlook.Session.GetDefaultFolder('olFolderInbox').Folders.Item('AlertSite').Items |
? { $_.Subject -match "Pass|Fail" } |
? { ($_.CreationTime -ge $startdate -and $_.CreationTime -lt $enddate) } | Select ReceivedTime, CreationTime, Subject | Format-Table

Get-date

Open in new window


Here is the start and End Time 2 Hours and i am only scanning a 2 hour window.
Monday, June 01, 2015 5:19:02 AM
Monday, June 01, 2015 7:17:33 AM

Open in new window


how much faster is VBA?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The time window does not have any effect on the task time. PS needs to go thru all emails in that folder, which involves a lot of object transformations - a costly operation.
It might work much better if we can use an Outlook filter, leaving Outlook to do the most of the work.
CLS
Get-date
[String] $startdate = '2015-06-01 03:00'
[String] $enddate = '2015-06-01 05:00'

$Outlook = New-Object -Com Outlook.Application
$Folder  = $Outlook.Session.GetDefaultFolder('olFolderInbox').Folders.Item('AlertSite').Items
$ml      = $Folder.Find("[CreationTime] >= '$(Get-Date $startdate -Format 'd')' and [CreationTime] < '$(Get-Date $enddate -Format 'd')'")
$(
  for (;$ml; $ml = $Folder.FindNext())
  {
    if ($ml.Subject -match "Pass|Fail") { Select ReceivedTime, CreationTime, Subject
  }
) | Format-Table

Get-date

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Before you ask: No, Find does not support to search for a pattern. That requires to use AdvancedFind, which is more complex.
0
Leo TorresSQL DeveloperAuthor Commented:
This new code not returning anything on this computer. I will have to get to my other computer which is the one that has the data I need.

Made Changes here to accommodate for this mail box. You missed a closing bracket I put it in.(Hey this is the only way I can say I helped you in powershell)
CLS
Get-date
[String] $startdate = '2015-05-21 04:00'
[String] $enddate = '2015-05-30 07:00'

$Outlook = New-Object -Com Outlook.Application
$Folder  = $Outlook.Session.GetDefaultFolder('olFolderInbox').Folders.Item('Dataservices').Items
$ml      = $Folder.Find("[CreationTime] >= '$(Get-Date $startdate -Format 'd')' and [CreationTime] < '$(Get-Date $enddate -Format 'd')'")
$(
  for (;$ml; $ml = $Folder.FindNext())
  {
    if ($ml.Subject -match "TRR") { Select ReceivedTime, CreationTime, Subject}

  }
) | Format-Table

Get-date

Open in new window


Not that I don't believe your point but some food for thought. This computer where I am digging thru a DataServices folder is using outlook 2010 the other one for AlertSite is using 2013(this is the one taking 2 hours). The 2010 version run immidieatly and take a few minutes

For instance
CLS
Get-date
[datetime] $startdate = '2015-05-21 04:00'
[datetime] $enddate = '2015-05-30 07:00'#$startdate.AddHours(3)

$Outlook = New-Object -Com Outlook.Application
$Outlook.Session.GetDefaultFolder('olFolderInbox').Folders.Item('Dataservices').Items | 
? { $_.Subject -like '*TRR*' } | 
? { ($_.CreationTime -ge $startdate -and $_.CreationTime -lt $enddate )} | Select ReceivedTime, CreationTime, Subject | Format-table

Get-date

Open in new window


The code above here are the start and End dates
Monday, June 01, 2015 9:55:47 AM
Monday, June 01, 2015 9:57:16 AM

Open in new window


I will tst the new code in my other computer 2013 version later this afternoon 8PM EST. Thank you!
0
Leo TorresSQL DeveloperAuthor Commented:
do you think the -match and -like option may have something to do with Delay. Another test I will try tonight.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
-like is faster than -match (usually), because it only supports simple search patterns. Match uses regular expressions, which might get very complex. But I doubt that difference adds delays in this code, as the main time consumer is still the COM Automation.
0
Leo TorresSQL DeveloperAuthor Commented:
I will stick with this for now. That folder had more than 20000 Emails I cleared the folder some and it runs in a few seconds.
0
Leo TorresSQL DeveloperAuthor Commented:
thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.