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]

Here is some code I use to send lotus notes and attachments. Copy it all into a new module. There is a test subroutine that will tell you how to use it.
Best of luck.

B

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 apiSetForegroundWindow Lib "user32" Alias _
"SetForegroundWindow" (ByVal Hwnd 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, strFilename As String, ParamArray 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&quot
'access the logged on users mailbox
Set oDB = oSess.GETDATABASE("", "&quot
Call oDB.OPENMAIL

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

'attach files
If strFilename <> &quot;&quot; Then
Set Body2 = rtitem.EMBEDOBJECT(1454, &quot;&quot;, strFilename)
If UBound(strFiles) > -1 Then
For X = 0 To UBound(strFiles)
Set Body2 = rtitem.EMBEDOBJECT(1454, &quot;&quot;, strFiles(X))
Next X
End If
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 &quot;&quot; if no subject needed
Dim strBody As String 'The main body text of the message. Use &quot;&quot; if no text is to be included.
Dim FirstFile As String 'If you are embedding files then this is the first one. Use &quot;&quot; if no files are to be sent
Dim SecondFile As String 'Add as many extra files as is needed, seperated by commas.
Dim ThirdFile As String 'And so on.

strTo = &quot;Ben O'Hara/721116/WAKE/WYP@WYP&quot;
strSubject = &quot;Test Message&quot;
strBody = &quot;This is a test&quot;
strBody = strBody & vbCrLf & &quot;Just add new lines by concatenating vbCrLF&quot;
FirstFile = &quot;G:\Apps\Windows\4bpo\ExcelUtilities.exe&quot;
SecondFile = &quot;G:\Apps\Windows\4bpo\life.xls&quot;
ThirdFile = &quot;G:\Apps\Windows\ImpactXP\CompactDbs.vbs&quot;

SendNotesMail strTo, strSubject, strBody, FirstFile, SecondFile, ThirdFile
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(&quot;NOTES&quot;, 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


----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[Sech]

Thanks so much for the code, it works extremely well and has solved MOST of my problems. However with one of my databases the number of file attachments is not determined in advance, it varies depending on which record is selected, and so needs to be set up in the code. The attachment names are stored in a recordset and I need the code to run through this and attach each one in turn. Do you know how to do this with Lotus Notes, as the module you have posted requires that the number of attachments are set up in advance.

Please help (if you can). Thanks again

 

[oharab]

You can add as many attachments as is needed.

aircode:

set rs=recordset with attachments

dim strAttachments as string

do until rs.eof
strAttachments=strAttachments & rs!Filename & &quot;,&quot;
'create a comma seperated list of filenames
rs.movenext
loop

strattachments=left(strattachments,len(strattachments)-1)
'take last comma off

SendNotesMail strTo, strSubject, strBody, eval(strattacments)

I think that should work.

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[Sech]

Thanks so much again, everything is sorted now. You are the lifesaver...

 

[Sech]

To Oharab,

Unfortunately (as you may have gathered by the E-Mail I sent you), I still cannot work out how to get the multiple file attachments to work. If the files are added with commas seperating them and then Eval used I get the run time error 2482 - Microsoft Access cant find the name 'T' you entered in the expression. T is referring to the first file path's drive letter. This seems to suggest that I should add quotations around each of the file paths. But when I do this I get the run time error 2432 - The expression you entered contains invalid syntax, or you need to enclose your text data in quotes.

Using code I have tried adding many variations of quotes around the file paths e.g. &quot;&quot;File path 1&quot;,&quot;File path 2&quot;&quot;
but nothing seems to work and when it is fed through to the mail sending sub, the attachments part never works, so they cannot be going into the ParamArray correctly.

This problem is starting to drive me crazy, I've been working on it for days now! If you can help in any way at all I would be most grateful.

Thanks for all your help

 

[oharab]

You will need to create a wrapper function for my code that passes an array with the attachments.
If you bear with me I will have a look at it, but right now I've not got time to scratch my <insert appropriate appendage here>!!

Soz mate.

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[oharab]

OK then! Time for a break from real work!

Here is a new module with changed code. This time, instead of sending a list of files, you send an array with the file paths in it.
If you are only sending 1 file you still need to create an array. Have a look through the code, & if you get stuck I'll have another look.

Regards

ben
(back to work I guess!)

Option Compare Database
Option Explicit

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

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

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

Private Declare Function apiIsIconic Lib &quot;user32&quot; Alias _
&quot;IsIconic&quot; (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 &quot;You have new mail&quot; Lotus Notes can stop this macro
If fIsAppRunning = False Then
MsgBox &quot;Lotus Notes is not running&quot; & Chr$(10) & &quot;Make sure Lotus Notes is running and you have logged on.&quot;
Exit Function
End If

On Error Resume Next

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

'create a new document as add text
Set doc = oDB.CREATEDOCUMENT
Set rtitem = doc.CREATERICHTEXTITEM(&quot;Body&quot
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, &quot;&quot;, 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 &quot;&quot; if no subject needed
Dim strBody As String 'The main body text of the message. Use &quot;&quot; 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(&quot;tblFiles&quot

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

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

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(&quot;NOTES&quot;, 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
----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[JPeters]

Oh my goodness. These people deserve some stars! Wow! Star them now! ;-)

-Josh ------------------
-JPeters
These things take time...
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[Sech]

That's it!!! Thanks Oharab you are a programming god...

But now I have just one more question (Will this ever end??) I have now updated all three of the databases with the new code for Lotus Notes in place of Outlook. They all send out absolutely fine. However, just to be difficult, the users have requested that the addresses being sent to should not be seen. The mails are sent out to many different companies at once. In the Outlook code it was possible to clear the To box after the recipients had been added to the mail. Now I COULD alter the code so for each individual address it sends out a new mail but this would potentially take ages and cause the system to hang. What I'd really like to do is either blank the To box (as long as the E-Mail still got sent out to the addresses) or send the addresses in the Blind Copy box instead of the To box. The code I currently use to send the mail is....

'Create Notes session
Set objSession = CreateObject("Notes.NotesSession")

'Access users mailbox
Set objDatabase = objSession.GETDATABASE("", "")
Call objDatabase.OPENMAIL

'Create a new document
Set objDocument = objDatabase.CREATEDOCUMENT
Set objRichTextItem = objDocument.CREATERICHTEXTITEM("Body")

'Add address, subject and mail message (With two blank lines under)
objDocument.sendto = strAddress
objDocument.Subject = strSubject
objDocument.Body = strMessage & vbCrLf & vbCrLf

'Set attachment 1 full path
strAttach1 = MessageData.strAttach1Path & MessageData.strAttach1File
If Nz(Len(strAttach1), 0) > 0 Then

    'Attach attachment1 if it exists
    Set objAttachment = objRichTextItem.EMBEDOBJECT(1454, "", strAttach1)
End If

'Set attachment directory
strAttachmentDir = MessageData.strAttachmentDir

'Get first filename in directory
strCheckPath = strAttachmentDir & "*.*"
strFileName = Dir(strCheckPath)

'Run through files in attachment directory and add them to the mail
Do While strFileName <> &quot;&quot;
    'Set full attachment path
    strAttachFile = strAttachmentDir & strFileName
    
    'Attach the file
    Set objAttachment = objRichTextItem.EMBEDOBJECT(1454, &quot;&quot;, strAttachFile)
    
    'Get next filename
    strFileName = Dir
Loop

'Send E-Mail
objDocument.Send False

The addresses are being set up with the line &quot;objDocument.sendto = strAddress&quot;. Do you know how to put strAddress into the blind copy box (BCC) instead of the To box? Any help from yourself or others reading this would be greatly appreciated. And then I promise never to trouble you again....well for the time being anyhow

 

[oharab]

I think what you need is:

objDocument.BlindCopyTo = strAddress

Not tried it tho.

FWIW:

objDocument.CopyTo = strAddress should be CC:
----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[Sech]

To Oharab,

Unfortunately the objDocument.BlindCopyTo line does not appear to work. When I debug and compile Access doesn't complain but no mail is sent out. So close and yet so far....any ideas?

 

[oharab]

with notes you MUST have someone in the send to or it won't go.
Put your own name in the send to and try it. ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[Sech]

At long last it's finally working! I put a blank space in the objDocument.SendTo and then the addresses in the objDocument.BlindCopyTo. This seems to work, and everything is sorted out. Thank you so much for all your help with this, I'm glad there are people like yourself around as nobody around my office knew what to do in the slightest...

 

[oharab]

Thanks Sech. It's good to know we got a result.

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[JPeters]

Hey Ben,
Check my new profile - you've got a cameo!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[oharab]

Aw shucks. Now I'm blushing! :~/
Going to take a week off to get over the embarrassment now.
(Well I'll also be moving house, but it's mainly the embarrassment!) ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[Sech]

Great...once again I thought this was all sorted out and once again I was proved wrong! Oharab I hope you get to read this as I don't think anyone else will know how to help. The problem is with the multiple addresses. When I attach the addresses to the new memo using the code line &quot;objDocument.blindcopyto = strAddress&quot; it only sends out to the first address stored in the string. strAddress is made up from any number of E-Mail addresses all seperated by a comma and space. I have tried seperating them with just a comma and a semi-colon as well and exactly the same thing happens. This is not due to the fact they are being blind copied. I first use the line objDocument.sendto = &quot; &quot; as we discussed before. The really strange thing is that when I try splitting the addresses with commas or semi-colons in Notes itself using a test message this appears to work, it just doesn't work with the code. I have also tried inputting the different addresses with individual objDocument.blindcopyto lines but this just overwrites and just sends the last one entered. Do the addresses each need quotation marks round them or something??

Please help! This whole fiasco is driving me nuts slowly but surely...

 

[oharab]

As you can tell I am back from my house move, ready to take on the world! I may have found a solution! Instead of passing a list of names to the code, you need to pass an array with the names of the recipients.

Dim recipients(2) As String
recipients(0) = &quot;Jim Dinauer&quot;
recipients(1) = &quot;Betty Dinauer&quot;
recipients(2) = &quot;Mary Sticka&quot;

objDocument.blindcopyto = recipients

You will need to play with this slightly to make it fit your needs, but I hope you get the idea!

Let me know how you get on.

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[hughesai]

Wow, what a marathon of a thread.
I'm taking this into work tomorrow and implementing some of it right away.

oharab - this is a topic well worthy of an FAQ if you had the time !

Aidan Hughes