Send Lotus Mail from Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Send Lotus Mail from Script

Author

Topic

loudbliss
Starting Member

37 Posts

Posted - 2008-04-09 : 16:13:45

Hi Guys,

Im want to be able to send an email from a script via Lotus Notes.

Im trying this code which i found at [url]http://blogs.conchango.com/jamiethomson/archive/2006/07/03/SSIS_3A00_-Sending-SMTP-mail-from-the-Script-Task.aspx[/url]


Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net


Public Class ScriptMain
  Public Sub Main()
    Dim myHtmlMessage As MailMessage
    Dim mySmtpClient As SmtpClient

    myHtmlMessage=New MailMessage("me@mail.com", "me@mail.com")
    mySmtpClient = New SmtpClient("192.168.3.1")
    mySmtpClient.Credentials = New System.Net.NetworkCredential   ("<username>", "<password>")
    mySmtpClient.Send(myHtmlMessage)
    Dts.TaskResult = Dts.Results.Success
   End Sub
End Class

First the task stayed Yellow and didnt changed, not it gives me the following error:
DTS Script Task has encounter an exception in user code.
Transaction failed. The server response was: Relay rejected for policy reasons.

Could someone help me there or suggest another way for sending Lotus Notes email from my SSIS Package?

Thanx

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-04-09 : 16:40:39

I have that, but that uses ActiveX Script, which is what I used in DTS back in the days.
The script you got from Jamie's web site is using SMTP, not your Lotus Notes profile, it's just sending the message from a script instead of a Send Mail Task component instead.

Can you post how you have your code?
Are you setting IP, SSL and Credentials Correctly?
Are there any reasons as to why you wouldn't want to use the Send Mail Task Component?

loudbliss
Starting Member

37 Posts

Posted - 2008-04-09 : 16:59:10

igor here's my code:

Public Sub Main()
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
Dim FromAdress As MailAddress
Dim ToAdress As MailAddress

FromAdress = New MailAddress("myadress@server.domain.com")
ToAdress = New MailAddress("myadress@server.domain.com")

myHtmlMessage = New MailMessage(FromAdress, ToAdress)
myHtmlMessage.Body = "Test Body"
myHtmlMessage.Subject = "Title"
mySmtpClient = New SmtpClient("172.31.3.145")
mySmtpClient.Credentials = New System.Net.NetworkCredential("myUsername", "myPassword")
mySmtpClient.Send(myHtmlMessage)
Dts.TaskResult = Dts.Results.Success

End Sub

My Ip and my Credentials are Correct.

I didnt used the Send Mail Task because i could find where to put the user and password. Maybe i skipped some step.
Is it possible to used the Send Mail task for Lotus Notes?

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-04-09 : 17:02:32

One thing to keep in mind though is that this approach will expose the account's password, which is a bad practice and for some companies it could even mean a compliance issue.

loudbliss
Starting Member

37 Posts

Posted - 2008-04-09 : 17:11:18

im aware of that problem, but i really dont know another method for doing this.

Is it possible to send Lotus Mail from Send Mail Task if my account is not Windows Auth.?

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-04-09 : 17:15:48

Send Mail task will only consume an SMTP Connection.

When I was on a Lotus Notes environment, I used the SMTP server, to me there was no benefit on using Lotus Notes, plus it was really painful to get the SQL Server to talk with Lotus Notes.

It must be either the server that you're using, the domain name that you're not passing on your credentials (domain/user) or SSL encryption, since I was able to run the same exact code you posted with just changing a few things here and there.

For more info on SmtpClient:
http://msdn2.microsoft.com/en-us/library/system.net.mail.smtpclient.aspx

loudbliss
Starting Member

37 Posts

Posted - 2008-04-09 : 17:19:08

I also find no benefit on using Lotus Notes but the company uses it so i have to work around it.

Im going to see if theres any restriction blockin me from sending the email since you verified the code and is ok

Thnx very much for your help Igor.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-04-09 : 17:28:57

are you sure you're pointing that to an SMTP server and not a Lotus Notes server? Your organization must have an SMTP server, make sure you're pointing to the SMTP server and not the Lotus Notes one and post any error messages if you have any.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-04-10 : 11:56:25

I've asked one of my old co-workers for this code. I think this will be sent out using your LN credentials.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Dim session
Dim db
Dim mdate
Dim Myweekday

Set session = CreateObject("Lotus.NotesSession")
session.Initialize ("")

Set db = session.GetDatabase("", "")
Set notesdoc = db.CreateDocument

Call notesdoc.ReplaceItemValue("Principal", "")
Call notesdoc.ReplaceItemValue("Sendto", "")
'Call notesdoc.ReplaceItemValue("Sendto", "")
Call notesdoc.ReplaceItemValue("Copyto", "")
Call notesdoc.ReplaceItemValue("Subject", "Report " & date & " " &Time)
Set notesrtf = notesdoc.CreateRichTextItem("body")
Set Style = session.CreateRichTextStyle
'Style.Bold = 1
Call notesrtf.AppendStyle(Style)
'Call notesrtf.AppendText("This is a test for email automation.")
Call notesrtf.AddNewLine(2)
Call notesrtf.AppendText("SYSP")
Call notesrtf.AddNewLine(2)

Call notesrtf.EmbedObject(1454, "", "E:\Test\Reports\Test0.TXT")
Call notesrtf.EmbedObject(1454, "", "E:\Test\Reports\Test1.TXT")

Call notesrtf.AppendText("SYSS")
Call notesrtf.AddNewLine(2)

Call notesrtf.EmbedObject(1454, "", "E:\Test\Reports\Test2.TXT")
Call notesrtf.EmbedObject(1454, "", "E:\Test\Reports\Test3.TXT")
Call notesrtf.AddNewLine(1)

notesdoc.SaveMessageOnSend = True
Call notesdoc.Send(False)
'Call notesdoc.Send(True, recipients)

Set db = Nothing
Set session = Nothing

Main = DTSTaskExecResult_Success
End Function

loudbliss
Starting Member

37 Posts

Posted - 2008-04-11 : 17:12:44

igor, im going to try the code now, i asked the person in charge of lotus is there is any reason why i would not be able to send emails and the guy had no clue, so not much help there.

Im going to view the code now, i'll try to post an answer today but is almost closing time here.

loudbliss
Starting Member

37 Posts

Posted - 2008-04-14 : 09:25:58

igor, theres a problem sending email from my server, it seems this is not a ssis related problem. I still havent found someone to help me with that since noone here knows much about lotus, not even the person in charge. i'll replay on msg you when i finally solve the issue.

Thanx a lot for your help

imranrizvi
Starting Member

1 Post

Posted - 2011-05-16 : 08:21:42

I think your smtp server is not relying on your web server to send email through it, following are the steps you can allow smtp server to relay.

1) Start->Programs->AdministrativeTools->IIS6
2) Start SMTP Service (right click on SMTP Virtual Server #1) and start
3) Properties->Access->Relay
4) Add IP Address(live) including 127.0.0.1
5) got Services , start SMTP services, and Change it start option from Manual to Automatic.

Thanks & Regards,

Mohammad Imran Rizvi
Senior Software Engineer

Zeon Solutions Pvt. Ltd.
9325882272/9303993202(m)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources