SQL Server Email Query

PowerShell Help
1

by sgarciacode at 2013-02-27 10:38:30

Hello,

I designed the functio below to open an excel file, extract emails from a specific tab and then send them all a specified email that is extracted from a text file. This works fine, the only thing is that now I have to access the email from an SQL Server Database. I am not sure if this is possible or how I would go about doing this. If anyone has some knowledge as to how to accomplish this that would be great.

[code2=powershell]function send-email( [string] $contactGroup, [string] $messageLocation ){

#This will create and excel object and open up the file to Sheet1 of the file
$Excel = New-Object -ComObject "Excel.Application"
$Excel.Visible = $true
$Workbook = $Excel.workbooks.open("FileLocation")
$Sheet = $Workbook.Worksheets.Item($contactGroup)

#Set this value to the first row you want to extract data from
$intRow = 3

#Create arrays of object to hold the excel information
$Emails = @()
$FirstNames = @()
$LastName = @()

#This will iterate throught the file and place infromation into arrays
Do {
$FirstNames += $Sheet.Cells.Item($intRow,1).Text #(row, col)
$LastName += $Sheet.Cells.Item($intRow,2).Text #(row, col)
$Emails += $Sheet.Cells.Item($intRow,3).Text #(row, col)

$intRow++
}
While ($Sheet.Cells.Item($intRow,1).Value() -ne $null)

#Quit Excel
$Excel.quit()

$emailString = [string]$Emails

$message = Get-content $messageLocation
$ofs = ' '
$messageBody = [string]$message

#Now that we have all the information we will create the email and send it
$PSEmailServer = "#######"
Send-MailMessage -From "no-reply@test.org" -To $emailString -Subject "Test email" -Body $messageBody.Replace(" ", "nn")[/code2]

Thanks,
by poshoholic at 2013-02-27 11:58:08
Just to clarify, so you have a script that automatically emails a list of addresses. Once that is done, from inside of a SQL script you want to access that email? If you send to multiple recipients, which one are you accessing? If all you need are the email contents, have you thought about pushing them into a location/format that is available to access from SQL Server? More details might help here.
by sgarciacode at 2013-02-27 15:38:24
Hi,

Actually what I want to do, is have an similar function that sends out emails, except the data source from which I extract the emails from will now be an SQL server database instead of an excel file.

So this is a list of step that I imagine the function would take:

Open SQL Database

Extract emails from database using a query

Place extracted emails into a string format

Place emails within the send-message cmdlet and send out email

Thanks,