473,699 Members | 2,722 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

41 New Member

I have two tables:


Data table is is used to create a Form


In the form I have a field


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()
  3. Code: ( vb )
  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
  14. If OPEN_SESSION Then
  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
  19. 'put your do loop here
  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
  28. 'end your loop here
  30. 'delete the file
  31. 'Kill ("C:\Documents and Settings\antonio.jamasb\Desktop\OCP Information\POL Report\POL1234")
  33. 'call the close session sub to destroy the objects
  36. Else
  37. 'session not opened properly
  38. End If
  40. End Sub
  42. Public Function OPEN_SESSION() As Boolean
  44. Dim objSession As Object
  45. Dim strServer As String
  46. Dim strMailFile As String
  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")
  53. strServer = objSession.GETENVIRONMENTSTRING("mailserver", True)
  54. strMailFile = objSession.GETENVIRONMENTSTRING("mailfile", True)
  56. Set mobjDB = objSession.GETDATABASE(strServer, strMailFile)
  58. OPEN_SESSION = True
  59. Else
  60. MsgBox "Please start Lotus Notes and try again.", vbOKOnly, "Emails"
  61. OPEN_SESSION = False
  62. End If
  64. End Function
  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
  69. Dim objDoc As Object
  70. Dim objRichTextAttach As Object
  71. Dim objRichTextItem As Object
  72. Dim objAttachment As Object
  74. Const NOTES_RECIPIENTS = ""
  76. Const NOTES_MAIL_FILE = "C:\Documents and Settings\steven.birch\Application Data\notes\mail\birchs.nsf"
  78. Set objDoc = mobjDB.CREATEDOCUMENT
  79. Set objRichTextAttach = objDoc.CREATERICHTEXTITEM("File")
  80. Set objRichTextItem = objDoc.CREATERICHTEXTITEM(objDoc, "Body")
  82. If strFile <> "" Then
  83. Set objAttachment = objRichTextAttach.EMBEDOBJECT(1454, "", strFile)
  84. End If
  86. 'set up the email to be sent
  87. objRichTextItem.AppendText strBody
  88. objDoc.REPLACEITEMVALUE "SendTo", strSendTo
  89. objDoc.REPLACEITEMVALUE "Subject", strSubject
  91. objDoc.SAVEMESSAGEONSEND = True 'send E-mail
  92. objDoc.SEND False 'false for do not attach a form
  94. EMAIL_REPORT = True
  96. Exit_Here:
  97. Set objAttachment = Nothing
  98. Set objDoc = Nothing
  99. Set objRichTextAttach = Nothing
  100. Set objRichTextItem = Nothing
  101. Exit Function
  103. EmailReport_Err:
  104. EMAIL_REPORT = False
  105. Resume Exit_Here
  107. End Function
  109. Sub CLOSE_SESSION()
  111. Set mobjDB = Nothing
  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
3 3007
Stewart Ross
2,545 Recognized Expert Moderator Specialist
... 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).

Mar 15 '08 #2
41 New Member
[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
Stewart Ross
2,545 Recognized Expert Moderator Specialist
[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:

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.

Mar 16 '08 #4

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

Similar topics

by: mark | last post by:
I've been working on an Access 2000 database for a couple of weeks now. I took a course in access about a year ago, a crash course, and I learned a ton, but I didn't touch Access for the year since then so I forgot everything I learned : ( So this little project I envisioned has turned out to be much harder than I thought. But I think I'm close with it, so I want to see it through. What I wanted to create is a database that will track my...
by: EJC | last post by:
Hi Folks, I've been trying to build a query from a form of dialogue boxes I have created that holds search criteria for my main database. I have been able to get the query to retrieve the data selected in the relevant dialogue boxes and perform the query on these boxes. However my problem lies in the fact that if no choice is selected in the dialogue box I want the query to default (or get passed) a wildcard that will mean it will...
by: Steve Patrick | last post by:
Hi All You guys are my last hope, despite spending money on books and hours reading them I still can not achieve the results I need. I have designed a database in Access 2000 based on 1 table, all has gone very well with one exception. The table is based on applications made by potential customers looking to buy franchise rights to particular locations and as part of the process they are asked to list their preferred locations 1 to 4....
by: Richard Hollenbeck | last post by:
I tried to ask this question before on the 14th of January but I never got a reply. I'm still struggling with the problem. I'll try to rephrase the question: I have a crosstab query with rows of students and columns of activities and the data are the students' scores in each activity. No problem, almost. The problem is that there are five classes at the moment and will be more classes (or courses) in future semesters. I don't want...
by: TD | last post by:
I have this expression in the criteria section of a query: IIf(Forms!frmReports!cboProductID="<ALL PRODUCTS>",. Like "*",Forms!frmReports!cboProductID) (the syntax of the above expression may not be exact because I'm trying to write it from memory us now) I have a form with a combo box that I fill from a Union query that adds the phrase "<ALL PRODUCTS>" so that when you click on this combo box
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted ******************************************************** For this teeny job, please refer to: http://feeds.reddit.com/feed/8fu/?o=25
by: billelev | last post by:
I have a query that selects data based on a particular date. This date is selected from a combo box on a form. The data from the query is then used to generate a report. This report can be filtered using other features on the form where the date is set. I am able to modify the query using the date set on the form. SELECT AssetPrices.Symbol, Max(AssetPrices.Date) AS MaxOfDate FROM AssetPrices, PricingDate WHERE...
by: deaconj999 | last post by:
Hi, I have nearly finished my database and I would like to add a query that uses a combo box to get the results, not the usual paramater style input. I suppose it would need a form and a query and a combo box, but where to start !!!!!!!! Any kind soul out there that can give me an example of where to start..........?
by: google | last post by:
I am trying to automate the way reports are printed in an Access 2003 database - I have ~200 records (people) who require between 5 and 10 customized reports (depending on values within certain fields) - currently I print each report by selecting all employees and printing one report at a time - once all reports are printed, I manually collate the packages - I would like to be able to have a combo box (or other option) highlight the...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.