Problems understanding automated emailing code.

Hi,

Can anyone help me on the following code, it has been designed to send out automated emails using Excel and Lotus Notes, unfortunately the code was give to me to modify and the person who created it didn't comment the code at all so i'm finding it impossible to see what each part is doing:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/19/2006 by ai9997
'

server = "server_name"
mailfile = "mail\file_name.nsf"

Set session = CreateObject("Notes.NotesSession")

Set Db = session.GetDatabase(server, mailfile)

Set DOC = Db.CreateDocument
DOC.Form = "Memo"

Recipient = TN

Set body = DOC.CreateRichTextItem("Body")

DOC.Subject = "expedite"

Call body.appendixtext("Hi,")
body.addnewline 1
body.appendtext ("This is an automated email")

Call DOC.send(False, Recipients)

Set DOC = Nothing
Set Db = Nothing
Set session = Nothing
'
End Sub

I tried running the Marco to see what happened but it stops at "Set DOC = Db.CreateDocument"

If anyone can help explain what is happening in this code that would be great failing that if anyone could provide me with some code that would work in much the same way that would be great too.

My final Target is to be able to send automated email from an Access Database but i'm assuming the code would be very similar if not please let me know.

Kind Regards,

David.

Hi David,

The problem is here:

Please Login or Register  to view this content.

You need to replace the text on the right side of each assignment statement with what matches your system.

Here is link to a PDF file that explains using LotusScript in Visual Basic environment ...

http://www.redbooks.ibm.com/redbooks/pdfs/sg244856.pdf

I know, I just took the servername and file_name out for sercurity reasons when i've run the code it seems to stop at "Set DOC = Db.CreateDocument"

Thanks for the link, I will look at this to see if it helps. If anyone else can help out that would be great.

Kind Regards,

David.

Hi MSP77079,

Thank you for your help so far, it's helped loads, only thing is it's now having problems with line: "Call body.appendixtext("Hi,")"

The error is comes back with is, "Object doesn't support this property or method.

Do you know if i'm missing something? I've put this code into a macro in Excel i'm guessing this is right seeing as the data is coming from Excel, but do you know if I should also have something written in Lotus Notes?

Kind Regards,

David.

Last question first ... no need to write anything in Lotus. Although, after this project you might become interested in creating your own macros (they call them agents) in Lotus.

I got a big grin from this line of code:

Please Login or Register  to view this content.

Try this ...

Please Login or Register  to view this content.

Thank you once again it seems to like the code now, only now it gets to: "Call DOC.send(False, Recipient)", then says: "No Recipient list for Send Operation"

I'm pretty sure it's because it's not looking in the correct place for the list of email addresses, but i'm not sure where it should be looking. I have a list of email addresses and i've named the selection as TN but i'm not sure if this is correct or not. Either way it's still halting at this stage.

As you can see i'm not great at understanding the complexities of VB, I'm more of an SQL person and even then my knowledge isn't amazing.

Any help on this bit would be great.

Kind Regards,

David.

Hi David,

Well ... I have a couple of thoughts.

First is that at the top of the code you have

Please Login or Register  to view this content.

But, later on you have

Please Login or Register  to view this content.

The "s" makes all the difference in the world. Recipients is undefined.

Second, not sure where TN is defined. From your last posting, it seems that maybe TN is a Name you gave to a Range of cells in Excel. To refer to that Range in VBA, you would use something like

Please Login or Register  to view this content.

But, I am quite certain that Lotus will reject that as well because it is probably expecting a text string, not an Excel Range. To, to convert the range (containing text strings, I hope) to a single text string, you would need something like this:

Please Login or Register  to view this content.

When I use VBA to send LotusNotes stuff, I use internet format for e-mail addresses.

Also, I use separate commands to append the recipients from the command to send the e-mail. (But, if above solves your problem, do not bother changing it.)

To add recipient list:

Please Login or Register  to view this content.

To send the e-mail:

Please Login or Register  to view this content.

Hope this helps.

One other thought ...

It is not Visual Basic that is difficult. It is understanding the object model of the application. In this case, your task is doubly difficult because you are struggling with both the Excel object model (example: how to convert a list of e-mail addresses in an Excel spreadsheet to a single text string to send to LotusNotes) and the Notes object model.

So, do not be so hard on yourself. You are climbing a mountain that very few people have attempted.

Greetings,

I have an Excel file which uses VB to create strings for an email message via Lotus Notes. The user enters information via text fields and controls. A push button grabs this information into the message body, saves the current file to a shared drive (date & shift becomes file name) and attaches itself to a Lotus Notes email to a grop defined in the Adress book. This has worked very well for the last couple years but I would prefer to include a link to the newly created file rather than attaching it. I have not had any luck. I am able to have the path and filename print in the message but cannot contrive a "clickable" link. Any help would be greatly appreciated.\

Thanks and regards,

Jay m. Brackett

Noob,

Thanks for your quick response! The information you linked to is certainly helpful but it doesn't aswer my immediate need. The closest I saw was a sample that purportedly would create a link. I am still getting just plain text: "\\"server"\"share"\Esters\Shift Reports\January 2007/Esters Shift Report 01-17-2007, 2nd Shift.xls"

I've tried different formats, including "s:\shared\Esters\Shift Reports\January 2007/Esters Shift Report 01-17-2007, 2nd Shift.xls"

No luck, it still comes in as plain text. Is there something other than:
"Dim stURL as String" that I should be using? Some other variable?

Thanks,

Jay

Noob,

I looked at the sample code tried variations of it and I am still only getting plain text in my message body when viewing the received message. Maybe I'm missing something. Thanks again for your help. - Jay

Hey Noob,

In case it ever comes up again:

http://www-1.ibm.com/support/docview...id=swg21096902

Thanks again!

Jay