Parse outlook appointment in shared calendar to check attachment

PowerShell Help
1

Hi All,

Maybe based on https://powershell.org/forums/topic/outlook-automatic-export-and-calendar/

I’d like to create a loop in all appointments of many users (probably 15 actually) shared calendar and extract to excel a list with all appointments which don’t contain an attachment starting with “XX”.

I didn’t find how to access appointment 's attachment…

If somebody can help the noob I am…

 

thanks to all

2

What would this list contain? Just the name and date of the appointment?

3

Hi Doug,

Thanks for reading

I need :

Person

Title

List of attachment filename

The goal is to verify if attachment filename start with “xx”

It seems to be very difficult to find info shared calendar objects.

4

You said you wanted appointments where the filename DOES NOT start with XX, please clarify. Also, there may be more than one attachment, what are you planning for those situations?

5

no sorry.
each appointment has one more attachment file.
One of them is delivery proof (AB.pdf), if it’s not present I have to add it.
What I want is to verify if each appointment of each shared calendar has an attachment file which name starts with “AB” with .pdf extension (for example).
Finally I’d like to get an output list in excel (or csv) of all appointments without this “AB.pdf” in attachment with field:

  • Calendar’s owner
  • Appointment’s object
  • Date / Time

Hope it’s more clear…

thanks

 

 

 

6

In this way I can get all I need except attachment:

 

Function Get-OutlookCalendar
{
    # load the required .NET types
    Add-Type -AssemblyName 'Microsoft.Office.Interop.Outlook'
    
    # access Outlook object model
    $outlook = New-Object -ComObject outlook.application

    # connect to the appropriate location
    $namespace = $outlook.GetNameSpace('MAPI')
    $Calendar = [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderCalendar
    $folder = $namespace.GetSharedDefaultFolder($recipient, 9)
    # get calendar items
    $folder.items |

      Select-Object -Property Start, Organizer, Attachments

}
Get-OutlookCalendar | Out-GridView
7

I’m not sure what you mean by “Appointments Object.” Hopefully this will help get you going.

This example assumes we are looping over the 15 accounts you’re trying to verify stored in the variable $userlist.

$userlist = 'smtp email account','alias','display name' # any of these work

# only need to define these once
Add-Type -AssemblyName ‘Microsoft.Office.Interop.Outlook’
$outlook = New-Object -ComObject outlook.application
$namespace = $outlook.GetNameSpace(‘MAPI’)
$olFolderCalendar = [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderCalendar

# loop over all calendar owners and collect matching appointments
$appointments = foreach($user in $userlist)
{
    $recipient = $namespace.CreateRecipient($user)
    
    # verify account is found
    if($recipient.Resolve())
    {
        $CalendarFolder = $outlook.Session.GetSharedDefaultFolder($recipient, $olFolderCalendar)
        $calendarsharing = $CalendarFolder.GetCalendarExporter()
        $CalendarSharing.IncludeAttachments = $true

        # if any attachment filename matches ab...pdf it won't be collected
        $calendarsharing.Folder.Items |
            Where-Object {($($_.attachments).filename -match '^ab.+pdf$') -eq $false} |
                Foreach-Object {
                    [PSCustomObject]@{
                        Organizer = $user
                        Subject   = $_.subject
                        StartTime = $_.start
                    }
                }
    }
    else
    {
        Write-Warning Unable to find account $user
    }
}

$appointments | Export-Csv c:\some\path\appointments_missing_ab-pdf.csv -NoTypeInformation

You could also choose to set the organizer property to $_.organizer.

8

Hi Doug,

thanks for that, it works fine.
I was able to add categories, but I wasn’t able to restrict to only the 30 next days or to add where-object for categories too.
Here’s what I tried :

$userlist = 'user1@domain.com', 'user2@domain.com' #,'alias','display name' # any of these work

# only need to define these once
Add-Type -AssemblyName ‘Microsoft.Office.Interop.Outlook’
$outlook = New-Object -ComObject outlook.application
$namespace = $outlook.GetNameSpace(‘MAPI’)
$olFolderCalendar = [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderCalendar

# loop over all calendar owners and collect matching appointments
$appointments = foreach($user in $userlist)
{
    $recipient = $namespace.CreateRecipient($user)
    
    # verify account is found
    if($recipient.Resolve())
    {
        $CalendarFolder = $outlook.Session.GetSharedDefaultFolder($recipient, $olFolderCalendar)
        $calendarsharing = $CalendarFolder.GetCalendarExporter()
        $CalendarSharing.IncludeAttachments = $true
        $CalendarSharing.startDate = (get-date)
        $CalendarSharing.endDate = (get-date).AddDays(30)

        # if any attachment filename matches ab...pdf it won't be collected
        $calendarsharing.Folder.Items |
            Where-Object {
            
            ($($_.attachments).filename -match '^AB.+pdf$') -eq $false #-or ($($_.categories) -notlike "*orange*", "*rouge*") -eq $false
            
            } |
                Foreach-Object {
                    [PSCustomObject]@{
                        Organizer = $user
                        Subject   = $_.subject
                        StartTime = $_.start
                        Categories =$_.categories
                    }
                }
    }
    else
    {
        Write-Warning Unable to find account $user
    }
}

$appointments | Export-Csv C:\MyPath\appointments_missing_ab-pdf.csv -delimiter ";" -Encoding UTF8

Take care of you.

9

It appears that startdate and enddate are used for saveasical and forwardasical - they both honor the dates set. For what you’re trying to do I’d just go with having powershell filter

$userlist = 'user1@domain.com', 'user2@domain.com' #,'alias','display name' # any of these work
 
# only need to define these once
Add-Type -AssemblyName ‘Microsoft.Office.Interop.Outlook’
$outlook = New-Object -ComObject outlook.application
$namespace = $outlook.GetNameSpace(‘MAPI’)
$olFolderCalendar = [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderCalendar
 
# loop over all calendar owners and collect matching appointments
$appointments = foreach($user in $userlist)
{
    $recipient = $namespace.CreateRecipient($user)
    
    # verify account is found
    if($recipient.Resolve())
    {
        $CalendarFolder = $outlook.Session.GetSharedDefaultFolder($recipient, $olFolderCalendar)
        $calendarsharing = $CalendarFolder.GetCalendarExporter()
        $CalendarSharing.IncludeAttachments = $true
 
        # if any attachment filename matches ab...pdf it won't be collected
        $calendarsharing.Folder.Items |
            Where-Object {
            
            ($($_.attachments).filename -match '^AB.+pdf$') -eq $false -and $_.start -gt (Get-Date) -and $_.end -lt (get-date).AddDays(30) #-or ($($_.categories) -notlike "*orange*", "*rouge*") -eq $false
            
            } |
                Foreach-Object {
                    [PSCustomObject]@{
                        Organizer = $user
                        Subject   = $_.subject
                        StartTime = $_.start
                        Categories =$_.categories
                    }
                }
    }
    else
    {
        Write-Warning Unable to find account $user
    }
}
 
$appointments | Export-Csv C:\MyPath\appointments_missing_ab-pdf.csv -delimiter ";" -Encoding UTF8

Of course you could save those dates as variables or build parameters - this is just to demonstrate.

10

Thanks Doug. A pleasure to learn with you. I 'll try to achieve the categories filter by myself.

11

Hi Doug, All

Everything was fine with this script sinc few days ago… Now I always have this error :

Add-Type : Impossible de charger le fichier ou l’assembly ‘Microsoft.Office.Interop.Outlook, Version=12.0.0.0,
Culture=neutral, PublicKeyToken=71e9bce111e9429c’ ou une de ses dépendances. Le fichier spécifié est introuvable.
Au caractère Ligne:1 : 1

  • Add-Type -AssemblyName ‘Microsoft.Office.Interop.Outlook’
  • CategoryInfo : NotSpecified: (:slight_smile: [Add-Type], FileNotFoundException
  • FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.AddTypeCommand

Do you know about any changes or similar issue ?

 

thanks for answer