PowerShell – VBA Read Outlook Emails and Export to Excel

August 28, 2019

Getting data out of Outlook using a VBA or a COM Script is not that difficult.

But when you want to reach the data in a Shared Mailbox that’s a different story !

 

There are different options :

1. VBA script in Outlook export to clipboard

2. VBA script in Excel and export to a sheet

3. PS script that that can handle both Outlook and Excel sheet Export

 

SOLUTION :

Outlook Script that can export to a clipboard.

But it runs quite slow and is depending on Microsoft Forms Library.

That is not by default available, so you need reference it.

The COM Library located here C :\WINDOWS\SysWOW64\FM2O. DLL

 

image

 

Sub ListMailsInFolder()
    Dim clipboard As New MSForms.DataObject
    Set clipboard = New MSForms.DataObject
    Dim txt As String
        
    Dim objNS As Outlook.NameSpace
    Dim objFolder As Outlook.MAPIFolder
    Dim olShareName As Outlook.Recipient
    Dim MailItems As MailItem
    Dim FmtToday As String
    
    FmtToday = Format(Date - 1, "DDDDD HH:NN")
    Debug.Print DateValue(Now()) - 1
    
    Set objNS = GetNamespace("MAPI")
    Set olShareName = objNS.CreateRecipient("outbound@yourcompany.com")
    'Set objFolder = objNS.Folders.GetFirst ' folders of your current account
    
'Shared Inbox
    Set objFolder = objNS.GetSharedDefaultFolder(olShareName, olFolderInbox) '// Inbox
    Set Items = objFolder.Items.Restrict("[ReceivedTime] > '" & FmtToday & "'")
    
    Debug.Print Items.Count
    
    For Each MailItems In Items
        'If TypeName(Item) = "MailItem" Then
            ' ... do stuff here ...
            Debug.Print MailItems.Subject & " " & MailItems.ReceivedTime
        'End If
        
        'Put some text inside a string variable
        txt = txt & MailItems.Subject & " " & MailItems.ReceivedTime & vbNewLine

        'Sleep (500)
    Next
    
    'Make object's text equal above string variable
     clipboard.SetText txt
    
' SubFolder
    Set objFolder = objNS.GetSharedDefaultFolder(olShareName, olFolderInbox).Parent.Folders("Shipments")  '
    Set Items = objFolder.Items.Restrict("[ReceivedTime] > '" & FmtToday & "'")
    Debug.Print "[ReceivedTime] > '" & FmtToday & "'"
    
    Debug.Print Items.Count
    
    For Each MailItems In Items
        'If TypeName(Item) = "MailItem" Then
            ' ... do stuff here ...
            Debug.Print MailItems.Subject & " " & MailItems.ReceivedTime
        'End If
        
        'Put some text inside a string variable
        txt = txt & MailItems.Subject & " " & MailItems.ReceivedTime & vbNewLine
        
        'Sleep (500)
    Next

    'Make object's text equal above string variable
     clipboard.SetText txt

    'Place DataObject's text into the Clipboard
    clipboard.PutInClipboard
End Sub

 

Initially it ran very SLOW, so I optimized it for speed using the RESTRICT method FILTERING.

Which runs a lot FASTER. Smile

 

You can convert this an Excel Macro to EXPORT it to a workbook sheet.

I added a input box to select the date FROM and TO, as well as added a Calendar Control Winking smile

image

image

See here on how to add a Calendar Control

https://www.ablebits.com/office-addins-blog/2016/10/12/insert-calendar-excel-datepicker-template/

 

Option Explicit

Sub Button1_Click()
    On Error GoTo ErrHandler
    
    Columns("A:C").Select
    Selection.ClearContents
    
    Dim dStartDate As Date
    Dim dEndDate As Date

'Enter the specific start date and end date
    dStartDate = InputBox("Enter the start date, such as 7/1/201x:", "Specify Start Date", Range("F2").Value)
    dEndDate = InputBox("Enter the end date, such as 8/31/201x:", "Specify End Date", Date)

If dStartDate <> #1/1/4501# And dEndDate <> #1/1/4501# Then
    
' Set Outlook APPLICATION OBJECTS.
    Dim Outlook As Object
    Set Outlook = CreateObject("Outlook.Application")
    
    Dim clipboard As New MSForms.DataObject
    Dim txt As String
        
    Const olFolderInbox = 6
    Dim objNS As Object
    Dim Items As Object
    Dim Item As Object
    Dim objFolder As Outlook.MAPIFolder
    Dim olShareName As Outlook.Recipient
    
    
    Set objNS = GetNamespace("MAPI")
    Set olShareName = objNS.CreateRecipient("outbound@yourcompany.com")
    'Set objFolder = objNS.Folders.GetFirst ' folders of your current account
    
'Shared Mailbox Inbox
    Set objFolder = objNS.GetSharedDefaultFolder(olShareName, olFolderInbox) '// Inbox
    Set Items = objFolder.Items
    
    Set clipboard = New MSForms.DataObject
    
    Dim iRows, iCols As Integer
    iRows = 2
    
    For Each Item In objFolder.Items
        'If TypeName(Item) = "MailItem" Then
            ' ... do stuff here ...
            Debug.Print Item.Subject
        'End If
        
    'Put some text inside a string variable
        txt = txt & Item.Subject & vbNewLine
        
        If Format(Item.ReceivedTime, "dd/MM/YYYY") >= dStartDate And Format(Item.ReceivedTime, "dd/MM/YYYY") <= dEndDate Then
        Cells(iRows, 1) = Item.Subject
        Cells(iRows, 3) = Item.ReceivedTime
        iRows = iRows + 1
        End If
    Next
    
    'Make object's text equal above string variable
     clipboard.SetText txt
   
' Shared Mailbox SubFolder(s)
    Set objFolder = objNS.GetSharedDefaultFolder(olShareName, olFolderInbox).Parent.Folders("Shipments")
    '.Parent.Folders("Shipments").Folders("_ToDo") ' Subfolders
    Set Items = objFolder.Items
    
    For Each Item In objFolder.Items
        'If TypeName(Item) = "MailItem" Then
            ' ... do stuff here ...
            Debug.Print Item.Subject
        'End If
        
    'Put some text inside a string variable
        txt = txt & Item.Subject & vbNewLine
        
'Debug.Print Format(Item.ReceivedTime, "dd/MM/YYYY") & " - " & dStartDate

        If Format(Item.ReceivedTime, "dd/MM/YYYY") >= dStartDate And Format(Item.ReceivedTime, "dd/MM/YYYY") <= dEndDate Then
            Cells(iRows, 1) = Item.Subject
            Cells(iRows, 3) = Item.ReceivedTime
            iRows = iRows + 1
        End If
        Next

'Object's text to Clipboard
    clipboard.SetText txt

'Place DataObject's text into the Clipboard
    clipboard.PutInClipboard
   
' Release Objects
    Set Outlook = Nothing
    Set objNS = Nothing
    Set olShareName = Nothing
    Set clipboard = Nothing
    Set objFolder = Nothing
    Set Items = Nothing

End If

ErrHandler:
    Debug.Print Err.Description
End Sub

 

This is the PowerShell Version !

 

CLS

[void][Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic')

$title = "Start Date"
$msg   = "From"
$default   = (Get-Date).AddDays(-1).ToString("d")  # Get-Date -UFormat "%m/%d//%Y" or (Get-Date).AddDays(-1).ToString("MM/dd/yyyy")

$sDate = [Microsoft.VisualBasic.Interaction]::InputBox($msg, $title, $default)

[string]$sDate += "  00:00" # + (Get-Date).tostring(‘t’)


[threading.thread]::CurrentThread.CurrentCulture = 'en-US'  # Important line !!

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook = $excel.Workbooks.Add()

$workbook.WorkSheets.Item(1).Name = "Outbound"

$sheet = $workbook.ActiveSheet

$olFolderInbox = 6

    Add-type -assembly “Microsoft.Office.Interop.Outlook” | out-null

    $outlook = new-object -comobject outlook.application


    $namespace = $outlook.GetNameSpace(“MAPI”)

    $olShareName =  $namespace.CreateRecipient("outbound@yourcompany.com")

    $olShareName.Resolved()

    $sFilter = ("[ReceivedTime] >= '$olddate'")

# Shared Inbox

    $Folder = $namespace.GetSharedDefaultFolder($olShareName, $olFolderInbox)
    
    $Items = $Folder.Items.Restrict("[ReceivedTime] > '$sDate'")
    
    $Items | Select-Object -Property Subject, ReceivedTime, SenderName, SenderEmailAddress | ? {$_ -match "@"} 


    # $Items | Export-CSV -NoTypeInformation XXXX\Trial.csv 

$counter = 0

    foreach($Item in $Items){

     $Email = ($Item.SenderEmailAddress | ? {$_ -match "@"})

   # ($Item.Subject + " " + $Item.ReceivedTime + " " + $Item.SenderName + " " + $Email)

    $counter++
        
        $sheet.cells.Item($counter,1) = $Item.Subject
        $sheet.cells.Item($counter,3) = $Item.ReceivedTime.Date
    }


# SubFolder
    $SubFolder = $Folder.Parent.Folders("Shipments")

    $olddate = (Get-Date).AddDays(-2).ToLongDateString()

    $Items = $SubFolder.Items.Restrict("[ReceivedTime] > '$sDate'")
    
    $Items | Select-Object -Property Subject, ReceivedTime, SenderName, SenderEmailAddress | ? {$_ -match "@"} 

    # $array | Export-CSV -NoTypeInformation XXXX\Trial.csv 

    foreach($Item in $Items){

    $Email = ($Item.SenderEmailAddress | ? {$_ -match "@"})

    # ($Item.Subject + " " + $Item.ReceivedTime + " " + $Item.SenderName + " " + $Email)

    $counter++
        
        $sheet.cells.Item($counter,1) = $Item.Subject
        $sheet.cells.Item($counter,3) = $Item.ReceivedTime.Date
    }
 
 $sheet.Cells.EntireColumn.AutoFit()


[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Outlook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

# Stop-Process -Name EXCEL -Force

Remove-Variable Excel, Email

 

Enjoy !!

Advertisements

PowerShell – Using PowerShell Script in Excel VBA

March 11, 2019

There are numerous real life scenarios where you would like to hand out an Excel to an End User to retrieve data straight in Excel.

Without him or her have to use PowerShell or any other obscure tool that does not look familiar to them.

So we can establish this by using the old time favorite tool from SAPIEN Technologies.

image

To download ActiveXPosh you need to register first …

This Free Tool is doing the magic between the PowerShell scripting and any COM compatible client, like Excel or VBScript or AutoIT Smile

So here we are going to create an Excel VBA macro to demonstrate the use.

Solution :

The flow being used is to export a PS script to CSV. And next open the data in the Excel file that runs the macro.

1. Make sure that you are using the correct architecture version x86 or x64, depending of your Excel architecture …

So if you use an Excel x86 version you need to install the x86 ActiveXPosh version.

2. Create a Excel + Macro using this script :

The PowerShell Script is :

Get-WmiObject -class Win32_Service | Export-Csv -Path c:\temp\temp.csv -NoTypeInformation

The complete VBA macro is :

 

Sub ActiveXPoSH_ExportPS_ImportCSV()

Const OUTPUT_CONSOLE = 0
Const OUTPUT_WINDOW = 1
Const OUTPUT_BUFFER = 2

Dim ActiveXPosh As Object
Dim success As Variant
Dim cmd As String
Dim file As String
Dim sheet As String

' see output location here
file = "C:\temp\temp.csv"
sheet = "Sheet1"

' create the PowerShell object
Set ActiveXPosh = CreateObject("SAPIEN.ActiveXPoSHV3")

success = ActiveXPosh.Init(vbFalse) 'Do not load profiles

If success <> 0 Then
    Debug.Print "Init failed"
End If

If ActiveXPosh.IsPowerShellInstalled Then
    Debug.Print "Ready to run PowerShell commands"
    'MsgBox ("Ready to run PowerShell commands")
Else
    Debug.Print "PowerShell not installed"
End If

'Set the output mode
ActiveXPosh.OutputMode = OUTPUT_CONSOLE

' set command string
cmd = "Get-WmiObject -class Win32_Service " _
      & " | Export-Csv -Path " & file & " -NoTypeInformation"

' clear all data
Sheets(sheet).Cells.Delete

Dim outtext, Str
' set the output mode to buffer
ActiveXPosh.OutputMode = OUTPUT_BUFFER

' run cmd
ActiveXPosh.Execute (cmd)

' get the output line by line and add it to a variable
For Each Str In ActiveXPosh.Output
    outtext = outtext & Str
    outtext = outtext & vbCrLf
Next

' import the data to the Sheet1
Set ws = ActiveWorkbook.Sheets(sheet)
With ws.QueryTables.Add(Connection:="TEXT;" & file, Destination:=ws.Range("A1"))
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .Refresh
End With

' delete the querytable if there is one
On Error GoTo nothingtodelete
    Sheets(sheet).QueryTables(1).SaveData = False
    Sheets(sheet).QueryTables.Item(1).Delete
nothingtodelete:

' Delete the temp CSV File
If Len(Dir$(file)) > 0 Then Kill file

' Alternatively you can get the output as a single String
' outtext = ActiveXPosh.OutputString
Debug.Print outtext

' Clear Immediate
Application.Wait (Now + TimeValue("0:00:02"))
Debug.Print Now
Application.SendKeys "^g ^a {DEL}"

End Sub

The result is :

image

I chose to work using an interim CSV file, because this is the fastest and most compatible with Excel. Smile

You can of course write cell per cell or an PowerShell array to Excel. But it will take you forever to make it happen. Sad smile

As you can imagine I can now get O365 and Azure data straight in Excel …

If you put a password on the macro you can safely hand it out in your organization !

 

Enjoy!


PowerShell – Crystal Reports COM Object

December 17, 2017

Recently I was going through my files and found (again) Smile the very nice Crystal Reports COM libraries

 

image

As you can see these are more then 15 years old !!

But still works like a charm … many years back I created an example in AutoIT using the CRViewer COM object.

image

Actually the Crystal Reports at that time came with some nice COM libraries, which are still my all time favourites.

And tons of functionality to automate your tasks…

image

It can access all kinds of Database formats using ODBC and native DB connections.

As you can see it also has a multitude of Export and printing abilities.

$Crystal_Report = 1
$Data_Interchange = 2
$Record_Style = 3
$CSV = 5
$Tab_Sep_Val = 6
$CharSepVal = 7
$Text = 8
$TabSepText = 9
$Paginated_Text = 10
$Lotus_23WKS = 11
$Lotus_123WK1 = 12
$Lotus_123WK3 = 13
$Word = 14
$Excel_5 = 21
$Excel_5_Tabular = 22
$ODBC = 23
$HTML32 = 24
$Explorer32 = 25
$Excel_7= 27
$Excel_7_Tabular = 28
$Excel_8 = 29
$Excel_8_Tabluar = 30
$PDF = 31
$HTML40 = 32
$Crystal_Report_7 = 33
$Report_Definition = 34
$RTF = 35
$XML = 36

 

PowerShell COM Backwards compatible ?

So the idea now is to see how PowerShell is able to handle these old COM libraries.

Here we go …  Smile

This script will read a Crystal Report 8.5 report using a MS SQL database data source.

Refreshes the data in the report, Exports it to Excel and next E-mails this to 1 or more recipients.

CLS

$Excel_7= 27

$ServerType = "p2lodbc.dll"
$ServerName = "ServerName"
$databaseName = "DatabaseName"
$user = "UserName"
$pswd = "Password"
 
$vFilenameReport = "C:\Deliveries per customer.rpt"
$vFilenameExport = "C:\Deliveries per customer.xls"

$oApp = New-Object -ComObject "CrystalRuntime.Application" # Com Object

# LogOnServer
    $oApp.LogOnServer($ServerType, $ServerName, $databaseName, $user, $pswd)

# Open Report
  $oRpt = $oApp.OpenReport($vFilenameReport, 0) # 0 / 1

# Report Options
  $oRpt.DiscardSavedData() # DiscardSavedData In Report to REFRESH
  $oRpt.EnableParameterPrompting = $False
  $oRpt.DisplayProgressDialog = $False
  $oRpt.MorePrintEngineErrorMessages = $False

# Export Options
Start-Sleep 2

  # $oRpt.ExportOptions.Reset | out-null
   $oRpt.ExportOptions.DestinationType = 1 # 1=>filesystem 
   $oRpt.ExportOptions.FormatType = $Excel_7  # 27=> Excel 7
   $oRpt.ExportOptions.DiskFileName = $vFilenameExport 

  # $oRpt.ExportOptions.ExcelExportAllPages = $True 
  # $oRpt.Export($True) # Export without prompting = False

   $oRpt.Export($False) # Export without prompting = False

# Send Email
 Send-MailMessage -to Your.Name@Company.com `
-from admin@Company.com `
-Subject "Monthly Email Service" `
-body "Hello !!! `nThis is your personal E-mail service.`nPlease find the updates enclosed.`nUntill next time. `nRegards," `
-Attachments $vFilenameExport `
-smtpserver YourEmailServer  

# Remove Attachment File
   Start-Sleep -s 2
   Remove-Item $vFilenameExport

 

If you schedule this script you have created your own reporting platform.

 

image

Keep in mind that you need to you the 32Bit PowerShell version

image

C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe

But it doesn’t spoil the fun Smile

And this proves PowerShell is 100% backwards COM compatible !!

 

Enjoy !


PowerShell – getting Errors using AutoITx3.dll

August 9, 2014

Normally you can get all Methods and Properties of any registered COM object using the Get-member cmdlet.

But occasionally you get this error.

image

The reason is that you have registered the COM object only as x32 bit and not as x64 bit.

2 ways to solve this.

First register both x32 and x64 bit Dll’s.

image

After registering both versions this was the result.

Cls

New-Object -com AutoItX3.Control | Get-Member

image

Lucky for us that AutoIT ships 2 versions. But most of the DLL are either x32 or x64 bit ?

So you can get it running using the x32 bit if Powershell using the x32 bit of the COM Dll and the same for x64.

Or try a hack to register the x32 Bit like this :

The 32-bit one goes into C:\Windows\SysWOW64, and the 64-bit DLL goes into C:\Windows\System32.

And appropriate registry keys in the appropriate location

http://msdn.microsoft.com/en-us/library/windows/desktop/ms724072(v=vs.85).aspx


PowerShell – Using AutoIT COM object

January 21, 2013

 

This example shows how to run the AU3 Com object in Powershell.

cls
Clear-Host
# AutoIT declare object in PS
$oAutoIt = New-Object -comobject AutoItX3.Control

# This will List all the methods and properties of the COM object
$oAutoIt | get-member

# This will create a tooltip in the upper left of the screen
$oAutoIt.ToolTip("This is an AU3 tooltip Example" , 300, 430) 

# Important don't leave a spaces after the properties like this
# is wrong $oAutoIt.Sleep (2000) but like this =&amp;gt; $oAutoIt.Sleep(2000)

$oAutoIt.Sleep(2000)
$oAutoIt.ToolTip("") 

You can use any COM object in Powershell as you can see.

For a .Net assambly or Powershell integration look here

Enjoy !


PowerShell – Export to EXCEL Issue

January 14, 2013

I was trying to use some simple script to create a workbook in Excel 2007 and add some worksheets in it, with data.

Spend hours on it, but it just opened up the Excel application. But no workbook and no sheets ? Crying face

This was the intial code :

CLS
#[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$Excel = new-Object -com Excel.Application
$Excel.visible = $True
$Excel.DisplayAlerts = $False
$ExcelWB = $Excel.Workbooks.Add()

# adding 3 extra sheets
$Sheet = $ExcelWB.Worksheets.Add()
$Sheet = $ExcelWB.Worksheets.Add()
$Sheet = $ExcelWB.Worksheets.Add()

# Assign each worksheet to a variable and
# name the worksheet.
$Sheet1 = $ExcelWB.Worksheets.Item(1)
$Sheet2 = $ExcelWB.WorkSheets.Item(2)
$Sheet3 = $ExcelWB.WorkSheets.Item(3)
$Sheet4 = $ExcelWB.WorkSheets.Item(4)
$Sheet5 = $ExcelWB.WorkSheets.Item(5)
$Sheet6 = $ExcelWB.WorkSheets.Item(6)
$Sheet1.Name = "General"
$Sheet2.Name = "System"
$Sheet3.Name = "Processor"
$Sheet4.Name = "Memory"
$Sheet5.Name = "Disk"
$Sheet6.Name = "Network"
Sleep 2
$ExcelWB.SaveAs('C:\Temp\test.xls',56)
write-host $Error[0]
Sleep 2

$ExcelWB.Close()
$Excel.Quit()

On the second line you can notice the line including

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'

When you uncomment it, everything works fine ?

Reason is that I use an English OS with a non English regional settings, which seems to be the issue.