Modules & VBA :: Loop On Recordset - Sending Email Out With All Needed Info
Dec 9, 2014
I have this code here that sends an email out with all the needed info. The problem i ran into was that for my field "item" it is in a table called "test" and there can be more than one record in there. I just can't get it to loop and show me all of the records. Right now it shows me only the first record. Also it is showing the name of the table before the result so i am not sure what is wrong there...
Code:
Private Sub Command4_Click()
On Error GoTo Err_SendInfo_Click
Dim varTo As Variant
Dim varCC As Variant
Dim stSubject As String
[Code] ....
View Replies
ADVERTISEMENT
Jan 19, 2015
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'):
Private Sub Command414_Click()
Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2
Const cdoAnonymous = 0
[code]...
View 1 Replies
View Related
Jun 4, 2014
Is there a way of looping through a non update able continuous subform, and using information from each line.
Which creates a new record in another table? so if there are 3 records in the subform it creates 3 new records in the other table and so on?
View 3 Replies
View Related
Jun 16, 2014
Im trying to create a record set that compares a quantity value in the recordset to a Value in a temporary table that holds the most recent remaining quantity. I have the following code in the after update of a text box, but it does not trigger. I want it to run after a value is entered into the text box. Is the code wrong or the location im putting it incorrect?
Code:
Private Sub txtQty_AfterUpdate()
Dim rs As DAO.Recordset
DoCmd.OpenQuery "qryQuantitySoFar"
Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
[Code] ....
View 11 Replies
View Related
Aug 2, 2013
I have this code (below) that loops through a recordset and sends appointments. It executes the queries correctly and sends all appointments in the table, but sends them only to the contacts listed in the first record of the query. How do I get it to loop the contact details?
Code:
Private Sub SchedFollowUp()
Dim rsFollow As DAO.Recordset
Set rsFollow = CurrentDb.OpenRecordset("SELECT * FROM Follow_Up WHERE HR_Approved = True AND Added_to_Outlook = False AND Cancelled = False;", dbOpenDynaset)
Dim rsEmployee As DAO.Recordset
[Code] ....
View 10 Replies
View Related
May 8, 2014
I am attempting to use 2 fields from a query to supply the Top and Left Properties of a Collection of Rectangle Controls on my form. The purpose of this is to display the locations on a map of "Spots" in a haunted house. The query that I am using shows the spots that have been pre-tagged with the location of where they belong on the map (currently the query has only 24 tagged spots). On the actual form I have rectangle controls (control type acRectangle) with their visible property set to False by default, named box1 through box25 (there will be more eventually, as I am just working with this test group).
I started with the following code, yet it stops after (correctly) placing the first spot on the map (please see the attached jpg):
Code:
Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
Dim db As DAO.Database
Dim rst As DAO.Recordset
[Code]....
I'm sure I need to have 'Loop' in there somewhere, but I am not sure exactly where to place it, or if another line is also needed.
View 10 Replies
View Related
Jun 9, 2014
Is there a way of looping through a form record set, while adding new records to a different form record set? using some data from the 1st record set in the new records?
View 8 Replies
View Related
Aug 27, 2014
So I have a table "zztblArticles." Some fields should go to "tblArticles" and values in Tag_ID should go to a lookup table "tblTag" and a junction table "tblArticles_Tags." I'll explain the code I've written below:
Code:
Private Sub cmdSubmit_Click()
Dim db As Database
Dim strINSERT As String
Dim strVALUES As String
Dim rszztblArticles As DAO.Recordset
[Code] ....
The first loop through works fine, I get the records uploaded to all tables. The second loop through fails at
Code:
intArticleID = DLookup("ID", "tblArticles", "Sourcing_Date = " & !Sourcing_Date)
Because it can't decide which ID value to use. This is because the value has been duplicated in tblArticles after the code acts on the same record again. It has completely failed to move to the next record in the recordset, despite the .MoveNext before the Loop!
View 12 Replies
View Related
Jul 22, 2014
In my database, I have a continuous form with a Name, a Date and a Yes/No field.
When the form opens, I want to look at the date of every record on the form and show a message box if it is before the current day.
The code I have is this;
Private Sub Form_Load()
With Me.RecordsetClone
While Not .EOF
If Me.Date1 < Date Then
MsgBox "" & Me.Person & ""
End If
If Not .EOF Then .MoveNext
Wend
End With
End Sub
However, it loops just the first record the amount of times there is of records (i.e., it will only show the first person's name in the message box, and will show 3 times if there are 3 records).
View 7 Replies
View Related
Jun 26, 2015
I have 2 identical tables of asset information. Table 1 has 251 records while table 2 has 84 records. All 84 records are in table 1 right now. My end goal is to be able to click a button, have vba script run, and table 2 updates table 1 with any changes from different fields. I know there are merging options with query and what not but the exact structure of my tables doesnt play well with it.
Right now, my code is able to loop through the values of each computername and display it. For some reason, when it gets to the 130th record of table one, the loop skips it and returns the 131st record, it stays one ahead for the rest of the loop and then prints the 130th record finally.
Since I am trying to compare field values, this sudden shift throws everything off. why it skips?
Code:
Option Compare Database
Private Sub UpdateAssetsBTN_Click()
On Error GoTo Err_Proc
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
[code]....
View 2 Replies
View Related
Aug 17, 2015
managed to get some code up and running but when trying to enhance it I have hit a rut.
I have a function that is looking up a query called Optimisation - Auto Optimise with operational data in it. I only need 2 key fields; consolcode and volume. There are 106 records with different consolcodes each with different volume. e.g consolcode: Chittagong to Rotterdam201452 (Chittagong to Rotterdam by year "2014" by week "52") and volume 161 (cbm)
I then run a code that allocates the volume into specific sea freight containers and returns the values into a different output table.
The allocation code works fine but when I run the loop function for the recordset rsttradelane it runs for the correct amount of records (106) but always returns the first record of Chittagong to Rotterdam201452 and 161 cbm and not the other 105 consolcodes with the different cbm. Giving me an output table with Chittagong to Rotterdam201452 and 161 cbm repeated 105 times!!
Code:
Set rsttradelane = dbsEPIC.OpenRecordset("Optimisation - Auto Optimise")
consollane = rsttradelane!consolcode
ConsolVol = rsttradelane!Volume
Do Until rsttradelane.EOF
'Optimisation code' then
Code:
rsttradelane.MoveNext
Loop
rsttradelane.Close
How do I ensure that each consolcode and its associated cbm is recognised individually and flushed through the optimisation code?
View 3 Replies
View Related
Mar 13, 2014
I found this code and have substituted parameters to suit my own needs however the loop is not working. Only the first record in my recordset (which is a test recordset of only 3 records) is being updated.
Also, for testing only, the edit or update being applied is trivial: Description = "WHITE RESIN". If i can get the loop to work I want to substitute higher functionality to the module.
Private Sub Update_Click()
Dim dbs As DAO.Database
Dim rsQuery As DAO.Recordset
Set dbs = CurrentDb
Set rsQuery = dbs.OpenRecordset("qryRmResin", dbOpenDynaset)
[Code] .....
View 14 Replies
View Related
Jul 8, 2015
I have some code that loops the clone recordset of my subform and generates a email with attachments. I have mainform and continuous subform within the subform I have field called address this holds paths to files and another field called send and this is a yes/no field
Now what I'm trying to do is loop through the subform if send field is true then attach file from the address path but if send field is false then do not attach file
Code:
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim olAttach As Outlook.Attachment
Dim rstAttach As DAO.Recordset
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
[Code] ....
View 14 Replies
View Related
Jan 12, 2015
I'm working on a database for work that email's the current record after the form is completed. I have a attachment field in the table called Photo and some record's have a photo attached and other's don't. The code below works great with records that have a photo attached, but I get a path not found if I try to email a record that does not have a photo. I know that I need to put some form of code to check the photo field for a attachment, but I'm having a brain fade as to just what the code is.
Code:
Private Sub eMail_Report_Click()
Dim oFilesys, oTxtStream As Object
Dim txtHTML As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
[Code] ....
View 5 Replies
View Related
Dec 16, 2014
i am trying to send a email with a pdf-report attached based on a report (rap_factuur_klant_pdf).I am using MS Access 2007 and Outlook 2007..
Code:
Dim sAddr As String, sSubj As String, sFor As String
Again:
sAddr = InputBox("E-mail address:")
sSubj = "Report"
sFor = Left(sAddr, InStr(1, sAddr, "@") - 1)
DoCmd.SendObject acSendReport, rap_factuur_klant_pdf, acFormatPDF, sAddr, , , sSubj, "BLA BLA BLA"
DoEvents
View 5 Replies
View Related
Jun 25, 2013
I use a macro (SendObject), which works, but it requires Us, or someone to go into Outlook to click on send.I'm new to vba, do I have to code something on outlook to send automatically?
View 6 Replies
View Related
Nov 13, 2013
I have this code below in order to send query through outlook. How can I display the mail and not directly sending it?
Private Sub Command64_Click()
Dim message As String
Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String
[Code] .....
View 5 Replies
View Related
Nov 26, 2013
I am a College Instructor and I would like to make a Database that will regularly email me a list of Students who are approaching the mid-point of their Program.Is it possible to program Access to email a list with Student Names, Registration Numbers, etc, etc. by a given date?
As of right now I have a Database with all of the students' pertinent information but I have to manually comb through the Database each week to see which students will require a Mid-Term interview that week.
View 7 Replies
View Related
Aug 16, 2014
I have this VBA code:
Option Compare Database
Option Explicit
Private Const URL_CDOCONFIG As String = ""
Public Function SendEmail(ByVal sTo As String, ByVal sFrom As String, _
Optional ByVal sCC As String = "", Optional ByVal sBCC As String = "", _
Optional ByVal sSubject As String = "", Optional ByVal sBody As String = "", _
[Code] ....
It is being called with the following macro:
SendEmail("gregory.west@dbwsys.mb.ca","vba@dbwsys. mb.ca","","","Subject line","Body of eMail here")
But I get this error message.
The "SendUsing" configuration value is invalid.
View 1 Replies
View Related
Mar 12, 2014
How would I set up an Outlook email using a list of recipients from an Access table? I'd like to be able to run this from Access 2007 and not Outlook. However, once the email is set up, I'd like to be able to go into the email and add subject, body, etc. before sending the email.
View 5 Replies
View Related
Aug 26, 2013
I am trying to send an email from a form in Access 2013 using fields on the form as part of the email body and I can't seem to find code to just do this.
I don't need to loop through tables as the send email address etc will be on the form together with appointment times etc ...
View 2 Replies
View Related
Apr 24, 2015
I have code that allows me to drag and drop an email to an access field, which then stores that email on the local / network folder. Another field with that email's file path is also displayed.
My big question is: knowing the email's file path, how can I have the system open those (network/folder) saved emails as a reply (not automatic send)?
I envision this as being triggered by a _Change event; if a drop-down changes or is selected, then the system reads the location in the 'Email Location' field, and opens the reply to that email.
View 3 Replies
View Related
Feb 7, 2014
I have been able to create code to send email for my ticket system but now have a need to add an additional recipient. I would prefer this recipient to be add in the CC field. This email address will come from its own table. I have tried to figure this out myself but I can't get anything to work for me . I am fairly new to VBA and all of my experience is just through trial and error (mostly error). I have attached my original code below ....
Private Sub cmdMailTicket_Click()
On Error GoTo Err_cmdMailTicket_Click
Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
[Code] ....
View 1 Replies
View Related
Oct 1, 2013
I would like to update the following code I have been using to send emails, from Access 2010 via Outlook 2010, to include my signature block with hard returns in between each line and a few above the signature.
Lets say the signature would be:
My Name
My Company Name
My phone number
My Reference
I tried next to: stMessage = Me![EmailMessage] &"My Name" & Chr(10) & Chr(13) & "My Company Name"
etc.
It does not work..
Here is the coding :
Code:
Private Sub SendEmail_Click()
On Error GoTo ProcErr
Dim stLinkCriteria As String
[Code]....
View 5 Replies
View Related
Apr 16, 2014
Code:
Public Function OPEN_SESSION() As Boolean
Dim objSession As Object
Dim strServer As String
Dim strMailFile As String
[code]....
but for some reason my form is not updating after sending the email.
View 1 Replies
View Related
Aug 25, 2014
I would like to set a flag when a report gets emailed.
View 14 Replies
View Related
