x

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 = <Blank>
 MailSubject = <Blank>

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.

more ▼

asked Feb 09, 2011 at 08:56 AM in Default

avatar image

siera_gld
1k 82 88 93

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

Feb 09, 2011 at 10:49 AM siera_gld
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Feb 09, 2011 at 10:24 AM

avatar image

Oleg
17.2k 3 7 28

(comments are locked)
10|1200 characters needed characters left

Do you have to do this with VB? Can you not use Database Mail? What version of SQL Server are you running?

more ▼

answered Feb 09, 2011 at 09:40 AM

avatar image

Tim
40.4k 39 84 166

SQL 2005 -

The Send Mail Task is successful and would work perfectly but will not support html - I need to include a corporate logo

Feb 09, 2011 at 10:00 AM siera_gld

A quick search shows a lot of people who have been able to send the body of database mail with HTML. Try this link

Feb 09, 2011 at 11:12 AM Tim

MSDN 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
Feb 09, 2011 at 11:18 AM Tim

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

Feb 09, 2011 at 12:47 PM siera_gld
(comments are locked)
10|1200 characters needed characters left

There was a good example of this on MSSQLTips.com - http://www.mssqltips.com/tip.asp?tip=1753

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).

more ▼

answered Feb 09, 2011 at 11:00 AM

avatar image

KenJ
24.8k 3 10 19

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1154
x87
x11
x10

asked: Feb 09, 2011 at 08:56 AM

Seen: 7780 times

Last Updated: Feb 09, 2011 at 09:06 AM

Copyright 2016 Redgate Software. Privacy Policy