SQL Database Mail – Send Emails with Attachment


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-SQL
USE msdb
EXEC sp_send_dbmail
  @profile_name=’MailProfile1′,
  @recipients=’someone@microsoft.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-SQL
USE msdb
EXEC sp_send_dbmail
  @profile_name=’MailProfile1′,
  @recipients=’someone@microsoft.com’,
  @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 at
http://www.microsoft.com/info/cpyright.htm

Comments (17)

  1. Alex says:

    And if I need file name in attachment to be formed dynamically, for example based on date now?

    How I can do this?

    Thanks

  2. manish says:

    Thx…………for help

    🙂

  3. Ram says:

    Thanks for sharing!!!

  4. Neal says:

    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?

  5. Golam sarwar says:

    thnx

  6. Bolinha723 says:

    thnx

  7. kusuma says:

    When Iam trying to send eamil with attachment, displaying error as attachment file is invalid. Please help me out.

  8. kusuma says:

    Even though the file is valid results the above error

  9. Niraj Sevalkar says:

    Needful article …… 🙂

  10. Curious_DBA says:

    Thanks. Great article 🙂

  11. Cathy says:

    Thanks very much, this is the exact information I was looking for

  12. Manikandan says:

    If I need to send a CSV file with more than one sheet, is that possible in sp_SEND_DBMAIL

  13. Manikandan says:

    If I need to send a CSV file with more than one sheet, is that possible in sp_SEND_DBMAIL

    Thanks in advance,

  14. Manikandan says:

    Dear Yuhong Li,

     I need to send the CSV file with different sheets as attachment using msdb.Sp_send_dbmail, it will have different query set, Kindly help me in this regarding,

    Thanks in Advance,

    R. Manikandan

  15. thabang says:

    is it possible to add the information of an attachment on to the email body?