Powershell Send-MailMessage from multiple recipients from sql table

darkgen · 4 Jul 2016 at 10:56

So I've set up a script which basically pulls data from a sql table and then based on certain conditions will then send out a mail notification. Historically I set this up to only send to a single recipient but now need top expand this via the "Send-MailMessage" cmdlet.

With this in mind I altered the sql table which stores the recipients as a single string in the following format (Write-Host confirms this):

Code:

"<[email protected]>","<[email protected]>","<[email protected]>"

The data does get pulled in correctly as a variabe

Code:

$mailrecipients = $item.mailrecipients

However when I pass this via the Send-Mailmessage cmdlet

Code:

Send-MailMessage -To $mailrecipients -From "[email protected]" -Priority Low -Subject "foosubject" -Body "foobody" -smtpServer 1.2.3.4

Send-MailMessage balks with

Code:

Send-MailMessage : An invalid character was found in the mail header: '"'....
Send-MailMessage : A recipient must be specified.

I must assume it does not like the format passed with $mailrecipients but various web checks suggest it is good.

Any help/pointers much appreciated.

TIA, Paul.

radderfire · 4 Jul 2016 at 13:41

I don't know exactly what systems you are using and how you are coding it, but the nature of your problem is going to be that you are allowing characters in to the variable that aren't liked by e-mail headers. So, for example, you are setting the variable to:

$mailrecipients ='"<[email protected]>","<[email protected]>","<[email protected]>"'

When it should be something like:

$mailrecipients = "[email protected], [email protected], [email protected]"

When you generate the list of e-mail recipients, it is likely to need to be a clean comma separated list like my example above.

Hope that helps.

darkgen · 4 Jul 2016 at 15:09

So I have removed the double-quotes between addresses

Code:

<[email protected]>,<[email protected]>,<[email protected]>

and the error is removed yet no mails are sent.

Code:

[string[]]$mailrecipients = @("<[email protected]>","<[email protected]>","<[email protected]>")
Send-MailMessage -To $mailrecipients -From "[email protected]" -Priority Low -Subject "foosubject" -Body "foobody" -smtpServer 1.2.3.4

EDIT - Works with a string array.

However this does not work inside my code and continues to throw out the invalid '"' character...

Something clearly awry with my code yet I can't see why its failing. I can see the recipients variable is populated and the command being executed.

EDIT
Clearly the way I'm handling multiple recipients is at fault. it does not get passed correctly to Send-MailMessage.

Tried various formats with no luck.

radderfire · 4 Jul 2016 at 16:44

I don't use powershell, but required format looks to be:

-To "User02 <[email protected]>", "User03 <[email protected]>"

See:

https://technet.microsoft.com/en-us/library/hh849925.aspx

So, you probably aren't constructing the To- field correctly with your code. What are you coding in?

darkgen · 4 Jul 2016 at 17:16

Powershell 3.0 (just using the standard ISE).
The friendly name is optional as confirmed in my previous post.

I suspect the issue is the variable isn't being passed or interpreted correctly from the sql query, even though the variable does appear to be populating correctly. The sql query pulls from the table previously but now it includes an additional column with the joined recipients list as a single string. (Comma delimited).

EDIT:

I think I've discovered the issue;

The sql data which populates the $mailrecipients is in the string format

Code:

"<[email protected]>","<[email protected]>","<[email protected]>"

This is a single string and not an array so is treated as a single mail address entry and promptly fails due to multiple '"' invalid characters.

SOLUTION

Code:

$Mailarray = $mailrecipients -split ','

radderfire · 5 Jul 2016 at 12:11

Glad you got it sorted. It tends to be the " that cause problems.

Rgds

Washout · 25 Oct 2016 at 10:20

Bit of a resurrection but we just had exactly this issue here at work. Some scripts were moved from a 2008 box to 2012. Clearly the testing was very thorough!

Seems the older version was happy taking a string of addresses but the newer required it to be an array. Did make me chuckle when I did a quick google for the panicking devs and found this thread!