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........b ut 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").T ext & "?subject=" & EmailSub

MsgBox "Address is " & EmailAdd


ActiveWorkbook. FollowHyperlink Address:=EmailA dd, _
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_r eport.


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()
[CODE=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:\Documen ts and Settings\antoni o.jamasb\Deskto p\OCP Information\POL Report\POL1234" , False

'put your do loop here

'reference the email report sub to mail the file
If EMAIL_REPORT("a ntonio.jamasb@p ostoffice.co.uk ", "My Email Body", "My Subject Line", "C:\Documen ts and Settings\antoni o.jamasb\Deskto p\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:\Documen ts and Settings\antoni o.jamasb\Deskto p\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("N otes.NOTESSESSI ON")

strServer = objSession.GETE NVIRONMENTSTRIN G("mailserver ", True)
strMailFile = objSession.GETE NVIRONMENTSTRIN G("mailfile", True)

Set mobjDB = objSession.GETD ATABASE(strServ er, 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(st rSendTo As String, strBody As String, strSubject As String, Optional strFile As String) As Boolean
On Error GoTo EmailReport_Err

Dim objDoc As Object
Dim objRichTextAtta ch As Object
Dim objRichTextItem As Object
Dim objAttachment As Object

Const NOTES_RECIPIENT S = ""
Const NOTES_REPORTS_A DMIN_USER = ""
Const NOTES_MAIL_FILE = "C:\Documen ts and Settings\antoni o.jamasb\Applic ation Data\notes\mail \jamasba.nsf"

Set objDoc = mobjDB.CREATEDO CUMENT
Set objRichTextAtta ch = objDoc.CREATERI CHTEXTITEM("Fil e")
Set objRichTextItem = objDoc.CREATERI CHTEXTITEM(objD oc, "Body")

If strFile <> "" Then
Set objAttachment = objRichTextAtta ch.EMBEDOBJECT( 1454, "", strFile)
End If

'set up the email to be sent
objRichTextItem .AppendText strBody
objDoc.REPLACEI TEMVALUE "SendTo", strSendTo
objDoc.REPLACEI TEMVALUE "Subject", strSubject

objDoc.SaveMess ageOnSend = 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 objRichTextAtta ch = 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[/CODE]

[jambonjamasb]

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