Access 2010 - Email Automation (if Not NULL)
Dec 18, 2014
I'm attempting to use Access to automate reporting emails and I'm trying to identify the best way to identify if the report is NULL (blank).
VBA:
(strReportName is set to the report. Employees is set to the Employee Name. Email is set to the employees email address. [Requested By] is the column in the report that is used as a filter. The code is functional but also emails reports that are blank. I'm trying to just send the ones that have data)
...
strOutputPath = CurrentProject.Path & "" & strReportName & "'" & Employees & "'" & ".pdf"
[Code] .....
View Replies
ADVERTISEMENT
Nov 26, 2013
I have a MS Access 2010 application when the User opens form CONTACTS Form
◦User finds single record to be used CONTACTID is identifier to be used for selection
◦User clicks button to open form frm_MAIL_MERGE
◦Frm_MAIL_MERGE has a drop down combo box that reads the folder location where the application resides and displays all .DOTM files (that is all template files) and one more combo box which contains the CONTACTID.
◦User selects single .dotm file for merge
◦Frm_MAIL_MERGE has either drop down to select CONTACTID or UNBOUND data field for user to type in CONTACTID number
◦User enters CONTACTID to be used for the mail merge
◦User selects SUBMIT
◦Application merges selected .dotm file with the information in table CONTACTS for the selected CONTACTID
◦Merged document is saved on the user Desktop as xxx.docx
View 10 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
Dec 18, 2014
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 Related
May 29, 2012
We are currently using Access 2007 and I've got a database that sends emails out to multiple users depending on the data that was entered. We are about to upgrade to Access 2010 and I am testing it now. I was originally getting runtime error '287' when I was adding addresses to a Recipient object. I got past that by adding the addresses in a string and concatenating. Now I'm getting the error with .Send.
View 8 Replies
View Related
Feb 25, 2013
Access 2010 - add report to body of an email
i have VBA code to create an email and attach an excel file, what i would like is code to add a report called REPORTMISSINGDATES to the body of the email.
this is the code i have so far, which works and adds everythng i wqant except the main body of the email.
Private Sub Command31_Click()
On Error GoTo Command31_Click_Err
Dim Email As String
Dim name As Variant
Dim EMPloy As Variant
Dim month As Variant
month = MonthName([Forms]![STAFFATTENDANCEMenu]![StaffMonth])
EMPloy = Forms!staffattendancezone!Staff
name = DLookup("[STAFFNAME]", "[QRYSTAFFNAME]", "[ASA] = Forms!staffattendancezone!Staff")
Email = (Forms!STAFFATTENDANCEAdjust!Email)
DoCmd.SendObject acQuery, "STAFFATTENDANCEZONECheckEmployee", "ExcelWorkbook(*.xlsx)", [email], "", "", "" & "Attendance Errors", "THIS IS WHERE I WANT THE REPORT TO GO" _
End Sub
View 1 Replies
View Related
Apr 1, 2014
I created a Tracking System and would like to send an automatic reminder to the person the ticket has been assigned to, using the due date and the text message in the field "reminder message". Once the message send, I want the database to recognize and not send it again. I want the database to send a new message if the due date has changed.
View 3 Replies
View Related
Oct 1, 2013
I've set up a piece of VBA code to loop through and email various recipients data, in the form of records within the email body. This works fine, but occasionally produces an error whereby the email output body lines are duplicated spuriously. This only seems to happen when running a large number of email loops.
The data source for the email body content is a table, which for each 'loop' is refreshed with new data by 1) deleting records from that table, and then 2) appending data to the table.
The portion of code for the loop & email:
Do
'Build outputbodytext
DoCmd.OpenQuery ("EmailQ2")
DoCmd.OpenQuery ("EmailQ1")
'set up email
Set rec2 = CurrentDb.OpenRecordset("outputbodytext")
strSendTo = rec2("Email")
[Code] .....
View 3 Replies
View Related
Oct 16, 2012
I'm working on a vacation/time off tracker and was hoping to use the collect data feature. I've created a form where a user submits their requested date and number of hours, at which point I'd like the form to be sent to their manager for approval. The manager's email address is stored in a table. Once the manager marks yes or no, I'd like that reflected in the time off requests table, and an email sent to the requester letting them know if it was approved or not. Is this possible? How would I go about it. I'm pretty new to Access, learning on the fly, but I'm pretty good at modifying code to match my situation/working backwards if you have any examples for me to look at.
View 1 Replies
View Related
Feb 12, 2013
I would like to automate the current process of sending an e-mail with a PDF attachment that is unique to each recipient; it is their test results. I'm sending an e-mail one at a time (which was fine when the number of folks being tested was small, but now I'm up to approximately 200 e-mails and growing each year)
System Environment:
Windows 7 SP1 (64 bit)
Office 2010 Professional
Access environment and Process:
1. A qry object/letter is built based on 4 tables (test results, result averages, a global employee list and location)
2. The Report has both static and dynamic text. Examples include: "Dear" [qry-Letter. F_name] and Address Information
a. =[qry-Letters.F_Name] & " " & [qry-Letters.L_Name]
b. =[qry-Letters.Position]
c. ="Dept " & Right([qry-Letters.Dept],3) & ", " & [qry-Letters.City]
3. The report also includes the employee's results, the session averages (testing is conducted at multiple locations each occurrence is a session) and companywide averages.
4. Report Process: Run the report, entering primary key Emp_ID, report is populated saved (overwritten with the next Emp_ID and printed as a saved PDF.
5. MS Outlook: create a new e-mail, text body is common to all e-mails e.g. "attached are your test results" the PDF is attached and the e-mail sent to the recipient (the e-mail address follows the corporate standard, =Trim([qry-Letters.F_Name] & "." & [qry-Letters.L_Name] & "@abc.com")
View 3 Replies
View Related
May 1, 2014
I currently have a database set up with three basic forms:
Form 1 = Main menu with options to go to Form 2 and Form 3
Form 2 = Employee information form which includes email address
Form 3 = Employee document upload form
My goal is once the Employee is registered in Form 2 they can then upload a document in Form 3. When they upload this document and fill out other parameters Including their bosses name (which is captured as a record in Form 2) they click "Submit Form." I would like for this submit form button to populate an email that is updated to send to the selected "Bosses name"
Currently on the Submit form button I have:
Private Sub Submit_Record_Click()
DoCmd.SendObject _
, _
, _
, _
"email .com", _ <-- this is what I want to autopopulate with the correct persons email (as well as their name below)
, _
, _
"***A new Lab Report has been submitted for your review***", _
"Bosses name," & vbCrLf & vbCrLf & vbCrLf & "Please log into the Report Database and review the latest pending report. If you have any questions please contact the sender." & vbCrLf & vbCrLf & "This is an automated response generated from Microsoft Access." & vbCrLf & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & "ESBU Lab Report Database", _
False
DoCmd.Close
End Sub
View 1 Replies
View Related
Aug 4, 2014
My company recently upgraded our MS Office from 2007 to 2010 (except for Access).
Previously, when I had Access 2007 and Outlook 2007, I had a process that generated 50+ dynamic emails from an Outlook template file (.oft).
The code would loop through a listbox and replace the template's default text to a string of text specific to the selection in the listbox by utilizing the Replace() function on the MailItem .HTMLBody.
Since the upgrade to Outlook 2010, the code is able to run, however, the Replace() function is no longer working; Instead, each email that is generated maintains the template's default text.
The only thing that is not working is the Replace() function, all other aspects of the code work fine.
I've provided a simplified version of the code below:
Dim myOlApp As Outlook.Application
Dim objMailMessage As MailItem
Dim stBody As String
Set myOlApp = Outlook.Application
Set objMailMessage = myOlApp.CreateItemFromTemplate("C:UsersDesktop emplate.oft")
[Code] .....
I've recreated the template file in Outlook 2010, thinking that the template created with Outlook 2007 would be the culprit, but to no avail.
What could have changed from Outlook 2007 to Outlook 2010 that would render my previously valid code ineffective?
Are there certain references I need to enable in both Access and Outlook to allow VBA in Access modify the content in an Outlook email?
View 12 Replies
View Related
Jun 20, 2013
I am currently using SendObj method to send an Outlook email and selected report as an attachment using the current form's data. This is triggered by a button click.
This is working well apart from having to use 'ClickYes' to outwit the Outlook 2012 security system.
However I now need to selectively send an extra pdf file with some of the emails based on a Yes/No field on the form. This is a fixed file on my local C drive.
I think I have to use automation to do that but I can't find a method of specifying the report I currently use in the SendObj method in the .Add.Attachment line.
Do I need to run the report and then save it so that I can specify the path and name in the .Add.attachment line followed by code which will delete the file? or is there a means of combining the code I currently use in SendObj to create the pdf object with automation option?
I have working code for both attachments at present but they send separate emails and I would prefer to combine them if possible.
View 3 Replies
View Related
Apr 4, 2013
Is it possible to have a value in an excel 2010 cell be used to update an access 2010 db?
For example, if in a spreadsheet "test" cell A2=3 then in a db "test" a column "value" is 3. However, if the value in the spreadsheet changes so does the db.
View 1 Replies
View Related
Mar 23, 2015
I wish to work in Access 2010 with a subset of my Outlook 2010 contacts (all desktop- no network barriers), so I am exploring the Desktop Contacts DB found in Microsoft's templates. It passes contact data back and forth using the standard macros, but I wish to add more fields from Outlook. It seems I need to find the code associated with the macro. I believe I can solve the problem using a Linked table, but am wondering if there is another solution?
Calendar synchronization is another issue. Linking gets me to the Archive Calendars, but not the Current one in Outlook which is what I want.
View 2 Replies
View Related
Jul 28, 2006
I would like to create a program within access that is linked to a specific folder on my C: drive and waits for a file to appear within the folder. Once the file appears, i would like access to process the file and return the output back to the folder. Is there a way to do this?
example: test.txt appears in folder C:Test. Access grabs the txt file, opens it, processes the information and then writes the output back to C:Test. I will need access to continously check the folder for new files.
thank you
View 1 Replies
View Related
Oct 28, 2004
I'm trying to take a value in an Access form text control and populate a single cell in an Excel file.
I can open the file I just don't know the VBA syntax to populate accordingly. Any help will be appreciated.
Thanks.
View 2 Replies
View Related
Dec 7, 2004
My table has pre existing data in 5 fields. I want to be able to schedule
and automatically append 25 records to the fields once a week. What is
the best way to approch this.
Thanks
View 1 Replies
View Related
Apr 1, 2015
I have a form on access which allows users to upload csv files.
The files can be in any layout, so the field names and the number fields will always be different each time.
This can cause issues on data types, as access assumes a certain data type for a field, but the data may not always reflect that.
For example telephone numbers, access assumes this is a numeric field, however sometimes there will be symbols in this field such as +44. Which then causes import errors.
Is there any way to import these files without getting these sort of errors?
Currently the import process uses a simple TransferText method
DoCmd.TransferText acImportDelim, , "Original Data", Me.filelist.RowSource, True
This imports the data into a new table called 'Original Data'.
I had the idea of saving the table structure of the csv being imported (Importing the data with errors, then deleting all the records to just leave the structure) However I can't find a code to change the data types of that table to TEXT format, when I can't tell what the field names will be.
This whole thing is an automated process, which why there are loads barriers btw.
View 14 Replies
View Related
Aug 1, 2013
I have just started to develop a database that will export data directly into a word template. I have used Word automation quite a lot but I'm new to trying to automate Word from Access.
It's going OK at the moment, I have got the db to open up the template, write data and then close. My objective is to add the data to multiple tables within word. So I have created several tables in my word template and then tried to select these tables and write to the them. Everything is thing for the first table but for any other table I get an error message saying that the member of the collection doesn't exit i.e. the table isn't there. I select the table using:
Code:
objWord.selection.tables (2).select
I then used:
Code:
objWord.selection.tables.count
To show how many tables were in the document and it doesn't matter how many there are, it always says there is 1 table.
why it can only see 1 table and what I can do to get around it?
View 2 Replies
View Related
Nov 4, 2013
I am using Access 2007 and are a newbie.
I am using the following code (Sendobject) to create a PDF of a report and send it via email to a customer's email address.
Private Sub EmailWorkOrderQuote_Click()
On Error GoTo Err_EmailWorkOrderQuote_Click
Dim stDocName As String
Dim mFilename As String
Dim mEmailAddress As String
[Code] ....
It works fine except that the I want to to able to utilize the ability to send the email with either both the CC and BCC or either or neither as well.
I have a table called "My Company Information" that contains the email addresses for the CC and BCC. If I have values in these fields, everything works great! If I have no values in either of these fields, I get "Invalid use of Null"....
I want to be able to use either both CC and BCC, either CC or BCC, or neither used and it still work.
View 9 Replies
View Related
Nov 12, 2014
Any code that stops the vba from running if an 'output to' function is null. I've found some code using the DCount function but I'm struggling to adapt this to multiple excel outputs.
I'm sending 5 excel files via E mail to several addresses and I'm wanting to identify if the first file has records, currently using access 2010.
View 5 Replies
View Related
Oct 24, 2013
I've been using the following code successfully in Access 2003 & now I need to migrate to Access 2010. The purpose of the code is to use the items that the user selects in the list box to build the criteria of a query. Access 2010 keeps giving me a syntax error when I try to run the query & I don't know why:
My code is:
On Error GoTo Err_Command151_Click
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] .....
The syntax error I get in Access 2010 is:
Syntax Error in query expression 'SELECT * FROM
qryContractListSummarybyDateContract3TYPEBREAK WHERE
qryContractListSummarybyDateContract3TYPEBREAK.Rep ortableName IN('Adbri
Masonry NSW');'
View 12 Replies
View Related
Mar 5, 2013
is there a way to have both Access 2010 and 2003 exist peacefully on the same desktop. My company uses Office 2010, but my department has an Access 2003 application. As a result, I have to have A2003 on my desktop and use it quite frequently for this one application.
I have some small databases that I really want to move onto A2010 so we can take advantage of SharePoint functions, etc. Is there anytning I can do to be sure that the .mdb and .mde open in A2003 and the new .accdb opens in A2010, without getting errors and the Windows installer going through the Office installation process for the respective version? I've gone to the file locations and set the "Open with" setting to the respective version.
View 4 Replies
View Related
Oct 15, 2015
I have Access 2010 database.
I have a Reporting Dashboard form that inturn has many buttons to open up different reports and forms.
Now I have been asked to extend the access to a few other users with restricted access. The new users cannot see all the form and report buttons in the Reporting dashboard form.
How can i restrict this? They also need to be given separate logins and passwords. How I can accomplish this?
View 2 Replies
View Related
Sep 6, 2012
Can I use the or operator in Access 2010
IIf(Mid([WBCode],4,1)="D" or "N"....
I am finding that I can use AND and LIKE, but not seeing the OR
View 1 Replies
View Related
