473,569 Members | 2,580 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
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
3 2996
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).

-Stewart
Mar 15 '08 #2
jambonjamasb
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.

-Stewart
Mar 16 '08 #4

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

Similar topics

2
2551
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...
6
2121
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...
5
2191
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...
15
4389
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...
2
1909
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...
0
5542
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
7
7644
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...
5
2016
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..........?
7
2051
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...
0
7938
Oralloy
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. ...
1
7694
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...
0
7993
tracyyun
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...
0
6317
agi2029
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...
1
5519
isladogs
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...
0
5245
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3679
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...
1
2128
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1236
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.