SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


email with attachment powershell


email with attachment powershell

Author
Message
mtz676
mtz676
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4500 Visits: 1411
This code runs successfully but does not send an email.Any help is appreciated.

Add-PSSnapin Microsoft.Exchange.Management.Powershell.Admin -erroraction silentlyContinue
function sendmail_withAttachment {
Param (

#[Parameter(Mandatory=$true)]
[string] $smtpServer = "smtp.xyz.org",

[string] $From_mail = "abc.org",
[string] $to_mail = "abc.org",
[string] $cc_mail = "abc.org",
[string] $Subject = "ABCDEF"
)
Write-Host "Sending Email"
$text= ''
$body = ''
$text = "Attached"
$filename = "20172018"
$file= "C:\NewFolder"+$filename
$att = new-object Net.Mail.Attachment($file)
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
#$smtp.Send($smtpFrom,$smtpTo,$messagesubject,$messagebody)
$msg.IsBodyHTML = $true
$msg.Body = $body
$msg.From = $From_mail;
$msg.To.Add($to_mail)
$msg.CC.Add($cc_mail)
$msg.Subject = $Subject;
$logs = Get-Content $file | Out-String
$msg.Attachments.Add($att)
$smtp.Send($msg)
$att.Dispose()
}


Thanks
bruce 1565
bruce 1565
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1742 Visits: 1090
The first thing I noticed was
$filename = "20172018"
$file= "C:\NewFolder"+$filename

That probably doesn't give you what you expect for the full file path/name. If you leave off the attachment can you successfully send an email?

ZZartin
ZZartin
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13987 Visits: 14590
I also noticed you have this up there -erroraction silentlyContinue , is that by any chance also set for the entire script or is it in a try catch? All of those commands should throw errors if there's a problem and as bruce said that file name doesn't look right but would throw an error if it's not being suppressed.

Also why not just use the Send-MailMessage cmdlet?
Lowell
Lowell
SSC Guru
SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)

Group: General Forum Members
Points: 196529 Visits: 41573
definitely, use something that will create a proper filename, and handle ending slashes

the below returns "C:\NewFolder\20172018"
$filename = "20172018"
$file= "C:\NewFolder"+$filename
you mosdt likely need the file and an extention as well right?

[string] $DestinationFileName = [System.IO.Path]::Combine($outputdir,$outputfile)

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
mtz676
mtz676
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4500 Visits: 1411
This piece of code works perfectly well when I run in powershell-sends an email along with the attachment.But when I schedule it in a task scheduler it does send an email.

send-mailmessage -from "abcd@efgh.org" -to "abcd@efgh.org", "abcd@efgh.org" -bcc abcd@efgh.org -subject "Updates $(Get-Date -format 'g')" -body "Hello YourName, `n Attached are the Updates `n Thanks `n MyName `n `n `n `n This email is scheduled to run every Sunday @12:00AM " -Attachment "C:\Users\myname\Desktop\WUpdates\WUpdates.xlsx" -smtpServer smtp.qwerty.org

Task Scheduler properties :

General - abc
Author - c\myname
Change user/group - abc\sqlenter
Run whether user is logged in or not
Run with hishest privileges

Trigger:Weekly once at 6:00AM

Actions

Program: powershell
Add arguments: -ExecutionPolicy Bypass -file "C:\Users\myName\Desktop\WUpdates\AWUES.ps1"

AWUES.ps1 has the path for the attachment excel file.

Any help is appreciated.
Attachments
Taskscreenshot.JPG (3 views, 86.00 KB)
ZZartin
ZZartin
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13987 Visits: 14590
Drill into the action completed step in the history, you'll like see some kind of error code in there. One way to test it is to RDC into the server with the account you're planning to use to run it, open a powershell console and run the command from there. Task scheduler has not the greatest logging or error trapping so that might give you some idea of what's wrong.
Lowell
Lowell
SSC Guru
SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)

Group: General Forum Members
Points: 196529 Visits: 41573
mtz676 - Tuesday, January 2, 2018 11:50 PM
This piece of code works perfectly well when I run in powershell-sends an email along with the attachment.But when I schedule it in a task scheduler it does send an email.

send-mailmessage -from "abcd@efgh.org" -to "abcd@efgh.org", "abcd@efgh.org" -bcc abcd@efgh.org -subject "Updates $(Get-Date -format 'g')" -body "Hello YourName, `n Attached are the Updates `n Thanks `n MyName `n `n `n `n This email is scheduled to run every Sunday @12:00AM " -Attachment "C:\Users\myname\Desktop\WUpdates\WUpdates.xlsx" -smtpServer smtp.qwerty.org

Task Scheduler properties :

General - abc
Author - c\myname
Change user/group - abc\sqlenter
Run whether user is logged in or not
Run with hishest privileges

Trigger:Weekly once at 6:00AM

Actions

Program: powershell
Add arguments: -ExecutionPolicy Bypass -file "C:\Users\myName\Desktop\WUpdates\AWUES.ps1"

AWUES.ps1 has the path for the attachment excel file.

Any help is appreciated.


Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Merkwürdigliebe
Merkwürdigliebe
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 19
I follow a format similar to the first code posted.
The only thing I see in the second posted code is no quotes on the server name.
-smtpServer smtp.qwerty.org
That could have been a copy and past thing but in my code this is my server reference.
$smtpserver = "mailserver.your_domain.com" #local relay server
the standard format I use when sending files. I can include the sql and build csv file if you want it but not 100% on topic.
{
$filename = "D:\Powershell\Results\closed_request.csv"
$smtpserver = "mailserver.your_domain.com" #your server
$msg = new-object Net.Mail.MailMessage
$att = new-object Net.Mail.Attachment($filename)
$smtp = new-object Net.Mail.SmtpClient($smtpServer )
$msg.From = "mail_admin <donotreply@your_domain.com>"
$msg.To.Add("Customer Service <customerserviceoffice@your_domain.com>")
$msg.Cc.Add("real name <some.emailaddress@your_domain.com>") # add a cc address like yourself so you can call BS when they say you didn't get it.
$msg.Subject = "Your request has been closed."
$msg.Body = "Hello, I closed your request. If you want a detailed explanation. Please see the attached file."
$msg.Body += "Thank you aaaaaand what not" # add a second body line
$msg.Attachments.Add($att)
$smtp.Send($msg)
Remove-Item D:\Powershell\Results\closed_request.csv
exit
}
I hope that helps.
Lowell
Lowell
SSC Guru
SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)

Group: General Forum Members
Points: 196529 Visits: 41573
my comments got lost somehow.
the issue the OP is encountering is the Service Account that the SQL Agent runs under does not have access to "C:\Users\myName\Desktop\WUpdates\"
that path is a private folder for the end user; it is not a path that the service account can get to.

Create a folder like C:\Data, or c:\Temp, make sure the service account has access to it,a nd use that folder as your target folder/subfolders for processes like this.

typically in a work environment, you create a unc file share, like \\MyServer\SQLMail\Attachments, and grant access to that folder.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search