By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 828 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

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

P: 41
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.

Expand|Select|Wrap|Line Numbers
  1. vb()
  2.  
  3. Code: ( vb )
  4.  
  5. Public module
  6. Public Sub SEND_EMAILS()
  7. 'use this one
  8. 'open the session with the lotus notes server
  9. 'this sub will output a report as a file
  10. 'attach the file and add the predetermined subject and body
  11. 'delete the file that was output
  12. 'close the session with the server
  13.  
  14. If OPEN_SESSION Then
  15.  
  16. 'output report to text file on C:\
  17. DoCmd.OutputTo acOutputReport, "POL1234", acFormat, "C:\Documents and Settings\steven.birch\Desktop\OCP Information\POL Report\fred.doc", False
  18.  
  19. 'put your do loop here
  20.  
  21. 'reference the email report sub to mail the file
  22. 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
  23. MsgBox "Message Sent"
  24. Else
  25. 'error in email module
  26. End If
  27.  
  28. 'end your loop here
  29.  
  30. 'delete the file
  31. 'Kill ("C:\Documents and Settings\antonio.jamasb\Desktop\OCP Information\POL Report\POL1234")
  32.  
  33. 'call the close session sub to destroy the objects
  34. CLOSE_SESSION
  35.  
  36. Else
  37. 'session not opened properly
  38. End If
  39.  
  40. End Sub
  41.  
  42. Public Function OPEN_SESSION() As Boolean
  43.  
  44. Dim objSession As Object
  45. Dim strServer As String
  46. Dim strMailFile As String
  47.  
  48. 'lotus notes must be open for module to work correctly
  49. If MsgBox("Do you have lotus notes running?", vbCritical + vbYesNo, "Warning!") = vbYes Then
  50. 'this code must be left out of the loop so that only one session is started
  51. Set objSession = CreateObject("Notes.NOTESSESSION")
  52.  
  53. strServer = objSession.GETENVIRONMENTSTRING("mailserver", True)
  54. strMailFile = objSession.GETENVIRONMENTSTRING("mailfile", True)
  55.  
  56. Set mobjDB = objSession.GETDATABASE(strServer, strMailFile)
  57.  
  58. OPEN_SESSION = True
  59. Else
  60. MsgBox "Please start Lotus Notes and try again.", vbOKOnly, "Emails"
  61. OPEN_SESSION = False
  62. End If
  63.  
  64. End Function
  65.  
  66. Public Function EMAIL_REPORT(strSendTo As String, strBody As String, strSubject As String, Optional strFile As String) As Boolean
  67. On Error GoTo EmailReport_Err
  68.  
  69. Dim objDoc As Object
  70. Dim objRichTextAttach As Object
  71. Dim objRichTextItem As Object
  72. Dim objAttachment As Object
  73.  
  74. Const NOTES_RECIPIENTS = ""
  75. Const NOTES_REPORTS_ADMIN_USER = ""
  76. Const NOTES_MAIL_FILE = "C:\Documents and Settings\steven.birch\Application Data\notes\mail\birchs.nsf"
  77.  
  78. Set objDoc = mobjDB.CREATEDOCUMENT
  79. Set objRichTextAttach = objDoc.CREATERICHTEXTITEM("File")
  80. Set objRichTextItem = objDoc.CREATERICHTEXTITEM(objDoc, "Body")
  81.  
  82. If strFile <> "" Then
  83. Set objAttachment = objRichTextAttach.EMBEDOBJECT(1454, "", strFile)
  84. End If
  85.  
  86. 'set up the email to be sent
  87. objRichTextItem.AppendText strBody
  88. objDoc.REPLACEITEMVALUE "SendTo", strSendTo
  89. objDoc.REPLACEITEMVALUE "Subject", strSubject
  90.  
  91. objDoc.SAVEMESSAGEONSEND = True 'send E-mail
  92. objDoc.SEND False 'false for do not attach a form
  93.  
  94. EMAIL_REPORT = True
  95.  
  96. Exit_Here:
  97. Set objAttachment = Nothing
  98. Set objDoc = Nothing
  99. Set objRichTextAttach = Nothing
  100. Set objRichTextItem = Nothing
  101. Exit Function
  102.  
  103. EmailReport_Err:
  104. EMAIL_REPORT = False
  105. Resume Exit_Here
  106.  
  107. End Function
  108.  
  109. Sub CLOSE_SESSION()
  110.  
  111. Set mobjDB = Nothing
  112.  
  113. End Sub
  114. Public module
  115. 'use this
  116. 'recipient as string, bodytext as string,saveit as Boolean)
  117. 'This public sub will send a mail and attachment if neccessary to the
  118. 'recipient including the body text.
  119. 'Requires that notes client is installed on the system.
  120. Public Sub SendNotesMail()
  121. 'Set up the objects required for Automation into lotus notes
  122.     Dim Maildb As Object 'The mail database
  123.     Dim UserName As String 'The current users notes name
  124.     Dim MailDbName As String 'THe current users notes mail database name
  125.     Dim MailDoc As Object 'The mail document itself
  126.     Dim AttachME As Object 'The attachment richtextfile object
  127.     Dim Session As Object 'The notes session
  128.     Dim EmbedObj As Object 'The embedded object (Attachment)
  129.     DoCmd.OutputTo acOutputReport, "POL1234", acFormat, "C:\Documents and Settings\steven.birch\Desktop\OCP Information\POL Report\fred.doc", False
  130.     'Start a session to notes
  131.     Set Session = CreateObject("Notes.NotesSession")
  132.     'Next line only works with 5.x and above. Replace password with your password
  133.     'Session.Initialize ("password")
  134.     'Get the sessions username and then calculate the mail file name
  135.     'You may or may not need this as for MailDBname with some systems you
  136.     'can pass an empty string or using above password you can use other mailboxes.
  137.     UserName = Session.UserName
  138.     MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
  139.     'Open the mail database in notes
  140.     Set Maildb = Session.GETDATABASE("", MailDbName)
  141.      If Maildb.ISOPEN = True Then
  142.           'Already open for mail
  143.      Else
  144.          Maildb.OPENMAIL
  145.      End If
  146.     'Set up the new mail document
  147.     Set MailDoc = Maildb.CREATEDOCUMENT
  148.     MailDoc.Form = "Memo"
  149.     MailDoc.sendto = "steven.birch@postoffice.co.uk"
  150.     MailDoc.Subject = "test"
  151.     MailDoc.Body = "hello is there anybody out there"
  152.     MailDoc.SAVEMESSAGEONSEND = SaveIt
  153.     'Set up the embedded object and attachment and attach it
  154.     Attachment = "C:\Documents and Settings\steven.birch\Desktop\OCP Information\POL Report\fred.doc"
  155.     If Attachment <> "" Then
  156.         Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
  157.         Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
  158.         MailDoc.CREATERICHTEXTITEM ("Attachment")
  159.     End If
  160.     'Send the document
  161.     MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
  162.     MailDoc.SEND 0, Recipient
  163.     'Clean Up
  164.     Set Maildb = Nothing
  165.     Set MailDoc = Nothing
  166.     Set AttachME = Nothing
  167.     Set Session = Nothing
  168.     Set EmbedObj = Nothing
  169. 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
Mar 14 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
... 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:

Expand|Select|Wrap|Line Numbers
  1. Forms("Data_Form").Controls("System_Change")
  2. Forms![Data_Form]![System_Change]
If you were referring to a control on a subform you could use
Expand|Select|Wrap|Line Numbers
  1. Forms![main form name]![subformname].Form![control name]
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
Mar 15 '08 #2

P: 41
[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:

Expand|Select|Wrap|Line Numbers
  1. Forms("Data_Form").Controls("System_Change")
  2. Forms![Data_Form]![System_Change]
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
Mar 16 '08 #3

Expert Mod 2.5K+
P: 2,545
[quote=jambonjamasb]
Hi. To refer to the control System_Change on form data_form you could use one of the following in your code:

Expand|Select|Wrap|Line Numbers
  1. Forms("Data_Form").Controls("System_Change")
  2. Forms![Data_Form]![System_Change]
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

Expand|Select|Wrap|Line Numbers
  1. Dim WhereClause as string
  2. WhereClause = "WHERE [somedate] = #" & Forms![some form]![a date field] & "#"
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
Mar 16 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.