locked
Attaching file to email when email contains recipient RRS feed

  • Question

  • Hi PS Experts,

    I realize that Send-MailMessage is the updated way to send email via Powershell but some of us don't have credentials to access a SMTP server.  So I will be using the comObject from Outlook.

    I am having issues with the below code where I want to attach a file where the name is contains the recipients name assigned to the variable $Assignee.Name associated with the process the recipient of the email is assigned to.

     The line of code I am having issue with is $attatch = $mItem.Attachments.Add($files).  It sends A attachment to both recipients with only the last file in the list.  I need the script to send a attachment to each individual where the attachment name contains the Assignee name. 

     

    I have tried   $attatch = $mItem.Attachments.Add($files | where {$_.name -contains $($Assignee.Name}) and no attachment is sent as well as it being incorrect code.  Below is the whole code.

     

     

    $process = Get-Process
    $exportfile =  'D:\Documents\custom.csv'
    $time = [datetime]::Now
    $props = foreach($proc in $process){
    if ( $proc | Where Name -eq "WUDFHost") {
    $procName = $proc.Name
    $procDesc =$proc.Description
    $procID = $proc.Id
    $assignee = 'Me_Netflix@yahoo.com'
    }
    else {
    $procName = $proc.Name
    $procDesc =$proc.Description
    $procID = $proc.Id
    $assignee = 'Prime@test123.xyz.edu'
    }
    [PSCustomObject] @{
    "Proccess Name" = $procName
    "Proccess Description" = $procDesc
    "procID" = $procID
    "Assignee" =  $assignee
    }
    }
    $props|  Export-Csv $exportfile
    $exportfile =  'D:\Documents\custom.csv'
    foreach($Owner in (Import-Csv $exportfile | group 'Assignee')) {
    $newExport = "D:\Documents\custom.csv\$($Owner.Name).xlsx"
    $Owner.group | Export-Excel $newExport 
    $newExport
    $files = $newExport | select $_.fullname 
    }
    foreach($Assignee in (Import-csv $exportfile | group 'Assignee')) {
    $Assignee.Group  |Sort-Object Process -Descending | Format-List | Out-Host
    $OL = New-Object -ComObject outlook.application
    $mItem = $OL.createitem(0)
    $mItem.Sender = "Me@Toyou.fscxyzj.edu"
    $recip = $mItem.Recipients.Add($Assignee.Name)
    $attatch = $mItem.Attachments.Add($files)
    $mItem.Subject = "TEST"
    
    $a = "<style>"
        $a = $a + "BODY{background-color:peachpuff;}" 
        $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
        $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
        $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:palegoldenrod}" 
        $a = $a + "</style>"
    
    $data= $Assignee.Group  |Sort-Object Process -Descending | ConvertTo-Html -Head $a -Body "<H2>Test</H2>"
    $body =  "<head>
    <title>Please Help </title>
    </head>
    <BODY style = font-size: 12pt;font-family:Arial>
    <body>
    <article>
    <!--Article heading-->
    <h1>Please Help</h1>
    <!--Inline image insert from the mp_photo1.png file-->
    <img src=C:\Users\User\OneDrive\pic.png id=logo alt=Somethong />
    <!-- p element to start paragraph.<strong> emphasizes texr-->
    <p><strong>This is just a test.</strong> You are receiving this email
    because no response has been received via the Technet SharePoint site.
    Your response is<strong>REQUIRED</strong> appreciated.$time</p>
    </body>"
    $mItem.HTMLBody = $body
    $mItem.Importance = 2
    $mItem.Send() 
    Write-Host("Sent to " +  $($Assignee.Name)).ToUpper() -BackgroundColor Black -ForegroundColor Green
        [PSCustomObject] @{
    "Recipients" = "Sent to " +  $($Assignee.Name)
    "Time" = $time 
    } | Export-csv D:\Documents\Report.csv -NoTypeInformation -Append
    
    }





    • Edited by 3k88EIL Thursday, July 23, 2020 4:24 PM Inserted Code using Code block and removed whites spaces
    Thursday, July 23, 2020 2:55 AM

Answers

  • Here is an example of how to eliminate issues caused by copying code without understanding how it works.

    # at beginning of script
    $style = @'
    <style>"
       BODY{background-color:peachpuff;}
        TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
       TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}
        TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:palegoldenrod} 
    </style>
    '@
    $bodytmplt = @'
    <head>
        <title>Please Help </title>
    </head>
    <body style = font-size: 12pt;font-family:Arial>
        <body>
        <article>
        <!--Article heading-->
        <h1>Please Help</h1>
        <!--Inline image insert from the mp_photo1.png file-->
        <img src=C:\Users\User\OneDrive\pic.png id=logo alt=Somethong />
        <!-- p element to start paragraph.<strong> emphasizes texr-->
        <p><strong>This is just a test.</strong> You are receiving this email
        because no response has been received via the Technet SharePoint site.
        Your response is<strong>REQUIRED</strong> appreciated.{0}</p>
    </body>
    '@
    $ol = New-Object -ComObject outlook.application
    
    
    # later in code update the body and send the message
    foreach($Assignee in (Import-csv $exportfile | group 'Assignee')) {
        $mItem = $ol.createitem(0)
        $mItem.Sender = 'Me@Toyou.fscxyzj.edu'
        $recip = $mItem.Recipients.Add($Assignee.Name)
        $attatch = $mItem.Attachments.Add($newExport)
        $mItem.Subject = 'TEST'
        $mItem.HTMLBody = $bodytmplt -f ([datetime]::Now)
        $mItem.Importance = 2
        $mItem.Send()
        $mItem.Dispose()
    }


    \_(ツ)_/


    • Edited by jrv Thursday, July 23, 2020 7:09 PM
    • Marked as answer by jrv Tuesday, July 28, 2020 5:49 PM
    Thursday, July 23, 2020 7:09 PM

All replies

  • Please fix your original post according to forum rules:

    See this for instructions: How to post code in Technet Forums 


    \_(ツ)_/

    Thursday, July 23, 2020 3:04 AM
  • Code has been reformatted
    Thursday, July 23, 2020 2:09 PM
  • Code has been reformatted

    It has been posted but not formatted.  It is impossible to follow.

    Please read the following to learn how to correctly format code in PowerShell.

    Please don't make people try to read through something that is just a mess to look at and nearly impossible to read as code.

    We do not double space code.  Blank lines should only be used to delimit logical blocks of code for readability.  Adding a dozen blank lines makes code impossible to follow.  Unreadable code is hard to review and see errors.


    \_(ツ)_/

    Thursday, July 23, 2020 3:16 PM
  • You have not saved your edited mail item.


    \_(ツ)_/

    Thursday, July 23, 2020 3:18 PM
  • You are trying to treat a string that contains t a file name as a file object.  This will result in a missing attachment.

    If you could make your code readable you would see this and other errors.

    Copying and editing code that you do not understand usually leads to this kind of issue.  Well formatted code helps you to see mistakes.


    \_(ツ)_/

    Thursday, July 23, 2020 3:25 PM
  • I have reformatted the code removing blank lines and selecting Powershell as the language.

    I added the .tostring() to my $file variable.  It attaches the file but not to the correct Recipient.  It only sends the last file in the list to recipients.

    I am looking to have each file attached to the email where the file name is included in the recipients name, which comes from the $Assignee.Name variable.

    I understand all of MY code and what each line does.  I have not just copied and pasted with the exception to the HTML portion.  Sure I have had help and used examples to come to my solution but I think most of us learned to code this way. 

    I do know I am not a Powershell expert so I have came here to ask for help from the experts.  Complete code below.

    • Edited by jrv Thursday, July 23, 2020 6:51 PM Removed unnecessary code
    Thursday, July 23, 2020 4:47 PM
  • You need to fix how you are adding attachments and validate that you actually have a file that is not null.

    You also need to read teh link I posted and format the original post correctly.  Continuously posting the same badly formatted code does not help you or us.


    \_(ツ)_/

    Thursday, July 23, 2020 6:54 PM
  • Here is a likely cause of your issue:

    $newExport = "D:\Documents\custom.csv\$($Owner.Name).xlsx"
    $Owner.group | Export-Excel $newExport 
    $newExport
    $files = $newExport | select $_.fullname 
    

    $newexport is a string and not a file object.  It does not have a property called "fullname".

    You need to step back and learn basic PowerShell.  Copying and editing code you do not understand will create many issues for you and will be very frustrating.


    \_(ツ)_/

    Thursday, July 23, 2020 7:07 PM
  • Here is an example of how to eliminate issues caused by copying code without understanding how it works.

    # at beginning of script
    $style = @'
    <style>"
       BODY{background-color:peachpuff;}
        TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
       TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}
        TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:palegoldenrod} 
    </style>
    '@
    $bodytmplt = @'
    <head>
        <title>Please Help </title>
    </head>
    <body style = font-size: 12pt;font-family:Arial>
        <body>
        <article>
        <!--Article heading-->
        <h1>Please Help</h1>
        <!--Inline image insert from the mp_photo1.png file-->
        <img src=C:\Users\User\OneDrive\pic.png id=logo alt=Somethong />
        <!-- p element to start paragraph.<strong> emphasizes texr-->
        <p><strong>This is just a test.</strong> You are receiving this email
        because no response has been received via the Technet SharePoint site.
        Your response is<strong>REQUIRED</strong> appreciated.{0}</p>
    </body>
    '@
    $ol = New-Object -ComObject outlook.application
    
    
    # later in code update the body and send the message
    foreach($Assignee in (Import-csv $exportfile | group 'Assignee')) {
        $mItem = $ol.createitem(0)
        $mItem.Sender = 'Me@Toyou.fscxyzj.edu'
        $recip = $mItem.Recipients.Add($Assignee.Name)
        $attatch = $mItem.Attachments.Add($newExport)
        $mItem.Subject = 'TEST'
        $mItem.HTMLBody = $bodytmplt -f ([datetime]::Now)
        $mItem.Importance = 2
        $mItem.Send()
        $mItem.Dispose()
    }


    \_(ツ)_/


    • Edited by jrv Thursday, July 23, 2020 7:09 PM
    • Marked as answer by jrv Tuesday, July 28, 2020 5:49 PM
    Thursday, July 23, 2020 7:09 PM
  • The files exist based on your example I get the following output inserting the below in my first for loop:

    $attatch = Get-Item $newExport | select fullname | Group-Object -Property fullname | where {$_.FullName} -Like $($Owner.Name) | Out-Host

    Which displays my files entire path:

    The logic I am trying to come up with is how can I group the attachments to send to the Assignee if the file name contains the Assignee name in the second for loop where I created the comObject/

    #
    # Script.ps1
    #
    #Gets Process
    $process = Get-Process
    #Exports Processes to csv
    $exportfile =  'D:\Documents\custom.csv'
    #defines time
    $time = [datetime]::Now
    #for loop to assign processes based on conditions
    $props = foreach($proc in $process){
    
      if ( $proc | Where Name -eq "WUDFHost") {
        $procName = $proc.Name
        $procDesc =$proc.Description
        $procID = $proc.Id
        $assignee = 'Me_Netflix@yahoo.com'
      }
      else {
        $procName = $proc.Name
        $procDesc =$proc.Description
        $procID = $proc.Id
        $assignee = 'Prime@test123.xyz.edu'
      }
        [PSCustomObject] @{
        "Proccess Name" = $procName
        "Proccess Description" = $procDesc
        "procID" = $procID
        "Assignee" =  $assignee
        }
    
    }
    #Export props variable data with Ownership assignment
    $props|  Export-Csv $exportfile
    
    #redifine exportfile for readability.  Understand this is redundant 
    $exportfile =  'D:\Documents\custom.csv'
    
    #for loop that Imports exportfile and create new files based on assignee column
    foreach($Owner in (Import-Csv $exportfile | group 'Assignee')) {
        $newExport = "D:\Documents\custom.csv\$($Owner.Name).xlsx"
        $Owner.group | Export-Excel $newExport 
        $newExport
        #attempting to group property to send to each assignee.  Owner and assignee are the same value with different variable names.
        $attatch = Get-Item $newExport | select fullname | Group-Object -Property fullname | where {$_.FullName} -Like $($Owner.Name)
    }
    
    #for loop to group processes by asignee and embed process and assignee data in HTML data table to send in emal 
    foreach($Assignee in (Import-csv $exportfile | group 'Assignee')) {
        $Assignee.Group  |Sort-Object Process -Descending | Format-List | Out-Host
        $OL = New-Object -ComObject outlook.application
        $mItem = $OL.createitem(0)
        $mItem.Sender = "Me@Toyou.fscxyzj.edu"
        $recip = $mItem.Recipients.Add($Assignee.Name)
        #Using the variable $newExport I can attach the last file in the list to both recipients
        #However I am looking to attach the file to the correct recipient if the file contains Assignee name
        $attachment = $mItem.Attachments.Add($attatch)
        $mItem.Subject = "TEST"
        #HTM Table
        $a = "<style>"
        $a = $a + "BODY{background-color:peachpuff;}" 
        $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
        $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
        $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:palegoldenrod}" 
        $a = $a + "</style>"
        #Data to insert into table
        $data= $Assignee.Group  |Sort-Object Process -Descending | ConvertTo-Html -Head $a -Body "<H2>Test</H2>"
        #HTML Body Insert into Emai;
        $body =  "<head>
        <title>Please Help </title>
        </head>
        <BODY style = font-size: 12pt;font-family:Arial>
        <body>
          <article>
           <!--Article heading-->
           <h1>Please Help</h1>
           <!--Inline image insert from the mp_photo1.png file-->
           <img src=C:\Users\User\OneDrive\pic.png id=logo alt=Somethong />
             <!-- p element to start paragraph.<strong> emphasizes text-->
             <p><strong>This is just a test.</strong> You are receiving this email
              because no response has been received via the Technet SharePoint site.
             Your response is<strong>REQUIRED</strong> appreciated.$time</p>
        </body>"
        $mItem.HTMLBody = $body
        $mItem.Importance = 2
        $mItem.Send() 
        Write-Host("Sent to " +  $($Assignee.Name)).ToUpper() -BackgroundColor Black -ForegroundColor Green
        [PSCustomObject] @{
        "Recipients" = "Sent to " +  $($Assignee.Name)
        "Time" = $time 
        } | Export-csv D:\Documents\Report.csv -NoTypeInformation -Append
    }
    

    I reformatted and separated the logical blocks of code and even used visual studio to help and used the insert block statement.

    The above is one of many approaches I used.  I am able to to send a attachment but not able to group attachment by recipient name to send them to each designed assignee.

    Friday, July 24, 2020 1:56 AM
  • You will have to learn PowerShell in order to understand what I am trying to show you.

    YOU cannot get a property from a string which is what your code is doing.  This shows you have not learned basic PowerShell and programming with a programming system or language.

    I cannot help you until you are able to understand what I am posting.

    If you take the time to look closely at your code and try to understand what it is doing you will see.  You will need to learn basic PowerShell to do that.


    \_(ツ)_/

    Friday, July 24, 2020 2:40 AM
  • Hopefully I can help someone out on this.  I was able to get this completed by creating an empty collection variable for my files:

     $files= @()

    In the subsequent foreach life I added the newfiles assigned to the $newExport variable to the $files variable by using $files += $newExport

    $props|  Export-Csv $exportfile
    $exportfile =  'C:\Users\User\Documents\custom.csv'
    foreach($Owner in (Import-Csv $exportfile | group 'Assignee')) {
        $newExport = "C:\Users\User\Documents\$($Owner.Name).xlsx"
        $Owner.group | Export-Excel $newExport 
         
         $files += $newExport
       
    }

    In the next foreach loop I added a variable named $receiver that includes the $recip.name.

    From there I added a foreachloop with a nest if to check to see IF the files name is like and the receiver name is like to attach the file to the right recipient. 

    $recip = $mItem.Recipients.Add($Assignee.Name)
        $recievers = $recip.Name
        foreach($file in $files){
      if ($file -like '*bart.simpson@students.elmentary.edu*' -and $receivers  -like  '*bart.simpson@students.elmentary.edu*' ) {
        $mItem.Attachments.Add($file)
      }elseif($file -like '*homer_simpson@yahoo.com*'  -and $recievers -like  '*homer_simpson@yahoo.com*' ) {
        $mItem.Attachments.Add($file)
      }else {
         Write-Host ("DOPE") -ErrorAction Stop
      }
        }

    Sure this can be done another way and with less hard hard coding if using an AD attributes like email and user name but hopefully this gets you started.

    Turns out it was not about having a basic understand of PowerShell but just needing to add extra logic to code.  Full code below.

    $process = Get-Process
    $exportfile =  'C:\Users\User\Documents\custom.csv'
    $time = [datetime]::Now
    
    $props = foreach($proc in $process){
    	if ( $proc | Where Name -eq "WUDFHost") {
    	$procName = $proc.Name
    	$procDesc =$proc.Description
    	$procID = $proc.Id
    	$assignee = 'homer_simpson@yahoo.com'
    	}
    	else {
    	$procName = $proc.Name
    	$procDesc =$proc.Description
    	$procID = $proc.Id
    	$assignee = 'bart.simpson@students.elmentary.edu'
    	}
    	[PSCustomObject] @{
    	"Proccess Name" = $procName
    	"Proccess Description" = $procDesc
    	"procID" = $procID
    	"Assignee" =  $assignee
    	}
    }
    
    $files= @()
    $props|  Export-Csv $exportfile
    $exportfile =  'C:\Users\User\Documents\custom.csv'
    
    	foreach($Owner in (Import-Csv $exportfile | group 'Assignee')) {
    	$newExport = "C:\Users\User\Documents\$($Owner.Name).xlsx"
    	$Owner.group | Export-Excel $newExport 
    	$files += $newExport
    }
    
    Write-Progress -Activity "Starting Email" -Status "We'll see"
    $files | Write-Host -BackgroundColor Black
    $files.Count
    
    foreach($Assignee in (Import-csv $exportfile | group 'Assignee')) {
    	$Assignee.Group  |Sort-Object Process -Descending | Format-List | Out-Host
    	$OL = New-Object -ComObject outlook.application
    	$mItem = $OL.createitem(0)
    	$mItem.Sender = "bart.simpson@students.fscj.edu"
    	$recip = $mItem.Recipients.Add($Assignee.Name)
    	$recievers = $recip.Name
    	
    	foreach($file in $files){
    	if ($file -like '*bart.simpson@students.elmentary.edu*' -and $receivers  -like  '*bart.simpson@students.elmentary.edu*' ) {
    	$mItem.Attachments.Add($file)
    	}elseif($file -like '*homer_simpson@yahoo.com*'  -and $recievers -like  '*homer_simpson@yahoo.com*' ) {
    	$mItem.Attachments.Add($file)
    	}else {
    	Write-Host ("DOPE") -ErrorAction Stop
    	}
    }
    	$mItem.Subject = "TEST"
    	$a = "<style>"
    	$a = $a + "BODY{background-color:peachpuff;}" 
    	$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
    	$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
    	$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:palegoldenrod}" 
    	$a = $a + "</style>"
    	$data= $Assignee.Group  |Sort-Object Process -Descending | ConvertTo-Html -Head $a -Body "<H2>Test</H2>"
    	$body = "Test"
    	$mItem.HTMLBody = $body
    	$mItem.Importance = 2
    	$mItem.Send() 
    	Write-Host("Sent to " +  $($Assignee.Name)).ToUpper() -BackgroundColor Black -ForegroundColor Green
    	[PSCustomObject] @{
    	"Recipients" = "Sent to " +  $($Assignee.Name)
    	"Time" = $time 
    	} | Export-csv C:\Users\User\Documents\Report.csv -NoTypeInformation -Append
    }


    Monday, July 27, 2020 1:28 AM
  • I think you are still missing the issues.

    I recommend that you take a bit of time to learn basic PowerShell instead of copying code and guessing.

    Your code is about twice as long as necessary and uses a kluge to avoid the real mistakes.


    \_(ツ)_/


    • Edited by jrv Monday, July 27, 2020 2:52 AM
    Monday, July 27, 2020 2:51 AM
  • I have to amend the above.  It is at least 5 times as much code as needed.  I tried to rewrite it from what you posted but it is so logically bad that thatseemed impossible.  Many of the lines of code are either unused or unnecessary.

    In my 40+ years experience I see that this is common for users who try to copy and edit code without understanding the code, the language or how programming is intended to be used.

    If you can state in a logical way what you are trying to do then we might be able to help you.

    If you are completely happy with what you think works then you are on your own.


    \_(ツ)_/

    Monday, July 27, 2020 3:14 AM
  • First of all, we will show you how to insert attachments in the body of a composing e-mail message directly.

    Step 1: Create a new email message:

    In Outlook 2010 / 2013, please click the New E-mail button in the New group on the Home tab.

    Freya009

    Please refrain from giving bad answers that have nothing to do with the question.  Until you learn enough about computers and PowerShell you should just follow the forums and not comment.  When you actually learn enough you will see whay I am posting this.


    \_(ツ)_/

    Tuesday, July 28, 2020 5:49 PM