Access/Lotus Notes

Hi guys i'm sorry to ask this question again but I can't seem to find the
answer? And I really need some help with either coding or macro. I have a
macro that does a SendObject. it does every thing i want until the report
arrives in lotus notes to be sent as an email. my problem is with lotus notes
i want the email to be sent automatically without the user having to click
send??? is this possible?? Below is the code that i have does en one know wot
is missing in th code for that final send?
plz plz help?? jo
------------------------------------------------------------
' checker_Query_overdue
'
'------------------------------------------------------------
Function checker_Query_overdue()
On Error GoTo checker_Query_overdue_Err

If (DCount("*", "OnOpenOverdue") 0) Then
DoCmd.SendObject acReport, "OnOpenOverdue", "SnapshotFormat(*.snp)",
", "", "", "Gauge Control", "Gauges Overdue",
False, ""
End If
If (DCount("*", "OnOpenOverdue") = 0) Then
Beep
MsgBox "No Gauges found overdue today..", vbOKOnly, "No Gauges
found.."
End If

Well, sendoject puts the email in the outbox, from that point on your
dealing with Lotus Notes.

The only suggestion I can come up with is two things:

1) make sure notes email is running before they attempt the sendobject. For
example, if you not running outlook, then the email stays in the outbox
until you launch outlook and do a send receive. However, if you launch
Outlook and leave it running, and then use sendobject in ms-access, the
email does not stay in the outbox...it is sent right away. So, try/check
this issue.

2) ask in the Lotus support groups if note supports automaton. If it does,
then ask them for a windows or VB, or VBA script that shows how to send a
email with an attachment (that does not stay in the outbox). That script
should be easily adopted to run in ms-access. I would then setup code to run
the ms-access report, save report as a pdf, and then have ms-access code run
the Lotus automaton script to create the email, attach the pdf file, and
then send it.

I can post the code to save the report as a pdf file, but until you come
back with a working Lotus email script, it will not of any use to you....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

merci


"jo" a écrit dans le message de groupe de
discussion : ...
Hi guys i'm sorry to ask this question again but I can't seem to find the
answer? And I really need some help with either coding or macro. I have a
macro that does a SendObject. it does every thing i want until the report
arrives in lotus notes to be sent as an email. my problem is with lotus
notes
i want the email to be sent automatically without the user having to click
send??? is this possible?? Below is the code that i have does en one know
wot
is missing in th code for that final send?
plz plz help?? jo
------------------------------------------------------------
' checker_Query_overdue
'
'------------------------------------------------------------
Function checker_Query_overdue()
On Error GoTo checker_Query_overdue_Err

If (DCount("*", "OnOpenOverdue") 0) Then
DoCmd.SendObject acReport, "OnOpenOverdue",
"SnapshotFormat(*.snp)",
", "", "", "Gauge Control", "Gauges Overdue",
False, ""
End If
If (DCount("*", "OnOpenOverdue") = 0) Then
Beep
MsgBox "No Gauges found overdue today..", vbOKOnly, "No Gauges
found.."
End If

I had the same problem, I am not a programmer, but I managed to get it
working by looking in all these threads. This is the code I found. You need
to put this first bit in to a new module-

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, strCC 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
Do While fIsAppRunning = False
MsgBox "Lotus Notes is not running" & Chr$(10) & "Make sure Lotus Notes
is running and press OK."
Loop


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.CopyTo = strCC
doc.Subject = strSubject
doc.body = strBody & vbCrLf & vbCrLf


'attach files
If strFIleName "" Then
Set Body2 = rtitem.EMBEDOBJECT(1454, "", strFIleName)
If UBound(strFiles) -1 Then
For x = 0 To UBound(strFiles)
Set Body2 = rtitem.EMBEDOBJECT(1454, "", strFiles(x))
Next x
End If
End If
doc.send False
End Function

Private Function fIsAppRunning() As Boolean
'Looks to see if Lotus Notes is open
'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


Then you call this function profm a private sub as follows :

Dim strTo As String 'The sendee(s) Needs to be fully qualified
address. Other names seperated by commas
Dim strCC As String 'The sendee(s) Needs to be fully qualified address
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 FirstFile As String 'If you are embedding files then this is the
first one. Use "" 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.



DoCmd.OutputTo acOutputReport, "Reportemail", acFormatPDF,
"c:\MOCreport.pdf"
strTo = [Forms]![tblManagementOfChangeRequest].[Text411]
strCC = [Forms]![tblManagementOfChangeRequest].[Text410]
strSubject = "You have been selected as approver for MOC " &
[Forms]![tblManagementOfChangeRequest]![strNumber]
strBody = "Please review in MOC database" & vbCrLf & "Thanks" & vbCrLf &
vbCrLf & [Forms]![tblManagementOfChangeRequest].[Text418]
strBody = strBody & vbCrLf
FirstFile = "c:\MOCreport.pdf"
SecondFile = ""
ThirdFile = ""
SendNotesMail strTo, strCC, strSubject, strBody, FirstFile, SecondFile,
ThirdFile


Hope this helps
CK


"1000€" wrote:

merci


"jo" a écrit dans le message de groupe de
discussion : ...
Hi guys i'm sorry to ask this question again but I can't seem to find the
answer? And I really need some help with either coding or macro. I have a
macro that does a SendObject. it does every thing i want until the report
arrives in lotus notes to be sent as an email. my problem is with lotus
notes
i want the email to be sent automatically without the user having to click
send??? is this possible?? Below is the code that i have does en one know
wot
is missing in th code for that final send?
plz plz help?? jo
------------------------------------------------------------
' checker_Query_overdue
'
'------------------------------------------------------------
Function checker_Query_overdue()
On Error GoTo checker_Query_overdue_Err

If (DCount("*", "OnOpenOverdue") 0) Then
DoCmd.SendObject acReport, "OnOpenOverdue",
"SnapshotFormat(*.snp)",
", "", "", "Gauge Control", "Gauges Overdue",
False, ""
End If
If (DCount("*", "OnOpenOverdue") = 0) Then
Beep
MsgBox "No Gauges found overdue today..", vbOKOnly, "No Gauges
found.."
End If

Prior to this code.."M" is a .snp file. HTH - Bob
Set Tools - References to "Lotus Domino Objects"

Dim NotesSession As NotesSession
Dim NotesDB As NotesDatabase
Dim NotesDoc As NotesDocument
Dim TheE As NotesRichTextItem
Dim NotesDir As NotesDbDirectory
'Instantiate NotesSession
Set NotesSession = CreateObject("Lotus.NotesSession")
NotesSession.Initialize
Set NotesDir =
NotesSession.GetDbDirectory(NotesSession.GetEnviro nmentString("MailServer"))
Set NotesDB = NotesDir.OpenMailDatabase
Dim sTO$, TCC(2) As String, Gog$
'Create new Notes document
'.............cboEM = "To:" -- One_EM & Two_EM = 2 "cc:"
sTO = cboEM
If Len(One_EM) 0 Then
TCC(0) = One_EM
End If
If Len(One_EM) 0 Then
TCC(1) = Two_EM
End If
'Ck Gog Length
If Len(One_EM) 0 Then
Gog = One_EM
If Len(Two_EM) 0 Then
Gog = Gog & ", " & Two_EM
End If
ElseIf Len(Two_EM) 0 Then
Gog = Two_EM
If Len(One_EM) 0 Then
Gog = Gog & ", " & One_EM
End If
Else
Gog = ""
End If
Set NotesDoc = NotesDB.CreateDocument
'Send to strTo and set Subject of e-mail
Call NotesDoc.ReplaceItemValue("Sendto", sTO)
If Len(Gog) 0 Then
Call NotesDoc.ReplaceItemValue("Copyto", TCC)
End If
If GoRush = True Then 'GoRush is a Yes/No Control on the Form
Call NotesDoc.ReplaceItemValue("Subject", "RUSH - Req Snapshot")
Else
Call NotesDoc.ReplaceItemValue("Subject", "Req Snapshot")
End If
Set TheE = NotesDoc.CreateRichTextItem("AD")
Dim RT As NotesRichTextItem
Set RT = NotesDoc.CreateRichTextItem("Body")
RT.AppendText ("Requisition Attached")
RT.AddNewLine (1)
If Not IsNull(LexNote) Then RT.AppendText (LexNote)
Call TheE.EmbedObject(1454, "", M, "")
NotesDoc.SaveMessageOnSend = True
Call NotesDoc.ReplaceItemValue("SendTo", sTO)
Call NotesDoc.Send(False)
Set NotesSession = Nothing
MsgBox "Email sent.", vbOKOnly + vbInformation, "Email Sent..."
End If