Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 @ 11:50 AM
Points: 12,877, Visits: 31,792
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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 @ 11:50 AM
Points: 12,877, Visits: 31,792
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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 @ 11:50 AM
Points: 12,877, Visits: 31,792
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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