I found the below link that looks great and works.
[URL] ....
Te only thing is that I don't have much control over it and would like to build something similar as we also need to work with access 2003 (still )Any way to find how the code works (if there is some of it hidden in access)?
I have a table called Contacts in Access:
ID Email
001 email1
002 email2
003 email3
004 email4
008 email5
010 email6
And I have a table called Data in Access:
ID Date Person Title Yes/No Action
001 20/4 Mr A Consultant
001 20/4 Mr B Supervisor
001 20/4 Mr C Consultant
004 20/4 Mr D Consultant
010 20/4 Mr E Consultant
010 20/4 Mr F Supervisor
What I'm looking for is coding that would start with the first ID number in CONTACTS and see if there is the corresponding ID in DATA. If there is, then I want it to create an Outlook email and have:
1.To field populated with the email address in the CONTACTS table
2.From and CC fields to be my email address
3.The subject line needs to read [ID] "Request" and then today's date backward (e.g. 20150420).
4.The body I need to put some generic wording and then a table with the records from the DATA table (first 5 fields plus the headings).
5. The format of the table etc isn't important as long as it is a table format
If it's created the email I want it to go back to the DATA table and put "Email Created" in the Action column for those IDs.
I don't want the email to automatically send; I only want it to create a draft (as this data needs to be manually confirmed before sending).
If for example it gets to checking in DATA table for ID 002 (and as seen there's none there), I want it to skip creating the email and move on to the next ID.
In the end I would expect to see 3 draft emails open in Outlook ready to be sent, looking something like the below (just 001 below)?
From... my email
To... Email1
CC... my email
Subject: 001 Request 20150420
action the below:
ID Date Person Title Yes/No
001 20/4 Mr A Consultant
001 20/4 Mr B Supervisor
001 20/4 Mr C Consultant
And the DATA table look like this (if only 001 email was created):
ID Date Person Title Yes/No Action
001 20/4 Mr A Consultant Email created
001 20/4 Mr B Supervisor Email created
001 20/4 Mr C Consultant Email created
004 20/4 Mr D Consultant
010 20/4 Mr E Consultant
010 20/4 Mr F Supervisor
I will need to add more data to the CONTACTS table over time, also the DATA table will be different each day so I need the coding to accomodate for both of these possibilities.
I have some code that takes from a query, an email address and sends out an email.Due the security warning that happens for each individual email, i want to create ONE email for all email address in the query and populate them to the BCC section of the sendobject.Here is the code that I currently use
Code:
Private Sub cmdSendEmail_Click()
Dim MyDB As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
[code]...
I want to populate the BCC field with multiple emails addresses seperated by ":" and send just one email.
how to add security features into My Access database. i.e create users with permissions/login.
View 1 Replies View RelatedI am trying to work with Access 2010 to do some pretty simple stuff. I have two tables, one with around 500k rows, the other with around 150k rows. There aren't too many columns, less or around 20 for each table.
I am just trying to do a simple inner join and then create a table but every single time I do it gets about 20% done on the status bar in the bottom right and just hangs. The query itself takes no time to run but it can't create it as a table. Same thing happens if I try to export the query.
In an Access 2007 module, is there a way of sending an email to a list of people stored as a list in a table in the database, rather than having to put all the names into the function?
I have a function I'm using to generate and send out an email to certain people, but the list is constantly growing so I'm looking for a better way to manage it! The main issue is having to kick users out of the database every time I need to update the recipients list... because it's stored in the code.
If I create a simple table containing all the names, how could I then ask it to use that instead?
This is the function I'm using currently - found on here and adapted to my own purposes
Syntax to use for the function: SendNotesMail "recipients", "Body Message", "Additional Text", "Subject"
Code:
Public Function SendNotesMail(strSendTo As String, strBody As String, strExtraText As String, strSubject As String)
'This public sub will send a mail and attachment if neccessary to the recipient including the body text and additional comments from the Active record
DoCmd.OutputTo acOutputReport, "REP09emailnotification", acFormatRTF, "x: endersgroup tendering databaseTenderUpdate.rtf", False
Dim Subject As String
Dim Attachment As String
[Code] ....
So I guess my question is: how do I get my recipients from a table into the strsendto?
I would like to create a multi-select list box for all the records in an access 2010 form.
View 2 Replies View RelatedI am trying to dynamically change the IN sql from within VBA using parameters. for some reason i have no luck, no errors shows up, but it's actually not picking up the criteria.
Code:
THE SQL IN STATEMENT
In (select RemID from [ReminderAssignees] Where RemDate between [Date1] and [Date2] And [sDismiss] )));
Code:
THE VBA CODE
qdf.Parameters("date1") = Date1
qdf.Parameters("date2") = Date2
Select Case iDismissed
Case 0, 1
qdf.Parameters("sDismiss") = "1=1"
Case 2
qdf.Parameters("sDismiss") = "(not isdate(Dismiss) or Dismiss > #" & Now & "#)"
Case 3
qdf.Parameters("sDismiss") = " isdate(Dismiss) and Dismiss < #" & Now & "#"
End Select
qdf.Execute
My manager wants to be able to implement a kind of punch card system for managing staff time. I'm thinking we could make a database that links to a folder in Outlook. Staff will be able to send an email to the inbox saying that they are, for example, going on a break at 10:50, and then another at 11:05 saying they have returned and are back to work.
This would probably add up to around 500 emails per day with the number of staff we have, so I don't want to have to keep all the emails to be able to add up break times etc.
I'll be extracting the timestamps, name, break description etc from the email and storing them in another table. What I would like to know is whether Access would then be able to delete the email from the inbox (or modify it in some way to reflect it has been accounted for), or whether this would have to be a manual task?
I have the following code which successfully opens the dialog box with filters however when I add a function with an Excel import, the filters do not work at all (no files are shown even though they exist in the directory that's opened within the dialog box). If I manually type in the filter (i.e. *.x) it still shows the files.The function at the bottom was provided by cheekybuddha from another form, I'd be lost on getting the Excel file imported into Access.Here's the VBA code (the Function is also included at bottom):
Code:
Private Sub Command0_Click()
On Error GoTo PROC_ERR
Dim strpathtofile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String, strInitialDirectory As String
Dim blnHasFieldNames As Boolean
[code]...
I have an application in Access 2003. It uses VBA to open an Excel file. The file has one tab for charts and one for data. The program counts how many charts needs to be created, one per project. It then will place 4 charts per tab, creating as many chart tabs as necessary, keep one data tab. The program then writes the data and links the data to the corresponding chart. It will also write legends, and scale the charts. This all works fine under Access 2003/Excel 2003.The error I get is "Application-defined error or object defined error".The line that gives me the error is: Set chCopy = xlsheet.ChartObjects(idx).I am using the Multi-Chart option frmChartType = 1
Now I'm moving to the Office 2010 world. It is not working. Below is the code I run to do the magic. I guess something is happening in Excel 2010 that is different from 2003.I found when I comment out the error line, and other lines dependent on chCopy it creates the charts, but all the charts are pictures and not charts!!
Code:
Public Sub GenerateChart()
' Generate an Excel chart
Dim db As Database
Dim rst As DAO.Recordset
Dim xlapp As Object
[code]...
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]....
Here is my project: For a duty plan the employees are assigned to positions in a certain area.
For example:
Name. Position. Description. Map
Brown. 1. backdoor. 1
Miller. 3. frontdoor. 1
Norman. 4. left wing. 2
Hoover. 6. right door. 2
....
Now I want to show the positions within circles on the maps, with the names within dynamically, as the employees change every week.
The coordinates of the positions do not change, but not every position is needed in every duty plan.
So, my idea is to draw all possible shapes on all possible maps and make them visible or not depending on the assignement.
The duty plan is my table in access and looping through all records I would like to fill the needed shapes.
All maps are on slides (40 slides and about 400 positions) and should be filled accordingly.
I can open my presentation, although I only could do it by late binding ( a reference to ppt 14 obj. Lib fails with an error!! - why??) but I cannot any solution how to loop through the records and do the job.
I want to enable a few fields when i click on the Add button on my form.I have change the onclick to Event Procedure to be able to add the following code
Code:
Category_Desc.Enabled = True
My Category_Desc field get enable as expected but the Add button no long work
Code:
Private Sub add_Click()
Category_Desc.Enabled = True
Me.Refresh
End Sub
The following code browse from one form to another and shows the record details I select in the first form which is what I need, however, the problem is it only shows that record and will not allow me go to next or previous records when pressing NEXT or PREVIOUS by saying this is the First Record or this is the Last Record.
Anyway to modify it to allow me navigate to other records.
Dim txtJobNumber As Integer
Private Sub Job_Number_DblClick(Cancel As Integer)
'Store Job_Number in txtJobNumber variable and display
message to show value.
txtJobNumber = [Job_Number]
[Code] ....
I have a database that, I would like to add a button that performs a active directory lookup. I would like it to check a username with Active Directory, and auto populate a few fields.
First Name
Last Name
Manager
Department
This is my first database and I have very little exp using VBA.
how to make this two fields in my form to say Y or N if the field is Not Null. Like if the field is not null = Y Else = N for the two fields. I have a picture to show what i'm talking about.
View 2 Replies View RelatedI have two tables linked via Project ID as shown in the attached file. I need Module to concatenate Project sub types against Project Types.
View 3 Replies View RelatedI opened a 2007 Access db in Access 2010. This DB worked perfectly in Access 2007, but when I opened it in 2010 my recordset.recordcount no longer works.
This is what I have:
rsGetInst was previously defined
Dim intInstCount as integer
Dim rsGetInst As New ADODB.Recordset
rsGetInst.CursorLocation = adUseClient
rsGetInst.CursorType = adOpenDynamic
rsGetInst.LockType = adLockOptimistic
rsGetInst.open "Select * from tblInstruction where CustID = " & intCustomerID
intInstCount = rsGetInst.recordcount
At this point a get a "type Mismatch" error, and it is happening in all my recordsets recordcount.
Is there a command or reference that needs to be change when using Access 2010. When I compared the references the only difference is that in 2007 we reference Microsoft Access 12.0 Object Library and in 2010 is Microsoft Access 14.0 Object Library.
I have always had solid read access to the Windows registry running on Windows XP with Access 2007. I am utilizing code from here in a separate VBA Module to obtain access to the Windows registry:
"Change registry settings in VBA"
[URL]
On Windows 7 x64 / Access 2010 x86, I am seeing a 0 value in lngKeyHandle. Also, m_lngRetVal has a value of 2 after the RegOpenKey LOC.
Code:
' --------------------------------------------------------------
' Query the key path
' --------------------------------------------------------------
m_lngRetVal = RegOpenKey(lngRootKey, strRegKeyPath, lngKeyHandle)
' --------------------------------------------------------------
' If no key handle was found then there is no key. Leave here.
' --------------------------------------------------------------
If lngKeyHandle = 0 Then
regQuery_A_Key = vbNullString
m_lngRetVal = RegCloseKey(lngKeyHandle) ' always close the handle
Exit Function
End If
I have verified that I have the key successfully in the registry... installed via the .reg file that works on Windows XP / Access 2007 systems.
I have a split db with tables in the back end and my forms, reports, code etc. in the front end.
I encrypted the back end with a password. That worked fine.
I deleted and relinked my tables to the encrypted back end. That worked fine as well.
I have a function that will disable the shift key bypass. If I run that in my front end db then I can't save it as an accde because I can't get to the HOME screen. If I save it as an accde first then I can't run my 'disable shift key bypass' function because I can't get to the modules!
How can I secure a split Access 2010 database so that the user cannot execute shift bypass on the front end or make any changes to the code?
Previously in Excel / Access 97 / 2003 I made a little tool that would search for files in specified locations - I modified the code so that it would search for files based on what was entered in cells.
I believe a lot of the old code doesn't work in Access 2010 and it seems like a lot of the old file searching code I used to use no longer works.
Is there code available that searches a directory and lists results?
In Access 2010 is it possible to have a tblKits with a column on it Reactions 8. Then on a form a field Used. When thee user inputs 2 into the Used field can the Reactions on in the tblKits be updated to 6 automatically?
View 1 Replies View RelatedI am trying to export a table from Access 2010 into an existing multiple tab excel 2010 spreadsheet.I want it to overwrite the "data staging" tab each time.I have it adding the tab into the existing spreadsheet but it names it "data_staging" however if I run this a second time I get excel found unreadable content in 'data staging' Do you want to recover the contents of this workbook? if you trust the source of this workbook click yes.
Code I am using
'export to existing spreadsheet data staging
Private Sub Command5_Click()
DoCmd.TransferSpreadsheet acExport, 10, "Phx Data Staging", "F:My DocumentsWorkSGN est est data staging.xlsx", False, "data staging"
MsgBox ("Completed")
I am using Access 2010.Most of the time a tenant pays his exact rent. When that happens, I currently type in the payment (taken form the Rent field) and date of payment (current date) into a form. The date should not change.
I would like to place a checkbox into the form. If checked, the rent would be entered into the payment field and payment date (current date) would be entered automatically. Otherwise, I would just enter in the payment and date manually.
FIELDS
Rent
Payment
PaymentDate
on form chkPayment
Using Access 2010: I have a query with four fields: ORG_NAME, PERS_NAME_LAST, CountOfORG_NAME, and BdMbrCount. There are a couple hundred companies in the database with 1-7 people associated with each company. I need to number each person so that they have a number, 1-7 in the MemberCount field of my query.
I have my query connected to VBA code.
I have experimented with code that I have found on the forum, just to see if I could get something to work and I am getting “Undefined function ‘BdMbrCount’ in expression. I am trying to pass [ORG_NAME],[PERS_NAME_LAST] to my function and assign the value of BdMbrCount to a new field in my query, BdMbrCount.
Code:
Function BdMbrNumber()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourBdMbrsRRecognizedQry")
'Set rst = CurrentDb.OpenRecordset("SELECT [ORG_Name],[PERS_NAME_LAST],[CountofORG_NAME], [BdMbrCount] FROM YourBdMbrsRRecognizedQry")
Dim ORG_NAME As String
[code]....