question

siera_gld avatar image
siera_gld asked

SSIS Send HTML Email With Attachments - Help!

I need to create a vb script in order to Send an Email with Attachments. I am having no luck creating a simple script to email me in test with no attachments. In My Package i Have Variables Assigned for the following: MailTo = myname@mydomain.com, MailFrom =myname@mydomain.com, SmtpServer = MyServer MailMessage = MailSubject = In My Script Task I have ReadOnlyVariables for MailTo, MailFrom, SmtpServer ReadWriteVariable = MailSubject (I will try the message after I get it to just email successfully Here is my script Imports System Imports System.Net Imports System.Data Imports System.Net.Mail Imports System.Net.NetworkCredential Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() Dim str_To As String Dim str_From As String Dim str_Message As String Dim str_Subject As String Try 'Create Mail Message and Subject Dts.Variables("User::MailSubject").Value = "Status for Order: " Dts.Variables("User::MailMessage").Value = "The order number: placed on is ready for shipment. Please generate invoice for amount: " 'Read mail attributes from package variable into local variable str_To = Dts.Variables("MailTo").Value.ToString str_From = Dts.Variables("MailFrom").Value.ToString str_Message = Dts.Variables("MailMessage").Value.ToString str_Subject = Dts.Variables("MailSubject").Value.ToString 'Send Mail MailSend(str_To, str_From, str_Subject, str_Message) Catch ex As Exception End Try Dts.TaskResult = Dts.Results.Success End Sub Public Sub MailSend(ByVal ToMail As String, ByVal From As String, ByVal Subject As String, ByVal Message As String) Dim smtpclient As New SmtpClient Dim mailmessage As New MailMessage Dim fromadd As New MailAddress(From) 'SMTP server will be picked from cinfig file smtpclient.Host = Dts.Variables("SmtpServer").Value.ToString smtpclient.Port = 25 smtpclient.Credentials = CredentialCache.DefaultNetworkCredentials mailmessage.From = fromadd mailmessage.To.Add(ToMail) mailmessage.Subject = CStr(Subject) mailmessage.Body = CStr(Message) smtpclient.Send(mailmessage) End Sub End Class Can Someone please help? This shows green as successful but no error messages ever appear and no messages appear.
ssisscripttaskvbscript
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

siera_gld avatar image siera_gld commented ·
I'm sure your suggestions will come in handy - The first hurdle I want to overcome is sending a mail successfully - at this moment I can't
0 Likes 0 ·
Tim avatar image
Tim answered
Do you have to do this with VB? Can you not use Database Mail? What version of SQL Server are you running?
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
A quick search shows a lot of people who have been able to send the body of database mail with HTML. [Try this link]( http://www.techrepublic.com/article/sending-e-mail-with-database-mail-in-sql-server-2005/6164310)
1 Like 1 ·
Tim avatar image Tim commented ·
[MSDN]( http://msdn.microsoft.com/en-us/library/ms190307.aspx) lists the syntax for sending mail using db mail. Note the @body_format [ @body_format= ] 'body_format' Is the format of the message body. The parameter is of type varchar(20), with a default of NULL. When specified, the headers of the outgoing message are set to indicate that the message body has the specified format. The parameter may contain one of the following values: * TEXT * HTML
1 Like 1 ·
siera_gld avatar image siera_gld commented ·
SQL 2005 - The Send Mail Task is successful and would work perfectly but will not support html - I need to include a corporate logo
0 Likes 0 ·
siera_gld avatar image siera_gld commented ·
TRAD - I think this is very close - the only trick for me is to pass in paramaters in - so this is going inside a foreach loop and every time it loops - i pass in a paramater for @file_attachments= ?; @recipients = ? and the subject and body have references to more package variables - so in the body of the email I use a contact name for the person and it varies on every numeration? I have tried sending myself an email from management studio and it appears like this might be successful but cant get it to work from within my foreach loop
0 Likes 0 ·
Oleg avatar image
Oleg answered
The code has some small problems. Here is what you can try to make it work: Add this line to the code to make sure that the message is sent in Html format mailmessage.IsBodyHtml = true Optionally set the priority: mailmessage.Priority = MailPriority.Normal If you need to add any attachments to it then use this: 'file path should be visible to the SQL Server mailmessage.Attachments.Add(new Attachment(filePath)) And finally, please consider wrapping your message into the using block to prevent memory leaks. MailMessage type implements IDisposable interface, and so any instance of it should be disposed promptly after use. Oleg
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
There was a good example of this on MSSQLTips.com - [ http://www.mssqltips.com/tip.asp?tip=1753][1] You might try scarfing their example code to get you up and running quickly. You get a using statement for free by using their code (this addresses the memory leak issue raised by Oleg). [1]: http://www.mssqltips.com/tip.asp?tip=1753
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.