Modules & VBA :: Exporting And Emailing From Access
Jul 18, 2013
i am very new to access, but i can code in excel well.what i really want to do is no how to automatically email a report, query or table. I also would like to be able to export the above three to excel as well. I have worked my way up a little bit but my hair is falling out with how hard this is to achieve. I have come up with the following so far:
Code:
Dim outputFileName As String
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String
[code]...
The above code works a bit, but it only allows me to export tables (dont know how to modify it). It also doesnt open the table in excel properly, it opens excel, but the table doesnt open. I have to go to the location and manually open it.
View Replies
ADVERTISEMENT
Aug 29, 2014
I am trying to automate the process of sending a query result via email.
I have code that will export a query as an excel document, using DoCmd.SendObject, but it has the name of the query.
DoCmd.SendObject acSendQuery, "Dailyrpt", acFormatXLSX, email.address, , , "Daily Report", "This is today's report", -1, False (email.address would actually be the email address it is being sent to.)
I also have code that will export the query to excel and save it as a file, using DoCmd.OutputTo, where I was able to name it something meaningful to the end user, and include the date.
DoCmd.OutputTo acOutputQuery, "Dailyrpt", acFormatXLSX, "Z:FolderComplete as of " & Format(Date, "yyyymmdd") & ".xlsx"
Dim Foldername As String
Foldername = "Z:FolderComplete as of " & Format(Date, "yyyymmdd") & ".xlsx"
Shell "C:Program Files (x86)Microsoft OfficeOffice14EXCEL.exe """ & Foldername & "", vbNormalFocus
Is there a way to either send the saved (renamed) file or to include the current date as part of the SendObject command?
View 4 Replies
View Related
Apr 21, 2015
I need to create a word file (it needs to be word) based on template (not a very complicated template) max 1 to 1 and 1/2 pages long.
the word doc needs to be named from the recordset but for now assume 12345.doc and the next one will be 12346.doc etc (I have a unique number system - available from tables /query .
i can either make the word doc in code or use a template (template would be better) recordset could have 20-30 in it each time i run it - but to be run every week
second half is how to email this out. i had in mind a email system
email to . test@testhotmail.xyz
file attach = this folder where the docs are store and attach 12345
in a loop
so either
create word file - email word file in a loop each time or create all the word files and then send them individual
View 4 Replies
View Related
Apr 21, 2015
I'm trying to export queries from access to excel using the DoCmd option. The code (see below) works to a point - it exports some of the queries before I get a run time error:
"31532: Microsoft Access was unable to export the data".
The worksheet tabs also do not pick up the query name but instead return what looks like a temporary ID (e.g. ~TMPCLP118431). Have tried different file locations and versions of excel but the same thing keeps happening.
Code:
Sub ExportAllQueries()
Dim qdf As QueryDef
Dim db As Database
[Code]....
View 3 Replies
View Related
Jan 16, 2015
I have got an existing query which is exported to excel through DoCmd.Transferspreadsheet. In simple terms the query looks like below:
Group | Client
Group1 Client1
Group1 Client2
Group1 Client3
Group2 Client4
Group2 Client5
Group2 Client6
etc.
As some of the groups contain large number of clients, I am trying to find the code to split groups by pre-specified number of clients and export to excel, as follows:
Group1 - clients 1 to 300.xlsx
Group1 - clients 301 to 600.xlsx
Group2 - clients 1 to 300.xlsx
etc.
View 1 Replies
View Related
Dec 7, 2013
I would like to export from access a recordset into Excel and bold the column headers.
My current code works for the most part, except for the bolding.
You run the code in Access module and the code does the following:
1) checks to see if the excel file is open
2) if excel file not open, it opens the file, clears existing records, and starts copying and pasting new recordset into the RAW worksheet
3) it is then supposed to bold the column headers.
bolding is not working.
Code:
Sub TestFileOpened()
Dim lastRow As Long
' Test to see if the file is open.
If IsFileOpen("c: est2003.xls") Then
' Display a message stating the file in use.
MsgBox "File already in use!"
[code]....
View 1 Replies
View Related
Oct 9, 2014
I know Access can setup an Outlook Task to auto-export query to Excel, but it requires the Outlook to be always open on the user's computer.
Is there an easy way to setup a schedule that can automatically export a query to Excel and this schedule will then auto-email the exported Excel file to an email address every Monday at 5AM for example?
If this can only be done in VBA, any reference I may start with?
View 4 Replies
View Related
Oct 22, 2014
I have a database with my staff in. If they are car drivers I take not of their Insurance and MOT renewal dates. I have built a query that gives me a reminder date ten days before and 5 days after the renewal. I also have an email function that emails my administrators the dates of these. Is there a way of getting the database to email specifically on those dates. I.e. a reminder 10 days before and a check 5 days after?
View 3 Replies
View Related
Jan 10, 2014
I would like to have my macro/vba send an email based on the results of two tables, or if both tables are null, i'd like the email to state that.
View 2 Replies
View Related
Jul 24, 2013
I'm trying to figure out why I can email a report as a PDF, but not save it as a PDF using the code below? When I save, I get the dreaded error 2501.
The searching I did for this said to uninstall and reinstall printers (I've done that - seems to have no effect), or took issue with filtering the report with a where clause (but if that was an issue, why does it email a PDF just fine?).I'd just like to be able to save a report as a PDF to a specified location.
CB
Developing in: Access 2010; Win7
For use in a mixed 2007/2010 XP/7 environment
Code:
Private Sub EmailDietCardBtn_Click()
On Error GoTo MyErrorHandler
Me.Refresh
Dim stReport As String
Dim stWhere As String
Dim stSubject As String
Dim stEmailMessage As String
Dim stCaption As String
Dim myPath As String
[code]...
View 11 Replies
View Related
Jul 2, 2015
I have built a simple database (by no means complete) but I am after adding some further code into it. Basically on the form [FrmMain] I have a button that allows me to send a document to a relevant CSU contact. It finds the file name based on the info in the table TblResident. What I want to do is open the word document before sending and add a password to it using vba.
View 1 Replies
View Related
Nov 24, 2005
I have a field within a table called E-Mail. I want to send a newsletter to everyone withing the DB. I have created some code which is as follows:
Private Sub testemailer_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("QryStoresEmailticked")
While rsEmail.EOF = False
strEmail = rsEmail.Fields("E-mail").Value + ","
DoCmd.SendObject acSendNoObject, , , "jo.bloggs@k-n.com", , _
strEmail, _
"UK KNLogin NewsLetter", , True, ""
rsEmail.MoveNext
Wend
Set rsEmail = Nothing
End Sub
Now this works but it opens an individual email per email address into the BCC part. That a major problem if you have over 1000 contacts to send to. How do i get all my email addresses from my DB into the BCC of an email?
Any help would be much appreciated!
View 1 Replies
View Related
Jan 30, 2006
Hello, I am looking for some help on what appears to me to be a very large problem. Hopefully, to others, it's an easy fix. I have a very large database that has several details, of which one is email address.
My report is structure to group first on the email address, 2nd on the cost center, and third on the exeption. I am emailing the report to each individual email on the report. I can get it this far, what I can't get is for the email to only mail out each part of the report that is strictly for that particular email address.
I know this isn't very clear, but right now the entire report goes to each email address, I only want specific pages to go. The kicker to it all is that if I set specific parameters the first time, then I would have to set them every time, because the report varies in length each time it is pulled.
Please helppppppp! Thank You
View 5 Replies
View Related
Jul 19, 2013
I've created an accounts package for my business using access 2003 and I'm just thinking of some time saving features.
We need to email our invoices to our clients.
Normally we print out an invoice, scan it back into the computer, save as a PDF and attach to an email manually one by one.
My package can now print to PDF (saving one step), but what I would like is to be able to print to PDF and attach to a new email (with the email address coming from the customer table) and possibly attach more than one PDF to a single email.
I know you can use
Code:
DoCmd.SendObject acReport, stDocName
to send a report but I would like it as a PDF as my formatiing is lost using that code and we normally send PDFs to our clients.
View 14 Replies
View Related
Apr 9, 2006
Hi all i have a delemma
Im building a database that is required to send out a email once a week to selected parties informing them of what items will go out of date this week.
The main problem im trying to over come is the Automatic email.
Any help would be greatful
Mike
View 1 Replies
View Related
Mar 29, 2007
We are a facility maintenance company that do a variety of jobs for many commercial companies with a several different trade groups we have (electrical, hvac, plumbing, painting, masonry, etc...) Our Trade Group Managers submit estimates and budgets for the jobs they are bidding to. I created a database to store all those jobs so we can track what manager has bid to any certain job, what trades are involved, if it has been accepted-rejected-in progress-and such. As of now the managers turn a written (paper) form into me to submit an estimating inquiry request and I put it in the database and print them out a receipt of all the information they submitted to me along with an Inquiry Number (which is the primary key in the database)-then that is how we keep track of that particular bid-with that unique Inquiry Number assigned to it.
Now we are wanting to be able to have this all electronically. So the managers can submit an inquiry request electronically and get a receipt back electronically-either by email or online or whatever. On the form they fill out there are several different trades that are able to be checked off. After they are finished filling out the form I would like for there to be a button at the bottom to email that form to all the checked off trades. How do I go about doing that?
..any suggestions would be greatly appreciated.
Thank you so much!!
View 2 Replies
View Related
Aug 27, 2007
Hello,
From the searches of this site Ive got it down and working to send an email with attachment via access. Thanks
The one thing I cant find is how to change the "From". I don't want these emails send via my name but my department. I usually just change the <From> to the department name in Outlook and send.
I cant seem to figure out how to make it work in access.
FYI: I did it via a module
With objMailItem
.To = "emails"
.Subject = "subject"
.Body = "text"
.Attachments.Add "C:file.xls", olByValue, 1, "department report"
.Save
.Send
needless to say .From does not work.
View 2 Replies
View Related
Mar 13, 2006
Please help! I wish to set up an access database from which I can email mutiple contacts at once. I would like to send a pretty HTML message and, if possible automatically address each email personally, i.e. Dear John rather than Dear all.
I have experience with access but not sending emails from records stored in a database. My visualbasic is um...basic, but I can understand it if you're kind enough to explain in simple terms! Your advice would be most appreciated - I need ease of use and most importantly the personal touch of addressing each email to the individual.
Thanks! Look forward to a reply!
View 1 Replies
View Related
Jan 19, 2007
Hi to all!
I dont know if this is possible but can someone please suggest if this is possible and if so where I could find some info on how to do it or what I should be looking for in this group.
Can I set something up so that I can email from Outlook to all the customers in the query below?
I have an access database which I have creatged a query that pulls results from a table with 100 customers in it.
Lately I have been cutting and pasting information from the database and email every customer individualy.
An example would be as follows.
------------------------------------------------------------------------------
Dear JOHN,
Your listing with us contins the following results...
CAT
DOG
MOUSE
Your listing has been viewed 10 times.
------------------------------------------------------------------------------
The text in capitals are results from three colums in the database.
Colums are called
Name: JOHN
Email:JOHN@THEPETSHOP.COM
Result: CAT, DOG & MOUSE
Search Result: 10
Can I set something up so that I can email from Outlook to all the customers in the query above?
Where can i look to learn how.
Cheers
Mally
View 1 Replies
View Related
Jan 12, 2012
In my database, I need to have a button on a form which when pressed (1) pulls the email address from the displayed record on that form, (2) loads an Outlook instance, (3) in that Outlook instance, load a drafted email for editing and sending.
More detailed: My form, frmReferrals, contains the email address of a contact. I need to have a button, cmdEmail, which loads the email address of the displayed record in an Outlook instance. Ideally, I would like the loaded Outlook instance to pull some other information from the form. For example, I would like it to pull the name of the contact and display it.
Something like this:
"Dear " & [FirstName] & " " & [LastName] & ","
Leads to this:
"Dear John Smith,"
There would be a lot more text in the email with other information to input automatically. I would prefer to use as little coding as possible... but I suspect that all of this will have to be done with coding.
View 2 Replies
View Related
Aug 7, 2015
My database is stores information about students such as name, student number, programme, email, etc. I have a STUDENT form with this information visible.
I also have a another table and MARKS sub form that contains the details of courses completed by the student and results. I have placed the sub form in the STUDENT form and can see each student's details and a list of their courses.
I want to be able to send this information to the relevant student via email. The student should only receive his information and no one else's.
Can this be done? Do I need to create a report first? Should I be using WORD to produce the emails?
View 1 Replies
View Related
Oct 9, 2013
exporting an Access query to Excel using VBA.When I run the code, the Excel workbook that is created defaults to the name of the query.I use naming conventions for my queries so the tab of the Excel spreadsheet is named "qryProviderAuditExport". I would like to name it "Provider Report". Is there a way to do this.It is one spreadsheet that is created when the code is run and there is only one tab to worry about. Here is my code so far:
Dim file_name As String
file_name = CirrentProject.Path & "Submitter_Audit_Report.xls"
DoCmd.OutputTo acOutputQuery, "qryProviderAuditExport", acFormatXLS, file_name, True
If possible, I would like to do this during the export without having the code open the Excel spreadsheet and doing it after the fact.
View 4 Replies
View Related
Jul 16, 2015
I have created 2 reports one that needs to be exported as an excel file and the other report as a PDF file. I have the exports working correctly for each on a button except this saves the files with the name of the report. The client wants the name of the file to appear as one of the fields on the report (the info that is in the description text box for example).
I have thought of two ways to do this and neither seems to be great - one is create a copy of the report with the new name and then export that saved file then delete the renamed report OR similar to that but instead rename the file save and rename it back (this causes all kinds of problems if the user cancels out that would need to be handled in the error handling - this is not a good way to go.
View 8 Replies
View Related
Nov 22, 2013
I Export data by the following code.
It works actually but when i go back to Access i get the following error message
runtime error 424, object is necessary.
Code:
Private Sub Befehl1_Click()
Dim xlApp As Object 'Excel.Application
Dim xlBook As Object 'Excel.Workbook
Dim xlSheet As Object 'Excel.Worksheet
Dim rstID As DAO.Recordset, tmpStr As String
[Code] ....
View 8 Replies
View Related
Jan 8, 2015
I have a database where I record grades for my students. I would like to create a macro so that every time I record a grade as an "F", the student will receive an email that will contain an attachment of their scores. A lot of the information I want to include in this email is in several different tables. So to make it easier, I have created a query that lists the following:
Students name (SName)
Student's email (SEmail)
ID# of the form I use to record their grades (ID)
ID# of the studen'ts work (WorkID)
Date of their work (CreatedDate)
Date I graded their work (AuditDate)
Grade="F" (Accuracy)
How can I create something that will automate emailing of this information--to the individual student with only their information? I am using Access 2010. If I can't automatically send the emails, I would like to create a button in my audit screen that will send a report containing that student's information to them.
View 1 Replies
View Related
Feb 24, 2015
how i can calculate totals after i export some data from access to excel (using CopyFromRecordset). I'd like to put the total the row after the last row of data similar to how one use to AutoSum in excel.
View 4 Replies
View Related
