Linking Access with Lotus Notes - please help! 9

[Sech]

ARGGGGGHHHHHHHHHHHH!

I work for HSBC. Recently we moved our E-Mail system from Outlook to Lotus Notes. I have developed several databases that have automatic E-Mailing functions within them. As an example I would add a reference to Microsoft Outlook 8.0 Object Library. I would then use code to open a new mail message linking into a saved template, attach a number of newly created Excel files and then send it to a chosen recepient within the database. All of this would occur invisibly when the user pressed the Send button.

However now we have migrated across to Lotus Notes none of this will work any more of course. I am urgently required to find out how to do the same thing with Lotus Notes so that I can update the databases. This is quite an emergency as we regularly utilize the automatic E-Mail functions.

Does anyone know how to do all this with Lotus Notes instead of Outlook. I would be extremely grateful if anyone could help me in any way with this as I have literally no knowledge of linking Access and Notes. Also if anyone knows any site addresses or books for sale that would answer my questions, could you please post them up for me?

Thankyou for your assistance

 

[oharab]

Thanks for the vote of confidence Aidan, unfortunately time is something I don't have! I am busy trying to work with ASP, Javascript and DHTML behaviours whilst trying to convince my IT department that putting databases on a web interface is a good idea! Right now I'm spending a lot of time trying to find the next brick wall to bang my head against!!!

I'll get round to it one day, I promise! I want to get myself to the top of the list again !

B ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[hughesai]

This thread just refuses to go away.

Ben,

All the code above works great.
I only have one small further thing to ask.
How can the code be altered to save the email in my own sent box. I have tried using the SAVE function of the NOTESDOCUMENT object, but that does not work.

Do you have any ideas?

Aidan Hughes

 

[oharab]

This has got to be one of the longest running threads by now!!!
I think, what you need to do is change
'Send E-Mail
objDocument.Send False

to
'Send E-Mail
objDocument.Send True

but I'm not sure.
Let me know if it works.

Ben ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[hughesai]

Hi Ben,

I'm afraid not.

Changing
objDocument.Send False
to
objDocument.Send True
does not work. The document is not saved, nor is the e-mail sent.
The associated error is "9 - Subscript out of range"

Regards,

Aidan

 

[oharab]

Bugger!
I'll look into it as soon as I can.

B ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[danrobson]

I have no idea how I came across his thread (I was looking for some info on Tabledefs) but it's great stuff. This is Tek-Tips at it's finest.

Dan

 

[Nene75]

Sech/Oharab

Is this process from Access to Lotus Notes or vice versa?

I am trying to get data from Lotus Notes Mail box and send it to Access DB.

 

[oharab]

This process Allows you to send an email from Access, using Lotus Notes.

Going the other way round is beyond me I'm afraid, IT won't trust me enough to let me design Lotus Apps!

You may be better posting in the Lotus Notes forum.

Good Luck.

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[Nene75]

Thanks for replying...
I'll check out Lotus Notes forum!

 

[MaxEd]

Hi,

I can't use the DAO in ACCESS 2000 for this. So I can't get the posted code from this thread to work. I am trying to get Access to send an email with Lotus Notes as the email client to multiple people in different departments with a query that is specific to each department. I want this done in the background so the user doesn't even see this going on. I will most likely use the scheduler to load up the database. My knowledge of using objects is limited so please be patient. Thank you.

-Laughter works miracles.

 

[oharab]

Max,
Why can't you use DAO? A2k does have DAO, I use it all the time. You just need to set a reference to Microsoft Data Access Objects 3.6 in the references and you are away.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[MaxEd]

Oharab, I guess it was one of those references things that caused it to fail. It worked out great. The code that I used automatically sends out an email every 1st and 10th day of each month and if it is not sent after the 10th day then any day after the 10th it will try to resend it. I use the task scheduler to automate the process.

Has anyone had encountered any problems with this code since it requires that you are logged into Lotus Notes? I'd appreciate it since this code that I have written will be going live in about 2 weeks. Thank you.

-Laughter works miracles.

 

[dwalker]

hughesai,

To save a copy in your sent folder add this:

objDocument.SaveMessageOnSend = True

Also, here's a function I use, it's much simpler than oharab's and works like a charm, just be sure to add a reference to Lotus Domino Objects domobj.tlb. oharab's code checks to see if Notes is running etc, this function doesn't do that but will start notes if it's not yet running (so long as it's locally installed and not delivered via a network). If you like, you can add a line to pass the user's password if Notes isn't yet running, if anybdy wants it, just post back.

Public Sub SendViaNotes(SendTo As Variant, _
               Optional CopyTo As Variant, _
               Optional BlindCopy As Variant, _
               Optional BodyText, _
               Optional SubjectText As String, _
               Optional Attachment1 As String, _
               Optional Attachment2 As String, _
               Optional SaveSent As Boolean)

' Written by Damien Walker
' Copy as you like but do me a favour and leave
' my name in it!

Dim notes_session As Object                                 ' Notes session
Dim notes_db As Object                                      ' Notes database
Dim notes_doc As Object                                     ' Notes document (memo)
Dim notes_body As Object                                    ' Body section of document

If IsMissing(CopyTo) Then CopyTo = Null                     ' give missing elements a value
If IsMissing(BlindCopy) Then BlindCopy = Null
If IsMissing(SubjectText) Then SubjectText = ""
If IsMissing(BodyText) Then BodyText = ""

Set notes_session = CreateObject("Notes.Notessession")      ' create Notes session

Set notes_db = notes_session.GetDatabase("", "")            ' set notes_db to database not yet named

Call notes_db.OPENMAIL                                      ' assigns notes_db to current user's mail

Set notes_doc = notes_db.CreateDocument                     ' create new email

Call notes_doc.ReplaceItemValue("Sendto", SendTo)           ' set receipt address

Call notes_doc.ReplaceItemValue("Copyto", CopyTo)           ' set CC address

Call notes_doc.ReplaceItemValue("BlindCopyto", BlindCopy)   ' set BCC address

Call notes_doc.ReplaceItemValue("Subject", SubjectText)   ' set BCC address

Set notes_body = notes_doc.CreateRichTextItem("body")       ' create body section

Call notes_body.AppendText(BodyText)                        ' append body text

If Attachment1 <> &quot;&quot; Then _

    Call notes_body.EmbedObject(1454, &quot;&quot;, Attachment1)      ' attach 1st file
    End If
    
If Attachment2 <> &quot;&quot; Then _

    Call notes_body.EmbedObject(1454, &quot;&quot;, Attachment2)      ' attach 2nd file
    End If
    
notes_doc.SaveMessageOnSend = SaveSent                      ' save in sent box if true

Call notes_doc.Send(False)                                  ' send email

End Sub

The function allows up to 2 optional attachments and don't forget that if you're sending to more than one recipient you must have their addresses in an array, not a string.

Regards

Damien

 

[stevenls]

hmmm I have something similar to this that I cannot get to work.

I use the following bit of attach code to attach a single file to an email;

Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment&quot
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", "C:\Dereg\810.doc", "Attachment&quot
MailDoc.CREATERICHTEXTITEM ("Attachment&quot

But I would love it to scan through a particular folder and attach everything that is there. They are work orders so the number of attachments will change each day and I am not sure how to get Access to loop through a folder and attach everything that is in there?

Can anyone help me with that piece of code. Maybe the answer is in the code in this thread, I just didn't get it if it were.

Thanks

S.

 

[oharab]

You need to put a loop round the add attachment bit:

dim fn as string
dim strPath as string

strPath="C:\Files\"

fn=dir(strpath)
do until fn=""
if fn<>&quot;.&quot; and fn<>&quot;..&quot; then
Call notes_body.EmbedObject(1454, &quot;&quot;, strpath & fn)
end if
fn=dir
loop

see if that does it.

Cheers

BEn


----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[roytheboy]

I am taking an excel file over to an access database with the ability to fax through lotus notes.

the xls already faxes through the lotus notes...

can anyone tell me how i can get microsoft access 2000 to fax through lotus notes 4.6.

thanks in advance.

Roy

 

[oharab]

Fax thru lotus notes? That's a new one on me! All my code does is send emails, didn;t know lotus could fax. You may want to ask in the lotus notes groups for some lotus script that would do the job which could be converted into Access.

Good luck.

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[lespaul]

You should be able to do the fax the same way as an email, just change the recipient information to:

DeliveryName@FaxNumber@FaxDomain

JoeBlow@5551212@FaxDomain

(at least that's the way the fax is addressed in our LotusNotes, I think it's a standard).

HTH

Leslie

 

[mkov]

Sorry to drag this thread any longer but I have a couple things that I am hoping that can be answered.

I have used the above code to send e-mails from Access with Lotus Notes. My questions are:

1. How do I create an array of e-mail addresses from a field that has anywhere from 1 to 20 e-mail addresses in it seperated by a &quot;;&quot;? I have been trying to figure it out, but I believe that I am making it too complicated for myself, any ideas are greatly appreciated.

2. How do I attach the results of a report or query to the e-mail? I have only been able to find info on attaching a file to an e-mail. With SendObject, you can just attach the results of a report or query, I need to be able to do this the same way when sending the e-mail with Lotus Notes.

Thanks in advance for your help.

 

[lespaul]

I'm not sure about attaching results from a query, but to send to multiple recipients you need to create an array of strings and put each person that you want to send as an element of the array. Here's some LotusNotes Script that does that:

Dim recipients( 1 To 2 ) As String
recipients( 1 ) = "Joe Blow"
recipients( 2 ) = "Jane Doe"

Call maildoc.Send( False, recipients )

Now these are sent to internal recipients so I just have the people's names, but you can replace this with the email address (&quot;jblow@somewhere.com&quot and it should also work.

HTH

Leslie