Database Mail allows you to attach files in emails. Several examples are provided to show how you can do it.
The article here is assumed that a SQL Database Mail profile "MailProfile1' has been created. The MailProfile1 tells Database Mail what account it should use to send emails. For how to create a Database Mail profile, you can refer to the previous post SQL Database Mail.
Example 1 - Attach a single file in Database Mail
-- Start T-SQLUSE msdbEXEC sp_send_dbmail @profile_name='MailProfile1', @email@example.com', @subject='Test Attachment', @body='An attachement has been included in this email.', @file_attachments='D:\temp\blog.jpg'-- End T-SQL
This script will attach the file from "D:\temp\bloc.jpg" and send out the email. After sp_send_dbmail is run, the person at @recipients may receive an email like the following one.
Example 2 - Attach multiple files in Database Mail
-- Start T-SQLUSE msdbEXEC sp_send_dbmail @profile_name='MailProfile1', @firstname.lastname@example.org', @subject='Test Attachment with Two Files', @body='Two files have been included in this email.', @file_attachments='D:\temp\blog.jpg;D:\temp\long folder\sample.txt'-- End T-SQL
This script will attach two files from "D:\temp\bloc.jpg" and "D:\temp\long folder\sample.txt". After sp_send_dbmail is run, the email may look like below.
The multiple files must be separated by semicolon (;). It is fine to have spaces in the file path as long as the semicolon can delimit each path.
The size of attachment is limited to 1 MB per file. You can change it in Database Mail configuration as shown below.
The Maximum File Size (Bytes) is the size limitation of attachment. You probably have noticed another item "Prohibited Attachment File Extensions" that is just below the line of Maximum File Size (Bytes). As the name suggests, files with the prohibited extension such as ".exe" can not be sent by DatabaseMail. If you want to send it anyway, you can rename the extension to something else, e.g., rename "foo.exe" to "foo.txt", then send it out.
For more information, see the sp_send_dbmail in MSDN document.
<End of Post>
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
Useful article :)
And if I need file name in attachment to be formed dynamically, for example based on date now?
How I can do this?
Thanks for sharing!!!
It is very helpful. I have 2 doubts here:
1. What I need to add many files, say 10+?
2. How do I fetch a file in some folder and then add this file as a attachment and a email?