I am setting up a database at the moment. I do not have a lot of experience with Access but up to know everything was alright :).
I don't know if it is at all possible...but I would like to be able to find out who has a yes in a "yes or no" field and than that it shows me the email addresses of the people that have a yes there....does that make sense...I hope so.
Ok if that is possible, the second thing I want to be able to do is to email those persons that have a yes.
I have understood by now I will need codes and everything. Problem is I don't know anything about creating codes...so would be great if someone can explain that to me. And is it right I put those codes into a new module?
I hope this all makes sense and that someone will be able/willing to help me.
***Updated*** Search/Email Results has been solved....Working out some bugs and trying to add a Print Address Labels option to the search. The post at Date Nov. 5, 11:4x is the new post with updated info. Thanks! ***Updated***
I have read a ton in the last hour or so on this subject and gained a little insite, but since I have no Access programing experience I am still pretty lost. However, I have programing experience in other areas, so if it requires some macros, I shouldn't have too much trouble. /crossfingers
I have put together a database for the business I work for that contains around 3,000 contacts. I am trying to figure out a way to do a search that will search out specific contacts and email them a newsletter. Their email address is included in their contact information. So I assume I need a way to search for a specific criteria, grab their email address, and then email them with either an attached document, a prior written email, or have included the newsletter in the body of the code to add into the created email.
I work for a large company, whose contact directory in outlook is enourmous (thousands of employees). Outlook currently manages its contact directory very efficiently. its very easy to search and email individuals. In my access database I would like to be able to have that same functionality. To pull contacts from outlook via a search, without actually importing outlook contacts.
I am designing a database for asset tracking of Lab equipment. Lab equipment gets borrowed or relocated frequently and I would like to assign a name/phone/email quickly and without error, when I need to input a record of equipment movement.
I'm getting a Run-time error 2295: Unknown Message Recepients.
I've got a DB of about 2000 clients. In testing I did a test DB with 50 random clients. Using any search criteria, it would grab those clients and open a new email with their email addresses no problem. However when I do this same thing with my complete DB of 2000 clients, I get this run time error. I'm pretty confused here, and anxious. It looks like the program is working, I just need it to run with all my clients. This is the last thing I must conquer to be finished with this project.
Ok here is what was going on. I have an option group, I would select an option, type in my search criteria into a text box, and click an event button. The program would then search my database for that criteria and grab the emails of the individuals meeting that criteria. A message box would then pop up, listed all the emails it had grabbed I would click ok (my sendobject command is set to edit the email) and at this point instead of opening the email to edit the program would crash.
After scratching my head for several days I moved on and have come back to this problem again two weeks later, and within 15min I realized what was wrong. I guess it is good to walk away and come back fresh sometimes.
What is happening is that the program does not account for entries in my database that do NOT have an email.
Example, let us say we want to search a state...lets use the state of GA for example. If I have 5 entries from GA with email addresses and one entry from GA without an email address, it will crash the program.
My only guess is that the way the program is setup is that it is grabing whatever data is in the email location. It that location is blank, its grabing blank and crashing the program.
I need a way to discard the entries it searches that don't have email addresses or something...
Please help, this is the last item for this project.
Here is the current code:
Code:'Code by M. Walts'Important information! this code requires a reference to the Microsoft DAO object libraryOption Compare DatabaseOption ExplicitPrivate Sub cmdEmail_Click()'will hold the dynamic SQL queryDim strSQL As String'will hold the WHERE clause portion of our SQL queryDim strWHERE As String'will hold all the recipients of this messageDim strRecipients As String'the recordset we will use to get the emails of the records that match our criteriaDim rst As DAO.Recordset'if there is input in the search criteria, then we will run the query and send the e-mailIf txtSearch <> "" Then'if you have more buttons, just add mosr cases (the value of the radio button'= the Case number, so Value of the State radio button is 1, etc.)Select Case opgSearch.ValueCase 1strWHERE = "WHERE State = '" & txtSearch & "'"Case 2strWHERE = "WHERE PrayerSupport = '" & txtSearch & "'"Case 3strWHERE = "WHERE Denom = '" & txtSearch & "'"Case 4strWHERE = "WHERE PACTTrainer = '" & txtSearch & "'"Case 5strWHERE = "WHERE PACTPartner = '" & txtSearch & "'"Case 6strWHERE = "WHERE City = '" & txtSearch & "'"Case 7strWHERE = "WHERE Donor = '" & txtSearch & "'"Case 8strWHERE = "WHERE MailingList = '" & txtSearch & "'"Case 9strWHERE = "WHERE Conference = '" & txtSearch & "'" Case 10strWHERE = "WHERE YouthPastor = '" & txtSearch & "'"Case 11strWHERE = "WHERE PreviousCustomer = '" & txtSearch & "'"End SelectstrSQL = "SELECT EMail FROM tblUser " & strWHERE'run the query and get the results into the recordsetSet rst = CurrentDb.OpenRecordset(strSQL)'Loop through the recordset and add all the EMailsDo While Not rst.EOFstrRecipients = strRecipients & ";" & rst!EMailrst.MoveNextLoop'remove the first ; from the strRecipientsstrRecipients = Right(strRecipients, Len(strRecipients) - 1)MsgBox strRecipientsDoCmd.SendObject , , , , , strRecipients, "Email Subject", "Email Body", Truerst.CloseSet rst = NothingEnd IfEnd Sub'stops a ' entered in the field from breaking the queryPrivate Function SQLSafe(safeMe As String) As StringSQLSafe = Replace(safeMe, "'", "''")End Function
Ok, I've run into two serious problems in testing, and another question that I'm hoping you guys can help me solve. Below my questions is the code used for making the search query/email program.
Problem #1: In testing, this search program only is working for me for new data. Any data that I had in the database prior to implimenting the program will not move past the search stage. In other words, I will do a search, it will find the emails and bring them up in a box, I hit "OK" and it gets hung up and won't move on to opening up a new Email with the addresses implemented. However if I enter new data in the database, say with a weird name so its only bringing up the new entry and no old entry, it works fine and opens up the new email.
Problem #2: Items I would like to search like check boxes. Say I have a check box "Donor", this is listed in the database as a 1 or 0. I can't have my boss searching 1 or 0. Is there a way to change this to, say, being recorded in the DB as a Yes or No. That way he would check to search "Donor" and type Yes in the search box and this would search the DB "Donor" column for "Yes" and bring up the results.
New Question: My boss and I reviewed the form as it is so far yesterday. He was asking if I could add a Print Labels option in there. I'm wondering instead of adding another seperate search box and all that mess for a labels search print, can I rather add two check boxes, one labeled "Print Labels" and the other labeled "Email". What this would do is depending on the check box you selected it would either run the search and email like we have it, or clicking the other check box would run the search and print labels.
Quote: 'Author: Michael Walts, but use it as you like'Important information! this code requires a reference to the Microsoft DAO object library Option Compare Database Option Explicit
Private Sub cmdEmail_Click()
'will hold the dynamic SQL query Dim strSQL As String
'will hold the WHERE clause portion of our SQL query Dim strWHERE As String
'will hold all the recipients of this message Dim strRecipients As String
'the recordset we will use to get the emails of the records that match our criteria Dim rst As DAO.Recordset
'if there is input in the search criteria, then we will run the query and send the e-mail If txtSearch <> "" Then
'if you have more buttons, just add mosr cases (the value of the radio button '= the Case number, so Value of the State radio button is 1, etc.) Select Case opgSearch.Value
Case 1 strWHERE = "WHERE State = '" & txtSearch & "'"
Case 2 strWHERE = "WHERE City = '" & txtSearch & "'" End Select
strSQL = "SELECT EMail FROM tblUser " & strWHERE
'run the query and get the results into the recordset Set rst = CurrentDb.OpenRecordset(strSQL)
'Loop through the recordset and add all the EMails Do While Not rst.EOF strRecipients = strRecipients & ";" & rst!EMail rst.MoveNext Loop
'remove the first ; from the strRecipients strRecipients = Right(strRecipients, Len(strRecipients) - 1)
'stops a ' entered in the field from breaking the query Private Function SQLSafe(safeMe As String) As String SQLSafe = Replace(safeMe, "'", "''") End Function
VBA Code to go in the on double_click event of a name field in a Subform bound to a table. The subform is just a copy of a data table and within the subform view, When the field "employee name" which contains e.g. John is double clicked, I would like access to Lookup and get John's email in the employee table under field "Email" and launch outlook application and insert it into the To field.
I assume hyperlinking the field can also achieve this similar to what excel does but I am fast learning that what is standard functionality in excel is a whole another story in Access..
I have a query (Access 2007) that contains a field named "email" (which contains email addresses, of course). I want to email everyone in the query and they are all going to receive the same message. My email to them doesn't have to be personalized and I don't need to collect data from the recipients. I don't even need a reply to the email I send.
I have a form which has a button to email the data out in a standard email message.
Private Sub Command60_Click() Dim MyDb As dao.Database Dim rsEmail As dao.Recordset Dim sToName As String Dim sSubject As String Dim sMessageBody As String
[code]...
This works well enough, however, FIELDS 11 through to 16 contain the venue address. This is all we ll and good if every field of the venue address is populated. here are times when not all of the fields are populated, for instance, the address might only be 5 lines.I know I can do this using IIf statements on a report, but how can i achieve the same thing for the email.
I have a database that I can use to create a query, grouping companies by city. I then want to send a specific email I have created in outlook to all of the email addresses in the query.I do not need to include names.
I have made a query with the name "Confirmation" and it is setup like this:
Name trainee Email Training John John@mail.com Tr one Mary Mary@mail.com Tr two
I also made a button in a report with the title "Send Mail" now is my goal that if i press that button automatically multiple e-mail message's will be generated with data from people in that query. So if click on that "Send Email" button i want two different mails messages generated that will be send to John@mail.com and Mary@mail.com with in the mail body their data.
I am currently using vb code to send an email in Access on the click of a button. I want the database user to be able to enter the recipient in a text box [ToEmail] which is on form [GroupStockProfiler]. However, I'm unsure how to put this into my code. I currently have the following which doesn't work (unless I put a specific email after 'To'):
I want to automate an email to include the senders email addres with some text in the body of the email.
Code: Private Sub send_mail_Click() Dim olApp As Object Dim objMail As Object On Error Resume Next 'Keep going if there is an error Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open
I have an automated email system (Lotus Notes) in a Access database I have created and am looking for the code needed to add up to 3 c.c addresses into an email prior to sending automatically.
The code I have to send the email to the To: individual is all working but what code I need to add and where for the c.c addresses of which there could be 1, 2 or 3. The control for To address is EmailTo = CustCopyEmail The controls for the 3 c.c emails are cc_EmailAddress, cc_EmailAddress1 and cc_EmailAddress2.
Here's the code I have:
Private Sub SEND_CC_Click() ' Set up the objects required for Automation into Lotus Notes. Dim Maildb As Object 'The mail database. Dim Session As Object 'The Lotus Notes session. Dim MailDoc As Object 'The mail document itself. Dim UserName As String 'The current users Lotus Notes name.
why the code below is not functioning properly. When I type in an acronym in the textbox, it keeps saying there is an error "Run-time error '3345': Unkown or invalid field reference 'ABO'." I do have ABO in the field.
The dysfunctional code:
Code: Private Sub btnFind_Click() If (TxtFind & vbNullString) = vbNullString Then Exit Sub Dim rs As DAO.Recordset Set rs = Me.RecordsetClone rs.FindFirst "[Acronym] = " & TxtFind
[Code] .....
The red highlighted line is where the debugger leads me to. Something with identifying the field? I would like to enable the search procedure to search throughout the entire records rather than just a specific field. How may I write such a line or two?
I have a form that has a subform on it. The main form shows a category of furniture and has custom navigation buttons and a search text box for asset numbers and command button that runs the search. The subform shows the asset numbers associated with that furniture category, sometimes there is only one asset number, in other cases there could be 60. There is a scroll bar to scroll through the asset numbers when there are too many to see in the initial window.
The buttons all work as they should except when I search for an asset number that is part of a category that has too many asset numbers to show in the main window. When this happens the "previous" and "next" navigation buttons do not take you to the previous or next record. All of the other buttons on the form work though - you can go to the first, or the last record, and you can search for a new asset.This is the code for the search:
Code:
Private Sub cmdAssetSearch_Click() Dim rs As Object If IsNull(Me.TextAsset) Or Me.TextAsset = "" Then MsgBox "Please type in an asset number to search for.", vbOKOnly Me.TextAsset.SetFocus
[code]....
I've also attached a picture of what I mean when I say there are more asset numbers than what the window shows.
I currently have a database with a few search forms. I recently attempted to add a box on one of my search forms to search 3 cells of a record for a key word. What happens is when I go to search, say for P0442, it does not bring up all of the records that contain that keyword in the 3 cells I have outlined. If I step down the code in the OR boxes of the query, it seems to work better but then for some reason my other search criteria , (Tracking number etc) does not work. I will upload the database for reference. I am currently working on the Search(View Only) and that is where you will find the query to work on.
I'm having Table with some universities name and i want web link address for all universities. Take university from table1 in column1 and search on google page and return first link of the search page and save into column2...
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
1. I created a form with some search-fields which are related to a query. Then I added a Subform in which I put some more Search criteria (So that I can easily hide and unhide those additional searchfields). It sounds strange but is necessary ;-). Now I related those searchfields in the subform to the same query. When I run that query a window pops up that I should put in a value in all those searchfields which are in the subform. But I told Access that it should display all rows, if there is no value in those searchfields. Just as I did it with the Searchcriteria in the Main form. Do I have to do something special, when I have a query which is related to two Forms?
2. I want a searchfield to search in three different columns. Usually the value will just be found in one of those columns. As the Table I search is very long and has many searchfields and multiple of those will relate to more than one column, is there an easy way to do it in VBA? As I did it by using the "or" field when designing a query, but this seems very slow and unstable.
i need to create a system that can search data using keyword.so i want to create search button that i will create it at form. currently i have 1 table and in that table i have 10 fields which are project_name, doc_ref_no, doc_title, volume, book_no, author, doc_status, box_barcode, filling_location, doc_availability.
i have create 2 forms, one form for user to add new record and another one is to search record. the user can search by enter project name, doc title, volume and box barcode. for project name and volume, i use combo box and for doc title and box barcode i'm using text box.
i want to create search function that can detect from user keyword and even user did not fill in all the form, the system still can search the record.
I am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.
I have a search combo box to search for a field on my form. But it just goes to that particular record. The combo box is for client id and it has more than one record. How can I have it return just the records pertaining to the client id choosen in the combo box.
