Hi
I have two tables:
email_tbl
Data_table
Data table is is used to create a Form
Data_form
In the form I have a field
System_type.
This is a combobox which lists PAF, POLFS, UNIX for example.
I have created the following code which sends an email out direct using lotus note.
- vb()
-
-
Code: ( vb )
-
-
Public module
-
Public Sub SEND_EMAILS()
-
'use this one
-
'open the session with the lotus notes server
-
'this sub will output a report as a file
-
'attach the file and add the predetermined subject and body
-
'delete the file that was output
-
'close the session with the server
-
-
If OPEN_SESSION Then
-
-
'output report to text file on C:\
-
DoCmd.OutputTo acOutputReport, "POL1234", acFormat, "C:\Documents and Settings\steven.birch\Desktop\OCP Information\POL Report\fred.doc", False
-
-
'put your do loop here
-
-
'reference the email report sub to mail the file
-
If EMAIL_REPORT("antonio.jamasb@postoffice.co.uk", "My Email Body", "My Subject Line", "C:\Documents and Settings\antonio.jamasb\Desktop\OCP Information\POL Report") = True Then
-
MsgBox "Message Sent"
-
Else
-
'error in email module
-
End If
-
-
'end your loop here
-
-
'delete the file
-
'Kill ("C:\Documents and Settings\antonio.jamasb\Desktop\OCP Information\POL Report\POL1234")
-
-
'call the close session sub to destroy the objects
-
CLOSE_SESSION
-
-
Else
-
'session not opened properly
-
End If
-
-
End Sub
-
-
Public Function OPEN_SESSION() As Boolean
-
-
Dim objSession As Object
-
Dim strServer As String
-
Dim strMailFile As String
-
-
'lotus notes must be open for module to work correctly
-
If MsgBox("Do you have lotus notes running?", vbCritical + vbYesNo, "Warning!") = vbYes Then
-
'this code must be left out of the loop so that only one session is started
-
Set objSession = CreateObject("Notes.NOTESSESSION")
-
-
strServer = objSession.GETENVIRONMENTSTRING("mailserver", True)
-
strMailFile = objSession.GETENVIRONMENTSTRING("mailfile", True)
-
-
Set mobjDB = objSession.GETDATABASE(strServer, strMailFile)
-
-
OPEN_SESSION = True
-
Else
-
MsgBox "Please start Lotus Notes and try again.", vbOKOnly, "Emails"
-
OPEN_SESSION = False
-
End If
-
-
End Function
-
-
Public Function EMAIL_REPORT(strSendTo As String, strBody As String, strSubject As String, Optional strFile As String) As Boolean
-
On Error GoTo EmailReport_Err
-
-
Dim objDoc As Object
-
Dim objRichTextAttach As Object
-
Dim objRichTextItem As Object
-
Dim objAttachment As Object
-
-
Const NOTES_RECIPIENTS = ""
-
Const NOTES_REPORTS_ADMIN_USER = ""
-
Const NOTES_MAIL_FILE = "C:\Documents and Settings\steven.birch\Application Data\notes\mail\birchs.nsf"
-
-
Set objDoc = mobjDB.CREATEDOCUMENT
-
Set objRichTextAttach = objDoc.CREATERICHTEXTITEM("File")
-
Set objRichTextItem = objDoc.CREATERICHTEXTITEM(objDoc, "Body")
-
-
If strFile <> "" Then
-
Set objAttachment = objRichTextAttach.EMBEDOBJECT(1454, "", strFile)
-
End If
-
-
'set up the email to be sent
-
objRichTextItem.AppendText strBody
-
objDoc.REPLACEITEMVALUE "SendTo", strSendTo
-
objDoc.REPLACEITEMVALUE "Subject", strSubject
-
-
objDoc.SAVEMESSAGEONSEND = True 'send E-mail
-
objDoc.SEND False 'false for do not attach a form
-
-
EMAIL_REPORT = True
-
-
Exit_Here:
-
Set objAttachment = Nothing
-
Set objDoc = Nothing
-
Set objRichTextAttach = Nothing
-
Set objRichTextItem = Nothing
-
Exit Function
-
-
EmailReport_Err:
-
EMAIL_REPORT = False
-
Resume Exit_Here
-
-
End Function
-
-
Sub CLOSE_SESSION()
-
-
Set mobjDB = Nothing
-
-
End Sub
-
Public module
-
'use this
-
'recipient as string, bodytext as string,saveit as Boolean)
-
'This public sub will send a mail and attachment if neccessary to the
-
'recipient including the body text.
-
'Requires that notes client is installed on the system.
-
Public Sub SendNotesMail()
-
'Set up the objects required for Automation into lotus notes
-
Dim Maildb As Object 'The mail database
-
Dim UserName As String 'The current users notes name
-
Dim MailDbName As String 'THe current users notes mail database name
-
Dim MailDoc As Object 'The mail document itself
-
Dim AttachME As Object 'The attachment richtextfile object
-
Dim Session As Object 'The notes session
-
Dim EmbedObj As Object 'The embedded object (Attachment)
-
DoCmd.OutputTo acOutputReport, "POL1234", acFormat, "C:\Documents and Settings\steven.birch\Desktop\OCP Information\POL Report\fred.doc", False
-
'Start a session to notes
-
Set Session = CreateObject("Notes.NotesSession")
-
'Next line only works with 5.x and above. Replace password with your password
-
'Session.Initialize ("password")
-
'Get the sessions username and then calculate the mail file name
-
'You may or may not need this as for MailDBname with some systems you
-
'can pass an empty string or using above password you can use other mailboxes.
-
UserName = Session.UserName
-
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
-
'Open the mail database in notes
-
Set Maildb = Session.GETDATABASE("", MailDbName)
-
If Maildb.ISOPEN = True Then
-
'Already open for mail
-
Else
-
Maildb.OPENMAIL
-
End If
-
'Set up the new mail document
-
Set MailDoc = Maildb.CREATEDOCUMENT
-
MailDoc.Form = "Memo"
-
MailDoc.sendto = "steven.birch@postoffice.co.uk"
-
MailDoc.Subject = "test"
-
MailDoc.Body = "hello is there anybody out there"
-
MailDoc.SAVEMESSAGEONSEND = SaveIt
-
'Set up the embedded object and attachment and attach it
-
Attachment = "C:\Documents and Settings\steven.birch\Desktop\OCP Information\POL Report\fred.doc"
-
If Attachment <> "" Then
-
Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
-
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
-
MailDoc.CREATERICHTEXTITEM ("Attachment")
-
End If
-
'Send the document
-
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
-
MailDoc.SEND 0, Recipient
-
'Clean Up
-
Set Maildb = Nothing
-
Set MailDoc = Nothing
-
Set AttachME = Nothing
-
Set Session = Nothing
-
Set EmbedObj = Nothing
-
End Sub
At the moment the Email address is hardcoded in (Line 149), but I am looking to create a string code to look a report which will be run that will match all the email address for the system selected. This is a simple issue, but I can't get my head round it.
I joined email_table with data_table with an inner join as both tables have the FIELD System_change, the problem I have is I cannot work out how to tell the query to look at FIELD System_change on FORM data_form?
Am I going about this the right way I thought if I could return this report I could then create a string looking at this report?
Help Help Help....
I haven't had a response on my last 3 posts, but have managed to work them out myself this time I have absolutely no idea so am very desperate.
I have also added the VB thing to try and make this easier to read, but it still shows the same so please accept my apologies
