how to send email to lotus notes with query result

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 2000 Forums

SQL Server Development (2000)

how to send email to lotus notes with query result

Author

Topic

nancy_lee
Starting Member

18 Posts

Posted - 2002-12-03 : 16:48:26

The objective is to be able to send a query resultset to the users via e-mail(Lotus Notes mail server... that's the tricky part). The sql statements is called in VB 6.0.

What are the setups to send e-mail?
I think I can run xp_sendmail but that's all I know. Also, each row of the resultset is going to a different e-mail address.

My issue is the e-mail portion (not the query in VB).

Thanks for the help in advance!

Edited by - nancy_lee on 12/03/2002 16:49:01

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-03 : 17:21:08

This should get you started ..... I'm not sure what version of notes we have here.

Public Function EmailExample()
Dim session As Object
Dim db As Object
Dim doc As Object
Dim rtf As Object

Set session = CreateObject("Notes.NotesSession")
Set db = session.GETDATABASE("", "")
db.OPENMAIL
Set doc = db.CREATEDOCUMENT
With doc
.REPLACEITEMVALUE "SendTo", "Person@Addr.com"
.REPLACEITEMVALUE "From", "You@Addr.com"
.REPLACEITEMVALUE "Subject", "Email Subject"
Set rtf = .CREATERICHTEXTITEM("Body")
rtf.APPENDTEXT "Email text goes here"
.SAVEMESSAGEONSEND = True
.Save True, False, True
.send False
End With
Set rtf = Nothing
Set doc = Nothing
Set db = Nothing
Set session = Nothing

End Function

You don't need to create a reference or anything for the above to work. I know earlier versions of notes didn't work with early binding, so you had to declare everything as an object (as I did, above). I think newer version fix this, though.

What you might want to do is create your session object once, and create a a function that accepts a session, dest address, subject and text. Then, in your code, create the session, open your recordset, and move through the records using the function to send each email.

Let me know if you need more details on how to do this or if the above code doesn't work. Test it out by sending an email to yourself.

By the way, I HATE Lotus Notes.

- Jeff

Edited by - jsmith8858 on 12/03/2002 17:24:41

nancy_lee
Starting Member

18 Posts

Posted - 2002-12-03 : 21:59:06

Jeff,
Thanks so much for your reply.
I may not have made myself clear...

Currently this is how my app. works :
1. the vb app. will create a resultset by querying the sql db
2. it will then create a crystal reports with all the info for the different users (formatted)

... my job is to find out how to send the report to the users via notes e-mail.

My notes version is 5.0 , my user is still on 4.6 ... does that make a difference when I code ?

My skill set is more on the sql side, so I may need more help with the vb and notes setup.

Thanks again!

Edited by - nancy_lee on 12/03/2002 22:03:42

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-03 : 22:06:23

How do you want to send it to the users? As an attachment? Or formatted within the email as a rich text document? Which version of Crystal are you using?

Also, what is the code you currently have (summarized) ?

- Jeff

nancy_lee
Starting Member

18 Posts

Posted - 2002-12-04 : 10:18:26

Jeff,

I would like to send it as an attachment...
Crystal version is 7.0

My VB application will read data from sql server(6.5), do all the calculation and then call crystal report for creating the report ... the screeen will switch from the app. to crystal report at the end of the processing... when I close the crystal report screen, my app will appear again.

I will then need to save this report and email to users.
The users want this to be automated through the app. rather than for me to send them email manually.

Hope I have made myself clear and thanks again.

nancy_lee
Starting Member

18 Posts

Posted - 2002-12-09 : 15:36:06

can someone please give me some hint with this questions ?
thanks!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-09 : 16:01:31

Are you running win2k.
If so then the recepient might use notes to read the email but you
can send it using cdonts.

Here is an example

exec sp_SMTPMail @SenderName='Joe', @SenderAddress='joe@xyz.com',
@RecipientName = 'Joe2', @RecipientAddress = 'joe2@xyz.com',
@Subject='SQL Mail Test',
@Body='This is a test message from SQL Server. Smile! It worked.',
@Path = 'c:\pathtofilegoeshere',
@FileName = 'myfile.txt'

Create Procedure sp_SMTPMail
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@Path varchar(128),
@FileName varchar(128)
AS
SET nocount on

declare @oMail int --Object reference
declare @resultcode int

EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT

if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body

EXEC @resultcode = sp_OAMethod @oMail, 'AttachFile' , @path, @filename

EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL

EXEC sp_OADestroy @oMail
END

SET nocount off
GO

Edited by - ValterBorges on 12/09/2002 16:12:53

nancy_lee
Starting Member

18 Posts

Posted - 2002-12-11 : 10:55:54

Thanks ValterBorges for your suggestion.

I first try to run the sp you gave me and put in the parameters just to test it on sql server 6.5, but I don't see the email that was sent to myself. Is there a way 1. to check for errors 2. there must be something that I haven't done or don't know about.

I have never done this before so your patience is most appreciated.

Thanks again!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-11 : 11:36:30

Do you have cdonts on the server.
cdonts gets installed if you have Exchange Server, IIS, Outlook

http://support.microsoft.com/default.aspx?scid=KB;EN-US;171440

sp_OACreate will return a nonzero number in case of error.

you can take the code put it in the query analyzer and put print statements to check resultcode after every exec.

Edited by - ValterBorges on 12/11/2002 11:38:02

Edited by - ValterBorges on 12/11/2002 11:42:58

nancy_lee
Starting Member

18 Posts

Posted - 2002-12-11 : 13:40:43

I copy the cdonts.dll to winnt/system32 and run your query again but it still doesn't work... do I need to install IIS on this PC (windows nt) ?

I have tried it on windows 2000 with iis disabled and it's still working.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-11 : 15:58:59

Did you register the dll using regsrv32.exe?
<drive>:\\winnt\system32\regsvr32 cdonts.dll

Where did you obtain cdonts.dll? NT OPTION pack 1?

FYI here is the road map for cdo.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncdsys/html/cdo_roadmap.asp

Edited by - ValterBorges on 12/11/2002 16:08:49

nancy_lee
Starting Member

18 Posts

Posted - 2002-12-11 : 16:40:23

I picked up the dll from windows 2000 and copy to windows nt 4.0 workstation, will that work ?

nancy_lee
Starting Member

18 Posts

Posted - 2002-12-11 : 17:15:40

I just register the dll on my windows nt workstation and test it again and it still doesn't work.

Please help!

nancy_lee
Starting Member

18 Posts

Posted - 2002-12-12 : 09:55:27

I have done some reading that I should install option pack 4 on my machine... however when I tried to execute the install.exe it's telling me that I have SP6 (with revised sp6a) on my machine, option pack 4 has not been tested with this version of SP6, so I don't know if I should run it since I know SP6a has a fix for lotus notes and I don't know if it will do any damage.

Please help!
Thanks

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-12 : 10:16:01

Just install IIS and make sure the service is disabled.

nancy_lee
Starting Member

18 Posts

Posted - 2003-01-07 : 15:38:11

I have done what all of the above but I was still unable to send an email... is it possible that all of my changes are done on a client and these all have to be done on the server ?
Please help
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources