Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Email attachment script ? Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 1:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 6:57 AM
Points: 204, Visits: 704
I have a sql table that contains 100 email addresses & a word document that needs to be sent to these recipients. Does anyone have a script to read through a table & send an email with a document attachment I can use ?

thx!



Post #1394774
Posted Monday, December 10, 2012 1:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 14,469, Visits: 38,082
so you want to send 100 individualized emails, each with the same (or different?) attachment?(hint: cursor)
or do you want to send a single email, with 100 To:/CC:/BCC:/ and the same attachment?

and finally, does the attachment exist on the SQL server?


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!
Post #1394775
Posted Monday, December 10, 2012 1:56 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 6:57 AM
Points: 204, Visits: 704
I want to send 100 different emails (Notifying customers of a change) & yes, the word document will be on the Sql server.

thx!



Post #1394776
Posted Monday, December 10, 2012 2:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 14,469, Visits: 38,082
a rough example of multiple recipients from a query, unique emails, same attachment:
declare
@isql varchar(2000),
@email varchar(64)

declare c1 cursor for select distinct email FROM DatabaseName.dbo.SomeTable WHERE CampaignID = 42 and dbo.IsValidEmail(email) = 1
open c1
fetch next from c1 into @email
While @@fetch_status <> -1
begin
declare @body1 varchar(4000)
set @body1 = 'Latest documentation For You. ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +
' '


EXEC msdb.dbo.sp_send_dbmail @recipients=@email,
@subject = 'Latest documentation ',
@body = @body1,
@body_format = 'HTML',
@file_attachments = 'c:\ServerAttachments\ERP.pdf'

fetch next from c1 into @email
end
close c1
deallocate c1




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!
Post #1394778
Posted Monday, December 10, 2012 2:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 6:57 AM
Points: 204, Visits: 704
Now I feel really dumb, but what is "CampaignID" ?


Post #1394792
Posted Monday, December 10, 2012 3:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 14,469, Visits: 38,082
it's just example WHERE statement for the data; your query which gathers the "right" emails would be very specific to your business logic, and especially the WHERE statement which decides whether someone gets an email or not;

i just wanted to show, as an example, you don't send to every email in a table, there should be logic.
same thing for distinct email, if someone is in the table more than once, you don't want to look unprofessional by sending them multiple emails.

finally, you can see i was using a udf to make sure an email was valid...no sense trying to send to an empty string, or an email that doesn't have the @ symbol in it.



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!
Post #1394797
Posted Monday, December 10, 2012 3:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 6:57 AM
Points: 204, Visits: 704
Great, thank you much!!!!


Post #1394798
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse