Mailing report in lotus notes - can ollyb help? or anyone

[jambonjamasb]

After months of work I have finally finished my Change Control management database.

It looks flash all the filters work, all the reports work, importing from Xcel is automatic its lovely........but I am struggling with my final little detail.

I have absolutely no idea where to start. I excel I am able to use the following code:-

Sub Email()


Dim EmailAdd As String
Dim EmailSub As String

Sheets("sheet3").Activate

EmailSub = Sheets("Message").Range("C3").Text

EmailAdd = "mailto:" & Sheets("sheet3").Range("b1").Text & "?subject=" & EmailSub

MsgBox "Address is " & EmailAdd


ActiveWorkbook.FollowHyperlink Address:=EmailAdd, _
NewWindow:=True

End Sub

But putting the following into an access format is proving difficult.

I created a mailing list in a table email_table.
What I want to do is on my form I would select a combo box called system. This lists the different systems we have. For example SAP or POLFS. this table that powers the form is called data_table andthe main form is called data_form.

what I am looking to do is when the field "system" is completed on the data_form, my query will return all the email address' assigned to that product.

This is where I am now confused, how do I get access to use the returned list to populate an email for sending with these email address, and can i get it to automatically attach a specified report i.e daily_summary_report.


Please can someone help me.

thanks.
Tony

[jambonjamasb]

I have been trying to use some peoples VBA, but I am not sure how to run a Public Sub rather than a Private

[jambonjamasb]

well i have tried to have a go at this but feel i may have ovrely complicated it. Also I am not sure how to use a table to populate the email address as this will vary dependant on type of change. anyhow. please help me.
vb()

Sub SEND_EMAILS()
 
'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", acFormatword, "C:\Documents and Settings\antonio.jamasb\Desktop\OCP Information\POL Report\POL1234", False
 
'put your do loop here
 
'reference the email report sub to mail the file
If EMAIL_REPORT("[e-mail]", "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
 
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
 
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\antonio.jamasb\Application Data\notes\mail\jamasba.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
 
Public Sub CLOSE_SESSION()
 
Set mobjDB = Nothing
 
End Sub

[jambonjamasb]

This can be closed I changed the code and sorted it myself.