Showing posts with label C Sharp. Show all posts
Showing posts with label C Sharp. Show all posts

Wednesday, August 14, 2019

How to read an XLSX file in .net code natively, without any 3rd party tools


Much to my surprise, there is no native way to read xlsx files in C# or PowerShell. Recently I had to write code that grabbed values from an xlsx file on SharePoint and then include the values in an email that is programmatically sent out. I couldn't use 3rd party software, so this presented a challenge.

As it turns out, an xlsx file is actually a compressed file and it can be renamed to a .zip file and then the contents (mostly xmls) extracted and mapped to each other.

The code below will download an xlsx file to a local directory or open it from an local directory if the download is not needed and then it extracts the files inside and maps the xmls. I put comments at the top of the code, you need PowerShell 5.0 or later to leverage the extraction function. It also assumes the first sheet in the spreadsheet is named worksheet1, you'll have to pull that programmatically if that is not the case or change the hard coded name. The code maps the xml that carries a list of shared strings (to save space) to the worksheet xml that has a single numerical representation of that shared string inside of it, as well as a row and column delineator. Dates are tricky as Microsoft uses the number of days from January 1st, 1900 to the value as the actual numerical representation, as well as some formatting data in the XML, but if you are like me you only care about the value itself. There is some caveat where MS used 1900 as a leap year, even though it was not, google that if you are off by a day or two.

If you have more than the standard A to Z columns in use, you'll have to write extra code for that and some of the xml symbolic representations I didn't take the time to figure out what they were for as I didn't need to. If someone does, I'd appreciate a comment here explaining what they found.

If you use this code, all of yoru values should easily be accessible via $columnListOfTuples or $rowListOfTuples, which will include a row and column value, as well as the actual cell value.

This was a PITA so I hope this helps others.


<#

    $columnListOfTuples and $rowListOfTuples have everything mapped

    Each xlsx file is a compressed group of xml files into a zip file that is renamed .xlsx
    There is an xml of shared strings that are then represented by a single numerical value to save space
    There is an xml for each sheet in the spread sheet that has numerical references to the values in the shared strings xml
    To produce a virtual/programmatic representation of the spreadsheet, the code will read the sheet, then read the numerical reference than then cross reference that to the shared strings value
    I have no mapped out all functions and xmls in the compressed file, but this is enough to get you started
    It's only configured for columns A - Z, you'll have to write code for additional columsn
    You need powershell 5.0 or later to have the native unzip feature
    It assumes your first worksheet is named worksheet1
#>

cls;

#Region Read Xlsx file

    #Region Create temporary zip file and location

    $fileDate = [DateTime]::Now.ToShortDateString();
    $fileDate = $fileDate.Replace("/", "-");
    $fileTime = [DateTime]::Now.ToShortTimeString();
    $fileTime = $fileTime.Replace(":", ".");
    $fileTime = $fileTime.Replace(" ", ".");
    $fileDateTime = $fileDate + "_" + $fileTime;

    $networkFile = "http://[xxxxx].xlsx";
    $originalFile = "c:\temp\file.xlsx"; # file must exist and location must have writable rights
    #Invoke-WebRequest -Uri $networkFile -OutFile $originalFile -UseDefaultCredentials; # this is to download a network file locally, may not be needed

    $index = $originalFile.LastIndexOf("\");
    $originalFileDirectory = $originalFile.Remove($index);
    $tempCopiedFileName = $originalFileDirectory + "\tempXlsx-" + $fileDateTime + ".zip";
    $desinationPath = $originalFileDirectory + "\xlsxDecompressed";

    #Endregion /Create temporary zip file and location

    #Region Map all of the zip/xlsx file paths

    $relsXmlFile = $desinationPath + "\_rels\.rels";
    $item1XmlRelsXmlFile = $desinationPath + "\customXml\_rels\item1.xml.rels";
    $item2XmlRelsXmlFile = $desinationPath + "\customXml\_rels\item2.xml.rels";
    $item3XmlRelsXmlFile = $desinationPath + "\customXml\_rels\item3.xml.rels";
    $item1XmlFile = $desinationPath + "\customXml\item1.xml";
    $item2XmlFile = $desinationPath + "\customXml\item2.xml";
    $item3XmlFile = $desinationPath + "\customXml\item3.xml";
    $itemProps1XmlFile = $desinationPath + "\customXml\itemProps1.xml";
    $itemProps2XmlFile = $desinationPath + "\customXml\itemProps2.xml";
    $itemProps3XmlFile = $desinationPath + "\customXml\itemProps3.xml";
    $appXmlFile = $desinationPath + "\[filename]\docProps\app.xml";
    $coreXmlFile = $desinationPath + "\[filename]\docProps\core.xml";
    $customXmlFile = $desinationPath + "\[filename]\docProps\custom.xml";
    $workbookXmlRelsXmlFile = $desinationPath + "\xl\_rels\workbook.xml.rels";
    $theme1XmlFile = $desinationPath + "\xl\theme\theme1.xml";
    $sheet1XmlRelsXmlFile = $desinationPath + "\xl\worksheets|_rels|sheet1.xml.rels";
    $workSheet1XmlFile = $desinationPath + "\xl\worksheets\sheet1.xml";
    $sharedStringsXmlFile = $desinationPath + "\xl\sharedStrings.xml";
    $stylesXmlFile = $desinationPath + "\xl\styles.xml";
    $workbookXmlFile = $desinationPath + "\xl\workbook.xml";
    $contentTypesXmlFile = $desinationPath + "\[Content_Types].xml";

    #Endregion /Map all of the zip/xlsx file paths

    Copy-Item $originalFile -Destination $tempCopiedFileName -Recurse; # copy the xlsx file as a temporary zip file
    Expand-Archive -LiteralPath $tempCopiedFileName -DestinationPath $desinationPath -Force; # unzip the file
    [xml]$fileLines = Get-Content($sharedStringsXmlFile); # read the contents of the shared strings file

    #Region Map shared strings

    $arr_SharedStrings = New-Object System.Collections.Generic.List[String];
    for ($i = 0; $i -lt $fileLines.sst.si.Count; $i++ )
    {
        $line = $fileLines.sst.si[$i];
        $tuple = [tuple]::create([int]$i, [string]("`"" + $line.InnerText + "`"") );
        $arr_SharedStrings.Add($tuple); # map the shared strings to the representational number inside of the xml files
    }

    #Endregion /Map shared strings

    [xml]$fileLines = Get-Content($workSheet1XmlFile);
    $sheetData = $fileLines.worksheet.sheetData;

    #Region Map rows
    $arr_sheet1_rows = New-Object System.Collections.ArrayList; # this will have a list/tuple of each cell that makes up a row
    for ($i = 0; $i -lt $sheetData.row.Count; $i++ )
    {
        $arr_sheet1_individualRow = New-Object System.Collections.Generic.List[String];
        $rowElements = $sheetData.row[$i].c; #grab data of all cells in that row
        foreach ($element in $rowElements)
        {
            $elementsTuple = [tuple]::create([string]$element.r, [string]$element.s, [string]$element.v)
            $arr_sheet1_individualRow.Add($elementsTuple);      
        }

        $rowTuple = [Tuple]::create([int] $i+1, $arr_sheet1_individualRow);
        $arr_sheet1_rows.Add($rowTuple) | Out-Null;
    }

    $rowListOfTuples = New-Object System.Collections.ArrayList; # has row number, then column letter, then cell value
    foreach ($rowMapped in $arr_sheet1_rows)
    {
        $cellValues = $rowMapped.Item2;
        $rowNumber = $rowMapped.Item1;
        foreach ($cellValue in $cellValues)
        {
            $cellValue = $cellValue.Remove(0, 1);
            $cellValue = $cellValue.Remove($cellValue.Length - 1, 1);
            $cellValue = $cellValue.Replace(" ", "");
            $cellValuesSplit = $cellValue.Split(",");
            $columnLetter = $cellValuesSplit[0];
            $columnLetter = $columnLetter -replace '[0-9]',''
            $cellValueOnly = $cellValuesSplit[2];

            $cellTuple = [tuple]::create([int]$rowNumber, [string]$columnLetter, [string]$cellValueOnly);
            $rowListOfTuples.Add($cellTuple) | Out-Null;
        }
    }

    #Endregion /Map shared rows

    #Region Map shared columns

    $rowCount = $arr_sheet1_rows.Count;
    $sheetDataCols = $fileLines.worksheet.cols;
    $arr_sheet1_columns = New-Object System.Collections.ArrayList; # this will have a list/tuple of each cell that makes up a column
    #$arr_sheet1_columns.Add("Place holder") | Out-Null;
    #for ($i = 0; $i -lt $sheetDataCols.col.Count; $i++ )
    for ($i = 0; $i -lt $rowCount; $i++ )
    {
        if ($arr_sheet1_columns.Count -lt $sheetDataCols.col.Count)
        {
            for ($j = 0; $j -lt $sheetDataCols.col.Count; $j++ )
            {
                switch  ($j)
                {
                    0 { $columnLetterTranslated = "A"; break; }
                    1 { $columnLetterTranslated = "B"; break; }
                    2 { $columnLetterTranslated = "C"; break; }
                    3 { $columnLetterTranslated = "D"; break; }
                    4 { $columnLetterTranslated = "E"; break; }
                    5 { $columnLetterTranslated = "F"; break; }
                    6 { $columnLetterTranslated = "G"; break; }
                    7 { $columnLetterTranslated = "H"; break; }
                    8 { $columnLetterTranslated = "I"; break; }
                    9 { $columnLetterTranslated = "J"; break; }
                    10 { $columnLetterTranslated = "K"; break; }
                    11 { $columnLetterTranslated = "L"; break; }
                    12 { $columnLetterTranslated = "M"; break; }
                    13 { $columnLetterTranslated = "N"; break; }
                    14 { $columnLetterTranslated = "O"; break; }
                    15 { $columnLetterTranslated = "P"; break; }
                    16 { $columnLetterTranslated = "Q"; break; }
                    17 { $columnLetterTranslated = "R"; break; }
                    18 { $columnLetterTranslated = "S"; break; }
                    19 { $columnLetterTranslated = "T"; break; }
                    20 { $columnLetterTranslated = "U"; break; }
                    21 { $columnLetterTranslated = "V"; break; }
                    22 { $columnLetterTranslated = "W"; break; }
                    23 { $columnLetterTranslated = "X"; break; }
                    24 { $columnLetterTranslated = "Y"; break; }
                    25 { $columnLetterTranslated = "Z"; break; }
                }
                $arr_sheet1_columns.Add($columnLetterTranslated) | Out-Null;
            }       
        }

        $rowElements = $sheetData.row[$i].c; #grab data of all cells in that row
        foreach ($element in $rowElements)
        {
            $columnLetter = $element.r -replace '[^a-zA-Z-]',''
            for ($k = 0; $k -lt $arr_sheet1_columns.Count; $k++)
            {
                $column = $arr_sheet1_columns[$k];
                if ($column.StartsWith($columnLetter))
                {
                    $columnTemp = $column.ToString() + "|" + $element.v;
                    $arr_sheet1_columns.Remove($column);
                    $arr_sheet1_columns.Insert($k, $columnTemp);
                    $stop = "here";
                }
            }
        }
    }

    $columnListOfTuples = New-Object System.Collections.ArrayList; # has column letter, than row number, then cell value
    foreach ($columnMapped in $arr_sheet1_columns)
    {
        $index = $columnMapped.IndexOf("|");
        $columnMappedLetter = $columnMapped.Remove($index);

        $columnIndividualValues = $columnMapped.Remove(0, $index);
        $columnIndividualValuesSplit = $columnIndividualValues.Split("|");

        $rowNumber = 0;
        foreach($columnIndividualValueSplit in $columnIndividualValuesSplit)
        {   
            $cellTuple = [tuple]::create([string]$columnMappedLetter, [int]$rowNumber, [string]$columnIndividualValueSplit);
            $columnListOfTuples.Add($cellTuple) | Out-Null;
            $rowNumber ++;
        }
    }

    #Endregion /Map shared columns



    # Here you have all of the data and you can parse it however you want, below is an example of that

    #Region Parse the extracted data

    $rowsForParsing = New-Object System.Collections.ArrayList;
    foreach ($arr_sheet1_row in $arr_sheet1_rows)
    {
        $rowNumber = $arr_sheet1_row.Item1;
        $cellValues = New-Object System.Collections.ArrayList; 
        $rowTemp = "";

        $indexer = 0;
        foreach ($rowValue in $arr_sheet1_row.Item2)
        {
            $valueSplit = $rowValue.Replace(" ", "");
            $valueSplit = $valueSplit.Replace("(", "");
            $valueSplit = $valueSplit.Replace(")", "");
            $valueSplit = $valueSplit.Split(",");

            $cellLocation = "";
            $cellLocation = $valueSplit[0];
            $cellSType = "";
            $cellSType = $valueSplit[1];
            $cellStringRepresentationValue = "";
            $cellStringRepresentationValue = $valueSplit[2];
            $translatedValue = "";

            if ($cellStringRepresentationValue -ne "")
            {
                switch ($cellSType)
                {
                    "1" { $translatedValue = getStringMappedValue($cellStringRepresentationValue); break;}  # represents column headers
                    "2" { $translatedValue = [datetime]::FromOADate($cellStringRepresentationValue); break;}  # number of days from January 1, 1900
                    "3" { $translatedValue = getStringMappedValue($cellStringRepresentationValue); break;}  # mapped value to strings table
                    "4" {$translatedValue = getStringMappedValue($cellStringRepresentationValue); break;}  # mapped value to strings table
                    "5" { $translatedValue = getStringMappedValue($cellStringRepresentationValue); break;}  # mapped value to strings table
                    "6" { Write-Host "#6: " $rowValue; break;}  # not sure what 6 represents yet
                    "7" { Write-Host "#7: " $rowValue; break;}  # not sure what 7 represents yet
                    "8" { Write-Host "#8: " $rowValue; break;}  # not sure what 8 represents yet
                    "9" { Write-Host "#9: " $rowValue; break;}  # not sure what 9 represents yet
                    "10" { Write-Host "#10: " $rowValue; break;}  # not sure what 10 represents yet
                    "11" { Write-Host "#11: " $rowValue; break;}  # not sure what 11 represents yet     
                }
            }

            if (($rowTemp -eq "") -and ($indexer -eq 0))
            {
                $rowTemp = "$translatedValue";
            }
            else
            {       
                $rowTemp = "$rowTemp" + "|" + "$translatedValue";
            }   

            $indexer++;
        }

        if ($rowNumber -eq 22)
        {
            $stop = "here";
        }

        $rowsForParsing.Add("$rowNumber|$rowTemp") | Out-Null;
    }

    $fileInformation = New-Object System.Collections.ArrayList;
    $topRow = $rowsForParsing[1];
    foreach ($rowParsed in $rowsForParsing)
    {
        $rowParsedSplit = $rowParsed.Split("|");    
        $date = $rowParsedSplit[1];

        $pass = $true;
        try
        {
            $dtVal = get-date $date;
        }
        catch
        {
            $pass = $false;
        }

        if ($pass -eq $true)
        {
            if ((get-date) -gt (get-date $date))
            {
                $dateApplicableRow = $rowParsed;
            }
            else
            {
                Write-Host "Top row is $topRow";
                Write-Host "This weeks row is $dateApplicableRow";
                $fileInformation.Add($topRow);
                $fileInformation.Add($dateApplicableRow);
                break;
            }
        }
    }

    #Endregion /Parse the extracted data

#EndRegion /Read Xlsx file

Tuesday, July 19, 2016

How to run multiple PowerShell commands in c# (including pre-environment commands like set-adserversettings -viewentireforest:$true)



This allows me to create a script in a string with multiple commands, it processes 1 command at a time and then creates a runspace in c# and runs all of the powershell commands. The amount of commands that you could put inline in the string is endless.


<pre lang="C#">string scriptText = @"$pw = convertto-securestring -AsPlainText -Force -String '<password>'; $cred = new-object -typename System.Management.Automation.PSCredential -argumentlist '<domain>\<username>', $pw;  $session = new-pssession -ConfigurationName Microsoft.Exchange -ConnectionUri '<server url>/powershell' -credential $cred; import-pssession $session; Set-ExecutionPolicy bypass -confirm:$false -force; $pic = ([System.IO.File]::ReadAllBytes('" + <picture file name> + "')); set-userphoto -identity <username> -picturedata $pic -domaincontroller '<dc fqdn>' -confirm:$false;";

              
                runExchangeShellScript(scriptText);</pre>


<pre lang="C#">private string runExchangeShellScript(string scriptText)
        {
           
            Runspace runspace = RunspaceFactory.CreateRunspace();

            // open it
            runspace.Open();

            // create a pipeline and feed it the script text
            Pipeline pipeline = runspace.CreatePipeline();
            pipeline.Commands.AddScript(scriptText);
            RunspaceInvoke runSpaceInvoker = new RunspaceInvoke(runspace);
            runSpaceInvoker.Invoke(&quot;Set-ExecutionPolicy Unrestricted&quot;);

            // add an extra command to transform the script output objects into nicely formatted strings
            // remove this line to get the actual objects that the script returns. For example, the script
            // &quot;Get-Process&quot; returns a collection of System.Diagnostics.Process instances.
            //pipeline.Commands.Add(&quot;Out-String&quot;);

            // execute the script
            Collection&lt;PSObject&gt; results = null;
            try
            {
                results = pipeline.Invoke();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException + &quot; - &quot; + ex.Message + &quot; - &quot; + ex.Source + &quot; - &quot; + ex.StackTrace + &quot; - &quot; + ex.TargetSite + &quot; - &quot; + ex.Data);
                return &quot;&quot;;
            }


            // close the runspace
            runspace.Close();

            // convert the script result into a single string
            StringBuilder stringBuilder = new StringBuilder();
            foreach (PSObject obj in results)
            {
                stringBuilder.AppendLine(obj.ToString());
            }

            // return the results of the script that has
            // now been converted to text
            return stringBuilder.ToString();
        }</pre>

Friday, February 8, 2013

Browse the registry just like a UNC path

Periodically I am searching for a registry path and I stumble upon something on the internet telling me to go 10+ subkeys into a hive to find a value. Although not terrible, it's kind of annoying to switch back and forth between regedit and the browser to follow the path down to the key that I need.

After some googling, I found that Mark Russinovich made a command line version of a tool for this, but I wanted a GUI so I decided to write my own registry jump browser tool. It's pretty simple to use, you plug the key into the input box and hit enter or click the browse registry button.

Format for the key path should look like

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Test Key\Test Sub Key
or
hklm\SOFTWARE\Microsoft\Test Key\Test Sub Key

and if the key is present, it will open the registry and navigate down to the key you've input. If the key does not exist, it will tell you that as well.

You may also use a command line version of this using the format below in the command prompt (remember to navigate to it)
regbrowser "hklm\software\microsoft"

You can only have 1 instance of the registry open when doing this, and it is designed for local registry browsing only, FYI.

Enjoy and leave a comment if it is useful for you, or if you have a suggested improvement.



http://www5.zippyshare.com/d/25056500/3177663/RegBrowser.exe

^ new version with help file built in and minor added functionality



http://www17.zippyshare.com/d/54500288/4459293/Registry%20Browser.zip

^ Sorry blogspot doesn't host .zip files

Friday, August 3, 2012

How to get around, bypass or defeat "A program is trying to automatically send email on your behalf" in Outlook 2010

If you've encountered this error like I have, you know it is a pain in the ass. You're writing code and trying to test it and you get blocked by Outlook security. Today I found a good solution that should be more widely available on the internet, then it is. These two registry keys disable that functionality. A C sharp code example is below and what I used to send emails in a console app. It loops and send a new incrementing subject email every 5 seconds. The code is from the internet and other people, that I've tweaked, same as the reg hacks. I have not tried this with some of the other programmatic ways to interact with Office, nor with other versions of Office. This may also work or affect the "A program is trying to access e-mail addresses you have stored in Outlook" as well.


[HKEY_CURRENT_USER\Software\Policies\Microsoft\Security] "CheckAdminSettings"=dword:00000002
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Outlook\Security] "ObjectModelGuard"=dword:00000002 



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Threading;

/*

 Windows Registry Editor Version 5.00

    [HKEY_CURRENT_USER\Software\Policies\Microsoft\Security] "CheckAdminSettings"=dword:00000002
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Outlook\Security] "ObjectModelGuard"=dword:00000002 
  
  
  
 */

namespace LateBindingTest
{
    class OutlookEmailerLateBinding
    {

        private object oApp;
        private object oNameSpace;
        private object oOutboxFolder;
        public static int messageNumber = 0;

        public OutlookEmailerLateBinding()
        {
            int loopNumber = 0;
            Type outlook_app_type;
            object[] parameter = new object[1];
            //Get the excel object
            outlook_app_type = Type.GetTypeFromProgID("Outlook.Application");
            //Create instance of excel
            oApp = Activator.CreateInstance(outlook_app_type);
            //Set the parameter which u want to set
            parameter[0] = "MAPI";
            //Set the Visible property
            oNameSpace = outlook_app_type.InvokeMember("GetNamespace",
            BindingFlags.InvokeMethod, null, oApp, parameter);

            var Logon_parameter = new object[4] { null, null, true, true };
            oNameSpace.GetType().InvokeMember("Logon",
            BindingFlags.InvokeMethod, null, oNameSpace, Logon_parameter);

            var GetDefaultFolder_parameter = new object[1] { 6 };
            oOutboxFolder =
            oNameSpace.GetType().InvokeMember("GetDefaultFolder",
            BindingFlags.InvokeMethod, null, oNameSpace,
            GetDefaultFolder_parameter);

            Console.WriteLine(messageNumber + " - Press enter to exit");
        }

        static void Main(string[] args)
        {
            //int messageNumber = 0;
            while (true)
            {
                messageNumber += 1;
                try
                {
                    OutlookEmailerLateBinding app = new OutlookEmailerLateBinding();
                    app.SendOutlookEmail("user1@domain.com; user2@domain.com", "Duplicate test message email " + messageNumber, "Test message. Testing. Only reply back if you see a duplicate.");
                }
                catch (Exception e)
                {
                    Console.WriteLine("Error Stack {0} ", e.Message);
                }

                //Console.ReadLine();

                Thread.Sleep(5000);
            }
        }

        public void SendOutlookEmail(string toValue, string
subjectValue, string bodyValue)
        {
            var CreateItem_parameter = new object[1] { 0 };
            object oMailItem =
oApp.GetType().InvokeMember("CreateItem", BindingFlags.InvokeMethod,
null, oApp, CreateItem_parameter);

            var mail_item_type = oMailItem.GetType();
            mail_item_type.InvokeMember("To",
                BindingFlags.SetProperty, null, oMailItem, new
object[] { toValue });
            mail_item_type.InvokeMember("Subject",
                BindingFlags.SetProperty, null, oMailItem, new
object[] { subjectValue });
            mail_item_type.InvokeMember("Body",
                BindingFlags.SetProperty, null, oMailItem, new
object[] { bodyValue });
            mail_item_type.InvokeMember("Send",
                BindingFlags.InvokeMethod, null, oMailItem, null);

        }

    }
}




On a side note, here is how to do it in PowerShell in a slightly different way by connecting directly to the Client Access Server (CAS).
.
if ( (Get-PSSnapin | Where-Object { $_.Name -eq "Microsoft.Exchange.Management.PowerShell.Admin"}) -eq $null )
  {
    add-pssnapin Microsoft.Exchange.Management.PowerShell.Admin
  }


 $emailattachment = "xx" # full path to attachment file

 $body = "xx" # body of email

 $smtp = new-object Net.Mail.SmtpClient("xxx") # Dns name or IP of your cas server
 $smtpuser = new-object System.Net.networkCredential
 $smtpuser.domain = "xx" # your domain
 $smtpuser.username = "xx" # your username
 $smtpuser.password = "xx" # your password
 $smtp.Credentials = $smtpuser
 
 $SmtpClient = new-object system.net.mail.smtpClient
 $MailMessage = New-Object system.net.mail.mailmessage
 $SmtpClient.Host = "xxx" # Dns name or IP of cas server
 $mailmessage.from = "xxx" # your email address
 $mailmessage.To.add("xx@xx.xx") # email address to send to
 $mailmessage.Subject = "xxx" # subject
 $MailMessage.IsBodyHtml = $False
 $mailmessage.Body = $body 
 $attachment = New-Object System.Net.Mail.Attachment($emailattachment, 'text/plain') # you may have to change the file type or encrypt file with 7zip to get around gmail file attachment restrictions
   $mailmessage.Attachments.Add($attachment)
 $smtp.Send($MailMessage)