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

 

[mkov]

I have that part figured out, but I have a field in a table that contains multiple e-mail addresses (between 1 and 20 addresses seperated by a ";&quot.

Example: email1;email2;email3;email4....

I have not been able to figure out how to pull each e-mail address out of that field and put it in an array.

Hopefully this makes sense.

Thanks.

 

[lespaul]

Somewhere in your code you're going to have to extract that field and parse it into individual email addresses (based on they are all divided by ';') and put each individual address in an element of the array.

The other option would be to normalize your database by having an intermediate table to store all the email addresses individually. Then you could just loop through the recordset and add the field into the array.

To normalize this, you would need to create a new table with the same PK as your existing table and add a field for email address:

Existing table
PK Field
Other fields
EmailAddresses (remove this field from this table)

New Table
PK Field
EmailAddress

HTH

Leslie

 

[stevenls]

Ben,

Good answer on the loop. I now have a little app that performs exactly how I need it to.. and even attaches as many files as needed that are located in whatever folder the system tells it to look at.

very nice..

S.

 

[oharab]

mkov,
If you are using Access 2k, then you can use the Split function:
dim vEmail as variant
vEmail=spilt("bob@mail.com;bill@mail.com;jane@mail.com",";&quot
'Splits the string into an array

Call maildoc.Send( False, vEmail )


If you want to send a query or report with the email you will need to save it to a file, then attach the file to the mail message.

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

 

[mkov]

lespaul,
I like the idea of having a table of e-mail addresses. That would be really easy to loop through and pick up the e-mail addresses.

oharab,
I have access xp, so I am assuming from what you are saying that the Split function won't work?

I was affraid that the files would have to be saved first. The problem with that is that my one routine sends out 250 different e-mails at a time and attaches the results of 2 reports to each. It currently takes about 15 minuates with SendObject, but I am affraid to even think how long it would take if I had to save every report.

Thanks for all of the help.

 

[oharab]

Sorry, I meant to say 2k or above, so yes it will.

Are you sending the same reports on each email, or does the data on them change each time.
Either way, it should be the same or quicker than using SendObject, as Access goes through the same process when it creates the emails, it just does it in the back ground.

cheers

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

 

[mkov]

I will give the Split function a try.

They are the same reports, but have different data each time.

I forgot about that, maybe this won't be so bad. Notes also seems to process the e-mail faster than Outlook Express did.

Again, thanks for the help. You guys are the best.

 

[mkov]

oharab,

I was able to get the Split function to work without a problem. You are correct, it is actually a little faster saving the report and then attaching it to the e-mail. Thank you very much for your help, you are the man.

One more question though:

Is there a way to bring the e-mail up and edit before it is sent? With SendObject, you could it wether to send it right away or bring up the e-mail for editing, just wondering if you can do the same in Notes.

Thanks.

 

[MJD1]

Hi!

I stumbled accross this thread which I think is awesome. This is something Id love to be able to use, however, as a technical user, I think this is going to be a learning curve.

As such, I have a few (simple) questions.

can someone tell me if this code will work with A97? I've tried, but can't seemed to make it work.

How do I lauch this code?? I'm trying with the runcode function in a macro but again, I can't get it to work.

thanks in advance for your help!

 

[MJD1]

When I'm in the module and click on the "go-Continue" button, it works.

SO then, how do I call this function in a macro or docmd??

thanks!

 

[MJD1]

Well, turns out i didn't need any help!!!

I figured it out.

to call the function....type in "Call test".....it was right under my nose!

 

[marksnow]

anyone know how to do the above but just get the email composed in Lotus Notes and then be able to edit it or add additional documents before you send it?

IE: automatically attach a document and set the sendto and subject line and then the user can add additional text etc.

 

[marksnow]

As there was another person in this thread looking for this info I shall post it here as I found the answer to my question.

The following code allows you to open a new email and specify the Email address, subject, body, attachments and then still be able to edit the email before you send it.


Found this code @

http://www.granite.ab.ca/access/email/lotusnotesedit.htm

Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
Declare Function ShowWindow& Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long)

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Function CreateNotesSession&()
Const notesclass$ = "NOTES"
' "Neues Memo - Lotus Notes"
Const SW_SHOWMAXIMIZED = 3
Dim Lotus_Session As Object

Dim rc&
Dim lotusWindow&
Set Lotus_Session = CreateObject("Notes.NotesSession")

DoEvents
DoEvents
lotusWindow = FindWindow(notesclass, vbNullString)
If lotusWindow <> 0 Then
rc = ShowWindow(lotusWindow, SW_SHOWMAXIMIZED)
rc = SetForegroundWindow(lotusWindow)
CreateNotesSession& = True
Else
CreateNotesSession& = False
End If
End Function

Sub CreateMailandAttachFileAdr(Optional IsSubject As String = "", Optional SendToAdr As String, Optional CCToAdr As String, Optional BCCToAdr As String = "", Optional Attach1 As String = "", Optional Attach2 As String = "")
Const EMBED_ATTACHMENT As Integer = 1454
Const EMBED_OBJECT As Integer = 1453
Const EMBED_OBJECTLINK As Integer = 1452

Dim s As Object ' use back end classes to obtain mail database name
Dim db As Object '
Dim doc As Object ' front end document
Dim beDoc As Object ' back end document
Dim workspace As Object ' use front end classes to display to user
Dim bodypart As Object '

Call CreateNotesSession&

Set s = CreateObject("Notes.Notessession") 'create notes session
Set db = s.getDatabase("", "") 'set db to database not yet named
Call db.Openmail ' set database to default mail database
Set beDoc = db.CreateDocument
Set bodypart = beDoc.CreateRichTextItem("Body")

' Filling the fields
'###################
beDoc.subject = IsSubject
beDoc.SendTo = SendToAdr
beDoc.CopyTo = CCToAdr
beDoc.BlindCopyTo = BCCToAdr

'''''''''''''''''''''''''
''If you want to send a message to more than one person or copy or
''blind carbon copy the following may be of use to you.

'beDoc.sendto = Recipient
'beDoc.CopyTo = ccRecipient
'beDoc.BlindCopyTo = bccRecipient

''Also for multiple email addresses you just set beDoc.sendto (or CopyTo or
''BlindCopyTo) to an array of variants each of which will receive the message. So

'Dim recip(25) as variant
'recip(0) = "emailaddress1"
'recip(1) = "emailaddress2" e.t.c

'beDoc.sendto = recip
''''''''''''''''''''''''

' beDoc.Body = "Hello Mary Lou, Goodbye heart"

' Attaches I
'###########
' Call bodypart.EmbedObject(EMBED_ATTACHMENT, "", DirWithPathFileName, FileName)
If Len(Attach1) > 0 Then
If Len(Dir(Attach1)) > 0 Then
Call bodypart.EmbedObject(EMBED_ATTACHMENT, "", Attach1, Dir(Attach1))
End If
End If

' Attaches II
'############
If Len(Attach2) > 0 Then
If Len(Dir(Attach2)) > 0 Then
Call bodypart.EmbedObject(EMBED_ATTACHMENT, "", Attach2, Dir(Attach2))
End If
End If

Set workspace = CreateObject("Notes.NotesUIWorkspace")

' Positioning Cursor
'###################
' Call workspace.EditDocument(True, beDoc).GotoField("Body")
Call workspace.EditDocument(True, beDoc).GotoField("Subject")

Set s = Nothing

End Sub

 

[KUZZ]

<<<DWALKER>>>
Thanx for the code. It works great. I do need however that extra line to pass the "password" through. Would you please post it.

Thank you.

Good luck,
Kuzz

&quot;Time spent debating the impossible subtracts from the time during which you
can try to accomplish it.&quot;

 

[jkirkland]

This is really cool stuff. Could anyone show me how to send to multiple recipients from a table of email addresses? I am new to VBA and don't have the whole 'loop' thing down yet.

I basically want to replace the 'strTo='Email Address' part of oharab's to something that will loop through a table of email addresses and send the message to those people listed in the table. This list grows and shrinks over time which is why I don't want to use a static list of addresses in the code.

Thanks for the assistance!

oharab's code that I am attempting to use is:
Option Compare Database
Option Explicit

Private Declare Function apiFindWindow Lib "user32" Alias _
"FindWindowA" (ByVal strClass As String, _
ByVal lpWindow As String) As Long

Private Declare Function apiSendMessage Lib "user32" Alias _
"SendMessageA" (ByVal Hwnd As Long, ByVal msg As Long, ByVal _
wParam As Long, lParam As Long) As Long

Private Declare Function apiShowWindow Lib "user32" Alias _
"ShowWindow" (ByVal Hwnd As Long, ByVal nCmdShow As Long) As Long

Private Declare Function apiIsIconic Lib "user32" Alias _
"IsIconic" (ByVal Hwnd As Long) As Long

Function SendNotesMail(strTo As String, strSubject As String, strBody As String, strFiles())
Dim doc As Object 'Lotus NOtes Document
Dim rtitem As Object '
Dim Body2 As Object
Dim ws As Object 'Lotus Notes Workspace
Dim oSess As Object 'Lotus Notes Session
Dim oDB As Object 'Lotus Notes Database
Dim X As Integer 'Counter
'use on error resume next so that the user never will get an error
'only the dialog "You have new mail" Lotus Notes can stop this macro
If fIsAppRunning = False Then
MsgBox "Lotus Notes is not running" & Chr$(10) & "Make sure Lotus Notes is running and you have logged on."
Exit Function
End If

On Error Resume Next

Set oSess = CreateObject("Notes.NotesSession")
'access the logged on users mailbox
Set oDB = oSess.GETDATABASE("", "")
Call oDB.OPENMAIL

'create a new document as add text
Set doc = oDB.CREATEDOCUMENT
Set rtitem = doc.CREATERICHTEXTITEM("Body")
doc.sendto = strTo
doc.Subject = strSubject
doc.Body = strBody & vbCrLf & vbCrLf

'attach files
If UBound(strFiles) > -1 Then
For X = 0 To UBound(strFiles)
If Not (IsEmpty(strFiles(X))) Then
Set Body2 = rtitem.EMBEDOBJECT(1454, "", strFiles(X))
End If
Next X
End If
doc.SEND False
End Function

Sub test()
Dim strTo As String 'The sendee(s) Needs to be fully qualified address. Other names seperated by commas
Dim strSubject As String 'The subject of the mail. Can be "" if no subject needed
Dim strBody As String 'The main body text of the message. Use "" if no text is to be included.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim afiles()

Set db = CurrentDb
Set rst = db.OpenRecordset("tblFiles")

On Local Error Resume Next
rst.MoveLast
ReDim afiles(rst.RecordCount)

strTo = "bpo@robotparade.co.uk"
strSubject = "Test Message"
strBody = "This is a test"
strBody = strBody & vbCrLf & "Just add new lines by concatenating vbCrLF"

Do Until rst.BOF
afiles(rst.Index) = rst!FilePath
rst.MovePrevious
Loop
SendNotesMail strTo, strSubject, strBody, afiles
End Sub

Private Function fIsAppRunning() As Boolean
'Looks to see if Lotus Notes is open
'Code adapted from code by Dev Ashish

Dim lngH As Long
Dim lngX As Long, lngTmp As Long
Const WM_USER = 1024
On Local Error GoTo fIsAppRunning_Err
fIsAppRunning = False

lngH = apiFindWindow("NOTES", vbNullString)

If lngH <> 0 Then
apiSendMessage lngH, WM_USER + 18, 0, 0
lngX = apiIsIconic(lngH)
If lngX <> 0 Then
lngTmp = apiShowWindow(lngH, 1)
End If
fIsAppRunning = True
End If
fIsAppRunning_Exit:
Exit Function
fIsAppRunning_Err:
fIsAppRunning = False
Resume fIsAppRunning_Exit
End Function

 

[lespaul]

I'm not at work, so I don't have any code samples handy, but here's a start.

You are going to want to write an SQL statement to retrieve those addresses and loop through that record set adding the information to the strTo string, separating by commas (another option is to always add the ", " and strip if off the beginning when you're done):

while not recordset.eof do
  if strTo <> '' then
    strTo = strTo & ", " & recordset.fieldname
  else
    strTo = recordset.fieldname
  end if
next

my syntax is off, I'm more fluent in Delphi, not VBA, but that should point you in the right direction!!

Leslie

 

[Magic84]

Hi all together,

I have a question. How can I send a report via email? The code from Oharab works fine. But the attachement has to be a report and not a file as such.

Is someone able to help me in this special case?

Thank you a lot!

Orhan

 

[snyperx3]

oharab,

can you please show how to view the email before sending it using your code? I see its been done on this thread but it was with completely different code than yours. Thanks for the help.

Pete

 

[snyperx3]

Ummm...never mind. I figured it out.

Set ws = CreateObject("Notes.NotesUIWorkspace")
Call ws.EditDocument(True, doc).GotoField("Body")

Thanks for the original post though. Very helpful.

Pete