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 – How to run an EncodedCommand using a Bat file

August 23, 2019

If you don’t want to expose the PowerShell script logic to your end users,

you can use the EncodedCommand switch to run Base64 Encoded files.

 

Next if you don’t want to give the PowerShell command or PS IDE you can use just a Bat file instead.

 

But this is challenging to get it all working. Confused smile

 

SOLUTION :

1. Create a Bat file that calls the Encoded PowerShell script :

Like this …

 

Powershell -file "C:\TEMP\OUTBOUND_Logfiles.ps1"

 

image

Next create an EncodedCommand PowerShell script :

Like this …

powershell.exe -EncodedCommand "JABmAGkAbABlAHMAIAA9ACAARwBlAHQAL...."

 

TIP :

If you want to use the Out-GridView for showing the output in a GUI.

You will need to at this to the end of the script in order to keep the GridView Open at the end.

....

Read-Host "press enter to exit"

 

image

Otherwise the GridView will not remain open at the end when the script finished Smile

 

Enjoy !!


PowerShell – GUI Ribbon

July 5, 2019

Ever wanted to build a modern GUI including a Ribbon in just a few clicks …

Office 2007 Style

image

Office 2010 Style

image

Office 2013 Style

image

Well this is where it all starts :

New Site : https://github.com/RibbonWinForms/RibbonWinForms

Old Site : https://www.codeproject.com/Articles/364272/Easily-Add-a-Ribbon-into-a-WinForms-Application-Cs?fid=1704255&df=90&mpp=25&sort=Position&view=Normal&spc=Relaxed&fr=101&prof=True

Movie : https://www.youtube.com/watch?v=OPrj7c1CYIg

Since the documentation shows all C# code we can easily transform this a PowerShell Script Winking smile

PREREQUISITES :

You need to download here the Ribbon Assembly to reference in the script.

SOLUTION :

Don’t forget to change the path to the Assembly and the ICONS

Add-Type -Path "C:\_\System.Windows.Forms.Ribbon35.dll"

$form = New-Object System.Windows.Forms.Form
$form.Size = New-Object System.Drawing.Size(600,300)
$form.TopMost = $true
$form.StartPosition = "CenterScreen"

$form.AutoScalemode = "Font" # Enum Font or DPI
$form.AutoSize = $true
$form.AutoSizeMode = "GrowOnly"  # "GrowAndShrink"

$form.SuspendLayout()

# Objects
$ribbon = New-Object System.Windows.Forms.Ribbon
$tab1 = New-object System.Windows.Forms.RibbonTab
$tab2 = New-object System.Windows.Forms.RibbonTab
$MenuItem1 = New-object System.Windows.Forms.RibbonOrbMenuItem
$panel1 = New-object System.Windows.Forms.RibbonPanel
$button1 = New-object System.Windows.Forms.RibbonButton

# $ribbon | get-member
# $ribbon.Parent

# ribbon1
$ribbon.Font = New-Object System.Drawing.Font("Segoe UI", 9);
$ribbon.Location = New-Object System.Drawing.Point(0, 0);
$ribbon.Minimized = $false;
$ribbon.Name = "ribbon1";

$ribbon.OrbStyle = "Office_2007" # "Office_2010"
#$ribbon.Visible = $true

$ribbon.OrbDropDown.BorderRoundness = 8;
$ribbon.OrbDropDown.Location = New-Object System.Drawing.Point(0, 0);
$ribbon.OrbDropDown.MenuItems.Add($MenuItem1);
$ribbon.OrbDropDown.Name = "Drop";
$ribbon.OrbDropDown.Size = New-Object System.Drawing.Size(527, 116);
$ribbon.OrbDropDown.TabIndex = 0;
$ribbon.OrbImage = $null;
$ribbon.RibbonTabFont = New-Object System.Drawing.Font("Trebuchet MS", 9);
$ribbon.Size = New-Object System.Drawing.Size(1280, 200);
$ribbon.TabIndex = 0;
$ribbon.Tabs.Add($Tab1);
$ribbon.TabsMargin = New-Object System.Windows.Forms.Padding(12, 26, 20, 0);
$ribbon.Text = "ribbon1";
$ribbon.ThemeColor = "Blue";
$ribbon.Height = 150

# ribbonOrbMenuItem1
$MenuItem1.DropDownArrowDirection = "Left";
$MenuItem1.Image = [System.Drawing.Image]::FromFile("C:\_\ABF-Outlook.gif") 
# ((System.Drawing.Image)(resources.GetObject("ribbonOrbMenuItem1.Image")));
$MenuItem1.SmallImage = [System.Drawing.Image]::FromFile("C:\_\ABF-Outlook.gif") 
# ((System.Drawing.Image)(resources.GetObject("ribbonOrbMenuItem1.SmallImage")));
$MenuItem1.Text = "ribbonOrbMenuItem1";

# ribbonTab1
$Tab1.Panels.Add($panel1);
$Tab1.Text = "ribbonTab1";

# ribbonPanel1
$Panel1.Items.Add($Button1);
$Panel1.Text = "ribbonPanel1";

# ribbonButton1
$Button1.Image = [System.Drawing.Image]::FromFile("C:\_\SaveItem.png") 
#((System.Drawing.Image)(resources.GetObject("ribbonButton1.Image")));
$Button1.SmallImage = [System.Drawing.Image]::FromFile("C:\_\SaveItem.png") 
# ((System.Drawing.Image)(resources.GetObject("ribbonButton1.SmallImage")));
$Button1.Text = "ribbonButton1";

# ribbonTab2
$Tab2.Text = "ribbonTab2";

$form.Controls.Add($ribbon)

$form.ResumeLayout($false)

$form.ShowDialog()



image


image

Enjoy !




PowerShell – Report Designer Assembly in a .NET GUI

June 4, 2019

If you need a Report Designer that has all basic functionality needed.

And can connect to multiple Data Sources…

image

Print Preview

image

And more …

image

This is the way to go.

Prerequisites :

Download the Assembly from here 

(if you don’t find it there you can email the developer and he will send it over)

Save the Assembly somewhere locally.

SOLUTION :

This script will trigger the GUI that give you full functionality in PowerShell in less then 10 lines of code !

This is because PS can access .NET Assemblies Smile

CLS

Add-Type -AssemblyName System.Windows.Forms
Add-Type -Path "C:\_\Apps\_PowerShell\_GUI Forms\.NET Report Builder\MySql.Data.dll"
Add-Type -Path "C:\_\Apps\_PowerShell\_GUI Forms\.NET Report Builder\ReportDesigner5.dll"


$rd = New-Object ReportDesigner5.Designer


#$rd.Dock = [System.Windows.Forms.DockStyle]::Fill
$rd.ShowDialog()
$rd.ShowProperty()
$rd.ShowReportTree()

image

One TIP : Winking smile

– You also need to run it in PS x86 bit for the moment.

– You need to play around with the ZOOM scalar to get the report visible 

Alternatively you can as well download the EXE  here :

That has the same possibilities if you don’t want to use PS.

image

A new version was released on my request to add SQLite as a data source Smile

image

image

Keep in mind that you need to reference 2 additional Assemblies for this version :

1. QRCoder.dll version 1.3.5

2. SQLIte.dll version 1.0.109

Many thanks the to developer to create this nice Reporting tool and providing the Assembly !

Enjoy !


PowerShell – Retrieve a SSRS Report in a .NET GUI

May 5, 2019

Ever wanted to create you Offline Reporting Engine based on SSRS using PowerShell !

The objective is to run the RDL reports server side and display in .NET GUI client side.

PREREQUISITES :

You first need to download the .NET Assemblies using the SSRS redistributables.

Download here :

2008 / 2010 / 2012 or 2015 for a newer version see here

SOLUTION :

The solution is based on .Net Scripting in PowerShell using a GUI.

REMOTE processing mode :

CLS
# [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.ReportViewer.WinForms”)
Add-Type -Path "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.WinForms\12.0.0.0__89845dcd8080cc91\Microsoft.ReportViewer.WinForms.dll"

#Windows.Forms for viewing dialog box
# [void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
Add-Type -AssemblyName "System.Windows.Forms"

# for credentials, if needed
# [void][System.Reflection.Assembly]::LoadWithPartialName("System.Net")
Add-Type -AssemblyName System.Net

$credential = Get-Credential

$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer;
$rv.ServerReport.ReportServerCredentials.NetworkCredentials = $credential

$rv.ProcessingMode = "Remote"

$rv.ServerReport.ReportServerUrl = "http://YourServer/Reportserver"
$rv.ServerReport.ReportPath = "/YourReportName”

$rv.Height = 600;
$rv.Width = 800;
$rv.RefreshReport();

#---------------------
# Show as Dialog Using Windows Form
#---------------------
# Create a new form
$form = New-Object Windows.Forms.Form;

# Form Properties
$form.Height = 610;
$form.Width= 810;
$form.Controls.Add($rv);
$rv.Show();
$form.ShowDialog();

#---------------------
# Export to Excel
#---------------------
#now let’s try exporting to Excel
$mimeType = $null;
$encoding = $null;
$extension = $null;
$streamids = $null;
$warnings = $null;

$bytes = $rv.ServerReport.Render("Excel”, $null,
[ref] $mimeType,
[ref] $encoding,
[ref] $extension,
[ref] $streamids,
[ref] $warnings);
$file = "C:\Temp\SampleReport.xls”;
$fileStream = New-Object System.IO.FileStream($file,
[System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();

#l Open up our excel application
$excel = New-Object -comObject Excel.Application
$excel.visible = $true;
$excel.Workbooks.Open($file) | Out-Null;

#---------------------
# Export to PDF
#---------------------
$bytes = $rv.ServerReport.Render("PDF”, $null,
[ref] $mimeType,
[ref] $encoding,
[ref] $extension,
[ref] $streamids,
[ref] $warnings);
$file2 = "C:\Temp\SampleReport.pdf”;
$fileStream = New-Object System.IO.FileStream($file2,
[System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();
[System.Diagnostics.Process]::Start($file2)
image

The result is the report in your own GUI that runs locally and is processed centrally, when you use REMOTE

If the report has Parameters it will prompt you to enter them just like in the Web Form.

image

After the GUI is close the script will be EXPORT the content to EXCEL and PDF too.

You will have the options to EXPORT to all supported formats, or add your own extension if you are working server side.

image

LOCAL processing mode :

Most interesting is that you can run the Report Viewer Control in LOCAL processing mode !

Which means that you can create a Local Reporting Engine that runs the RDL reports from you client in a PowerShell GUI

image

And additionally you can add any kind of .Net support Data Source, like in this case an SQLite database !

Which is not supported by server side SSRS…

How cool is that ! Surprised smile

For more info see here

Happy Scripting Smile

Enjoy!


PowerShell – Create a Barcode GUI Form and Print to PDF

April 9, 2019

When you need to create a Barcode you can use this method

Download one of the free TTF (True Type Fonts) barcode fonts.

Example : “CCode39”

image

image

Solution :

CLS

Function PrintPDF{

    $PrintPageHandler = {
        param([object]$sender, [System.Drawing.Printing.PrintPageEventArgs]$ev)

    $linesPerPage = 0
    $yPos = 0
    $count = 0
    $leftMargin = $ev.MarginBounds.Left
    $topMargin = $ev.MarginBounds.Top
    $line = $null

    $printFont = New-Object System.Drawing.Font("CCode39",30)

    # Calculate the number of lines per page.
    $linesPerPage = $ev.MarginBounds.Height / $printFont.GetHeight($ev.Graphics)

    # Print each line of the BarCode.
    $yPos = $topMargin + ($count * $printFont.GetHeight($ev.Graphics))
        write-host $yPos " " $count " " $linesPerPage
    $ev.Graphics.DrawString($oLabel.Text, $printFont, [System.Drawing.Brushes]::Black, $leftMargin, $yPos, (New-Object System.Drawing.StringFormat))

    # If more lines exist, print another page.
    if ($line -ne $null) 
      {
        $ev.HasMorePages = $true
      }
    else
      {
        $ev.HasMorePages = $false
      }
   }

    $pd = New-Object System.Drawing.Printing.PrintDocument
    $pd.PrinterSettings = New-Object System.Drawing.Printing.PrinterSettings
    $pd.PrinterSettings.PrinterName = 'Microsoft Print to PDF'
    $pd.PrinterSettings.PrintToFile = $true

    # https://social.technet.microsoft.com/Forums/scriptcenter/en-US/c7351021-800a-4ce9-bfa3-37b54e1750df/printing-a-windows-form?forum=winserverpowershell
    $pd.add_PrintPage($PrintPageHandler)

    $pd.PrinterSettings.PrintFileName = "C:\Temp\BarCode 39.pdf"
    
    $pd.Print()
}


Function CreateForm {

    Add-Type -AssemblyName System.Windows.Forms
    Add-Type -AssemblyName System.drawing
 
    # Create a new Form Object
    $Form = New-Object System.Windows.Forms.Form
 
    # Create the size of your form 
    $Form.width = 1000
    $Form.height = 500
 
    # Set the name of the form 
    $Form.Text = ”This is a Barcode 39 Form”
 
    # Set the font of the text to be used within the form
    $Font = New-Object System.Drawing.Font("Arial",10)
    $Form.Font = $Font
 
    # Add and set the BarCode text and TTF Font
    $oLabel = new-object System.Windows.Forms.Label
    $oLabel.Location = new-object System.Drawing.Size(300,180) 
    $oLabel.size = new-object System.Drawing.Size(600,80) 
    
    $oLabel.Text = "*1234567*"
    $oLabel.Font = New-Object System.Drawing.Font("CCode39",30)
    
    $Form.Controls.Add($oLabel)
   
    # Add a Print Button
    $oButton = New-Object Windows.Forms.Button
    $oButton.Text = "Print to PDF"
    $oButton.Top = 10
    $oButton.Left = 10
    $oButton.Width = 150
    $oButton.Anchor = [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Right 
    
    $oButton.add_click({PrintPDF})
    $oButton.add_click({$oButton.Text = "PDF Created"})
    
    $Form.controls.add($oButton)
 
 
    $Form.Add_Shown({$Form.Activate()})
    
    #Show the Form
    $Form.ShowDialog()| Out-Null
} 

# call the function 
CreateForm

Enjoy ! Smile


VBScript / PowerShell – Automating FTP / SFTP using WinSCP .Net Assembly COM Server

April 9, 2019

When you want to automate WinSCP using a VBScript it is not so straight forward ? Sad smile

First of all you can find the script here :

https://winscp.net/eng/docs/library_session_listdirectory#vbscript

But when you look carefully you will notice that this is not really a VBS Script but a WSH Script ?

What is the difference ? See here

WSH  is a very COM scripting technique, that has been replaced over time by .NET and PowerShell.

But is still available even on Windows 10  Winking smile

 

Prerequisites :

Download the WinSCPnet.dll Assembly from here

And register the .Net Assembly to expose the COM component.

‘ 32 Bit => %WINDIR%\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe WinSCPnet.dll /codebase /tlb:WinSCPnet32.tlb
‘ 64 bit => %WINDIR%\Microsoft.NET\Framework64\v4.0.30319\RegAsm.exe WinSCPnet.dll /codebase /tlb:WinSCPnet64.tlb

PS :

Keep in mind that you also need the WinSCP.exe (from the same version as the WinSCPnet.dll !

So make sure you download both of them to guarantee the compatibility between the 2

 

Solution :

This is the original WSH Code

<job>                                                               
<reference object="WinSCP.Session"/>
<script language="VBScript">
Option Explicit
 
' Setup session options
Dim sessionOptions
Set sessionOptions = CreateObject("WinSCP.SessionOptions")
With sessionOptions
    .Protocol = Protocol_Sftp
    .HostName = "example.com"
    .UserName = "user"
    .Password = "mypassword"
    .SshHostKeyFingerprint = "ssh-rsa 1024 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
End With
 
Dim session
Set session = WScript.CreateObject("WinSCP.Session")
 
' Connect
session.Open sessionOptions
 
' Upload files
Dim transferOptions
Set transferOptions = WScript.CreateObject("WinSCP.TransferOptions")
transferOptions.TransferMode = TransferMode_Binary
 
Dim transferResult
Set transferResult = session.PutFiles("d:\toupload\*", "/home/user/", False, transferOptions)
 
' Throw on any error
transferResult.Check
 
' Print results
Dim transfer
For Each transfer In transferResult.Transfers
    WScript.Echo "Upload of " & transfer.FileName & " succeeded"
Next
 
' Disconnect, clean up
session.Dispose
 
</script>
</job>

 

Convert WHS into a real VBS Script like this :

Option Explicit
 
' Setup session options
Const Protocol_Ftp = 2 ' 0 = SFTP

Dim sessionOptions

Set sessionOptions = CreateObject("WinSCP.SessionOptions")

With sessionOptions
    .Protocol = Protocol_Ftp
    .HostName = "xxx.xx.xxx.xx"
    .UserName = "xxxxxxx"
    .Password = "xxxxx"
    '.SshHostKeyFingerprint = "ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
End With
 
Dim session
Set session = CreateObject("WinSCP.Session")
 
' Connect
session.Open sessionOptions
 
Dim directoryInfo
Set directoryInfo = session.ListDirectory("/")
 
Dim fileInfo
For Each fileInfo In directoryInfo.Files

' To avoid Variable uses an Automation type not supported in VBScript 800A01CA Error
' https://support.microsoft.com/en-us/help/306916/prb-vbscript-type-mismatch-error-when-field-type-is-adnumeric

    WScript.Echo fileInfo.Name  & " with size " & CDbl(fileInfo.Length) & _
        ", permissions " & fileInfo.FilePermissions & _
        " and last modification at " & fileInfo.LastWriteTime
Next
 
' Disconnect, clean up
session.Dispose

 

If you want to use FTP using TLS encryption you need to use these Session Options

Option Explicit
 
' Setup session options
Const Protocol_Ftp = 2
Const Protocol_SFtp = 0

Const Implicit = 0
Const ExplicitTls = 3
Const ExplicitSsl = 2

Dim sessionOptions

Set sessionOptions = CreateObject("WinSCP.SessionOptions")

With sessionOptions
    .HostName = "xxxxxxxxxx"
    .UserName = "xxxxxxxxxx"
    .Password = "xxxxxx"

    .Protocol = Protocol_Ftp
    .FTPSecure =  ExplicitSsl 
    .TlsHostCertificateFingerprint = "xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"

    ' .Protocol = Protocol_SFtp 
    ' .FTPSecure =  ExplicitTls 
    '.SshHostKeyFingerprint = "xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"

End With
Option Explicit
 
' Setup session options
Const Protocol_Ftp = 2
Const Protocol_SFtp = 0

Const Implicit = 0
Const ExplicitTls = 3
Const ExplicitSsl = 2

Dim sessionOptions

Set sessionOptions = CreateObject("WinSCP.SessionOptions")

With sessionOptions
    .HostName = "xxxxxxxxxx"
    .UserName = "xxxxxxxxxx"
    .Password = "xxxxxx"

    .Protocol = Protocol_Ftp
    .FTPSecure =  ExplicitSsl 
    .TlsHostCertificateFingerprint = "xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"

    ' .Protocol = Protocol_SFtp 
    ' .FTPSecure =  ExplicitTls 
    '.SshHostKeyFingerprint = "c4:15:73:e2:3a:c9:7b:cd:9e:a4:f1:ed:00:d2:ff:d7:56:fb:58:58"

End With

To get access to the Protocol and KeyFingerPrint in WinSCP

Go to Sessions – Server/Protocol Information

image

Copy the FingerPrint Key to the keyboard to use in your script

image

Bonus :

The beauty of the WinSCPnet.dll Assembly is that it has 2 interfaces being a DotNet Assembly that is COM Compatible.

As shown above you can use it in any COM Compatible Client (like VBS Script or AutoIT) Smile

1. .Net Assembly – PowerShell

But as well in PowerShell or any other DotNet Compatible Client (like C# or VB .Net or AutoIT !)

Here is how to generate the script from within WinSCP GUI.

Go to Generate Session URL/Code

image

Select .Net Assembly Code / PowerShell

image

Copy to clipboard and you’re off …

2. Scripting : Batch – Commandline and more …

image

For the Batch you need to download the commandline version WinSCP.com

Or you can run the the /console commandline option using the WinSCP.exe

image

ISSUES :

1. File Encoding Windows to UNIX

I had issues sending files from a Windows server to a UNIX server.

All looked OK when inspecting the source files… But the receiving party alarmed me that the files where corrupt Confused smile

There are 3 strange characters preceding the data on the other end ?

image

After investigation these 3 character are an indication that file is a UTF-8 encoding including a BOM

clip_image001

While UNIX don’t these encoded files, but wants ANSI or ASCIIF encoded files.

You can fix it 2 ways :

A. GUI

image

Select Preference of the DESITINATION Site and SET the “remove BOM and EOF marksoption

B. Scripting

The scripting engine of WinSCP has not means of setting this option automatically Sad smile

PowerShell script to save the files before sending as ASCII encoded files

Use this script to inspect the encoding of the files

CLS

Get-Content -Encoding Byte -TotalCount 100 "YourFile.txt" |% {Write-Host ("{0:x2} " -f $_) -NoNewline}; Write-Host

If you see these 3 Bytes : ef bb bf appearing at the beginning of the file  you have a UTF-8 encoded file

image

Save the file using ASCII encoding fixes the issue :

Get-Content "YourFile.txt" | out-file -encoding ASCII "NewFile.txt

 

AutoIT script to save the file as ANSI encoding:

If you open the file in Binary Mode – 16

You can see the same 3 characters appearing

<!– HTML generated using hilite.me –>

$file = FileOpenDialog("Select test file", @DesktopDir, "CSV files (*.csv*)")

 

$hFileOpen = FileOpen ($file,16)
$out = FileRead ($hFileOpen)

 

image

 

To fix it save the file as ANSI Mode – 512

<!– HTML generated using hilite.me –>

$file = FileOpenDialog("Select test file", @DesktopDir, "CSV files (*.csv*)")

 

$hFileOpen = FileOpen ($file,512) ; ANSI Encoding
$out = FileRead ($hFileOpen)

 

2. Event ID Error 8 / 11 Crypt32

When running the GUI I did not have any issues but when running the cmdline options I could not get connected ?

It took me a long time to figger out what the problem was. In the event viewer I saw a lot of Crypt32 Errors ?

image

The reason was that the server did not have Internet Access …

image

Once I fixed it I could log connect … this means that the GUI behaves differently from the cmdline option.

The GUI could connect using port 22 or 21 while the cmdline option needed to have regular internet access to download so Certificate information

Once this was done I could disable the regular internet access again.

 

Conclusion :

Basically the WinSCP options are unlimited on a windows platform.

You just have to choose the right scripting environment, GUI / Command Line / COM / .Net

Test it against your FTP server or SFTP Server

 

Happy Scripting Smile

Enjoy