HELP QUERY to return email add in REPORT when combo box is selected in a FORM

[jambonjamasb]

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

[Stewart Ross]

... the problem I have is I cannot work out how to tell the query to look at FIELD System_change on FORM data_form?

Hi. To refer to the control System_Change on form data_form you could use one of the following in your code:

[code=vb]Forms("Data_For m").Controls("S ystem_Change")
Forms![Data_Form]![System_Change][/code]

If you were referring to a control on a subform you could use
[code=vb]Forms![main form name]![subformname].Form![control name][/code]

The code tag qualifier for vb code is added within the brackets of the code tag, like this "["code=vb"]" (the quotes are to prevent this example itself being interpreted as code tags).

-Stewart

[jambonjamasb]

[QUOTE=Stewart Ross Inverness]Hi. To refer to the control System_Change on form data_form you could use one of the following in your code:

[code=vb]Forms("Data_For m").Controls("S ystem_Change")
Forms![Data_Form]![System_Change][/code]

Hi Stewart, so are you saying I do not have to run a report I could just add a section to the code that say dependant on what is on the forms return anyh matches from the table

[Stewart Ross]

[QUOTE=jambonjam asb]

Hi. To refer to the control System_Change on form data_form you could use one of the following in your code:

[code=vb]Forms("Data_For m").Controls("S ystem_Change")
Forms![Data_Form]![System_Change][/code]

Hi Stewart, so are you saying I do not have to run a report I could just add a section to the code that say dependant on what is on the forms return anyh matches from the table

Hi jambonjamasb. As long as the forms are open you can reference in VB code as shown - for example

[code=vb]Dim WhereClause as string
WhereClause = "WHERE [somedate] = #" & Forms![some form]![a date field] & "#"[/code]

Can't do it like that if the forms are closed; you would have to use a DLookup on the query or base table involved instead.

-Stewart