Modules & VBA :: Word Naming And Emailing Attach From Access 10?
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
I am trying to write code in an excel worksheet to try to export excel data to a table in access.
The range in which the data is situated in excel varies. So the range address varies with the data. After a lot of searching I wrote the following code which works for a range which is fixed. The code is the following:
Dim acc As New Access.Application Range(Range("b22"), Range("b22").End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ghazla", RefersTo:=Selection acc.OpenCurrentDatabase "D:PayRollFactoryJasminePayroll.accdb" acc.DoCmd.TransferSpreadsheet _
[Code] .....
As one can see from the first two lines of code I am trying to first delineate the range and then to name it. The intent is to name the range "ghazla" and so to cater for the fact that the extent of data varies.
The code works with the fixed range ="Sheet2$B140". However what I need to do is to adjust the code so that I can insert "ghazla" as the range name so that this can vary.
I have a subroutine that works well for sending emails. A need has arisen to, in some cases, send an Access report as a PDF attachment. I know how to do this using the SendObject method, but that won't work in this app. I also don't want to have to first save the report as a pdf file. I think I'm close, but I don't know the syntax for passing the report name as the Source parameter in the Attachments.Add method.
Code: If strEmailAddress = "jsmith" Then .Attachments.Add source:=MyReportName, type:=acFormatPDF End If On Error GoTo SendErr .Send On Error GoTo 0
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?
Currently, I have a form with a button that sends emails to all records in a given record set. My email message is hardcoded, and it has dynamic fields that are different for each record. For example, "Dear rs![First Name], you have been placed in rs![Group Name]..." I have set this up in visual basic as an email object, and my .To field is set to rs![Contact Email Address]
Is it possible for me to put my email message in a Word document (so that it can be edited without going through the visual basic editor) and then either attach it to each email or put it in the message body (while still maintaining the dynamic fields)?
I would also like to automatically attach on the server to this email. Such a file stored such as servernamesharefilename.pdf. Is it possible to do this?
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.
I have a Database in which I would like to archive data once it is now longer actively being used. I have VBA code that does everything i need it to, however I would like to name the new archive table related to the dates in the data being archived. Below is the code I am using to archive and delete the information from the main table.
Code: Dim strSQLCreate As String Dim strSQLDelete As String Dim strArchiveTableName As String
[Code].....
I know it will be in the "strArchiveTableName" variable, I am just not sure how to pull the dates out of the data being moved. The TestData table holds a date and time for each test, and the earliest and latest dates is what i want to append to the table name.
For example: Currently "tblTestData_A102Archive"What I want "tblTestData_A102_1/1/2012-1/1/2013"
I'm currently working with Access 2007 building a way to create PODs to clients. I have most everything laid out, one problem I can't seem to get around is naming the file the SOW #. Below is the code:
Function Run_all_PODs_01() Dim myPath As String Dim name As Object
DoCmd.OpenQuery "Q5 SOW bill requested data points all", acViewNormal, acEdit DoCmd.OpenQuery "Q5 SOW bill requested All 01", acViewNormal, acEdit
[Code] ...
I can't seem to get the name to equal the SOW number.
The table that I'm trying to reference is "SOW bill requested data points" it is a basic table one row with a column header as SOW #. I'm not sure how to tell it name the file that data point in the column.
Been hunting a round looking for some code that will output my access report to excel but more inportantly add a unique field from the report to the name of the file. example would be [FileName] & [ReferenceNo].xls.
I would like to add the "Month" of the information queried to my file name at export if possible. I included my current code and an example of how I would like the title to read.
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?
I am currently in the process of creating a form that will allow you to enter details for an email sent, select a number of documents from a list box and then open the mail message pre-written with attachments. The attachments reside in a list box currently, and I am attempting to use a "For Each" with item selected.column(3) as the file path, as column(3) contains the filepath from the table.
Currently my code is:
Private Sub OutlookBut_Click() Dim olApp As Object Dim objMail As Object Dim varItm As Variant On Error Resume Next Set olApp = GetObject(, "Outlook.Application")
[Code] ....
When this code runs, outlook will open as a process when it is closed, but then freeze and not allow me to see it or access it in anyway. If Outlook is opened Microsoft office usually gets angry at me and decides to throw error messages at me.
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)
Am working in MS Access 2013 and I have a form with a button, I need the button to doattach automatically the pdf-report to the email template(i don't want to attach the pdf to a new email)
Right now the command open the E-mail template and create the pdf-report in the desktop
Code: Private Sub Command18_Click()
Dim oApp As Outlook.Application Dim oMsg As Outlook.MailItem Dim oAttachment As Outlook.attachment Set oApp = New Outlook.Application
I have a database of film events, for which i have to do posters.
With the click of a button on a form, access opens word and puts all the fields in, including a field called [photo], which is the name of the image stored outside of the database.
Is there a way of getting that image into the word Document with all the other fields? Or have I just wasted an afternoon....
I'm doing a project for my work. I created a few reports in Access. Some of these reports are simple graphic bars. How can I insert these reports into a word document template?
I often create contract using mail merge. I have an access file that I want to use as data source for word file. But it does not automatically.
Please see the attached file !
If there are 1 customer and 1 property, I do not need to do anything. Conversely, if there are many customers and many properties, I take time to manipulate.
Firstly, I open the word file. I have to copy and paste paragraphs that I want. Highlight of the original paragraphs is blue.
Secondly, I click 'Insert Word Field' -> select 'Next Record'.
In short, I want to use VBA in access file to automatically perform the steps that I have outlined.
I was tasked to create an application where by the user enters keywords into an Access form, and when he clicks the button, it will run the keywords against the file names stored in the table and automatically open the Word document that is the best match.
I have created a table query called Directory, which contains FPath (Z:), FName (Document1.doc) and Directory (Z:Document1.doc).
Code: Private Sub Command2_Click() Dim wrdApp As Word.Application Dim wrdDoc As Word.Document Dim filepath As String Dim strSearch As String
If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
[Code] ....
This is the code that I am currently using to search and open the Word documents, however, this code only manages to open Microsoft Word program without loading any documents. Also, there are no error messages when I click the submit button.
I have been tasked with setting up a certificate request database. I have an Access 2032 FE & SQL 2012 BE. I need to store PDF documents from all our branches.
I have been working on this for the past 3 working days and I have seen a great deal about saving the file path to the database vs saving the actual document in the DB. The documents would be located on each users computer.
I have a Form with all the information that is required by the certification department. On the form is a button (Called InsertID(ID in this case is the persons Identity Docuement)) and the button is linked to a table called dbo_CertificationSupportingDocuments. The table is referenced to the Main Certificate Request table.
The dbo_CertificationSupportingDocuments table is structured as follows:
I am having the strangest results with my automated mailmerge. Basically it does work, but not all the time. The basic idea is to allow the user to dynamically create a query that produces a result list which fills a temporary table. The use then selects a prebuilt merge template and merge is executed against the temp table. The merge template are of the .doc type, but sme have been converted to .docx; the .doc files tend to work most often, but all of the will eventually get a Table is locked message... However if I run in test mode with code breaks and manually step through the process it always works... here is the heart of the code ...
Err_Pos = 10
Code: DoCmd.SetWarnings False ' if tmp tbl left over from last run kill it DoCmd.RunSQL "Drop table Word_Merge_Tmp_TBL" Err_Pos = 12
[Code].....
There are many error cases in the error catch routine. That I have managed to make Access stop hanging when word has a problem or the table is locked. But I can't get the table to be free consistantly and why does it always work when I manually step through the code.
