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 RepliesCurrently, 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'm currently working on a way to import data from many word documents into a table - I've got this working fine using the following code:
Code:
Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
[code]....
However, I'm wanting to add a link to those files to the table so that the user can click on the record (or select one and then use a button) to open each document in it's native environment (MS Word). I've tried adding a self-referencing hyperlink to a form in the word document and then reading that in - but that doesn't seem to work. As these files may move around (and there are a lot of them) I need to make sure it's done automatically so can't really get the user to write it in manually.
I'm trying to add the information from a form and subforms to a report. This would be a medication mar sheet and needs to be in the format of the attached word document. I'm happy to do it in a report and change the formatting if that will work better. The personal info needs to go at the top and the medication needs to be listed on each sheet.
The form is called FrmMarSheet. It basically needs the info from that like the "sample mar sheet.doc". I can only get one medication per page to work at the moment.
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?
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 Relatedi 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 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 RelatedI use a database that someone else set up and is no longer here. To get access to the database you need to enter a user id and password. I have a new user who has been getting into the database using an established user's id and password.
I went into the file and found the table with the users, their id's and passwords listed, and added the new person to the table, but I still get an invalid user message when trying to access the database with the new user's information.
I went through the Access Help and tried a couple things it said for adding users to a password protected database, but either I didn't do it right, or the way to add a user is different from what I am getting from Access Help.
Lastly, this database is different in that if I would add a report, it only shows up on my computer when I access the database, the report doesn't show up on anyone else's. That is odd to me, but it is how it works.
Thank you for any help you can give.
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]...
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'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.
Is it possible to format a PDF document created in MS Access i.e. set the document to landscape instead of portrait as in the following code;
DoCmd.OutputTo acOutputQuery, "Query Name", acFormatPDF, "Document Name", False
This PDF document is attached to an MS Outlook e-mail and sent without any intervention from the user. This is accomplished using further VBA code. This code is working at the moment sending both Excel spread sheets and PDF documents but the PDF's require the page formatting before sending. I want to be able to accomplish this without having to open another application such as Adobe Acrobat.
I use the following code to achieve following
a) Open a word document from MS access / VBA,
b) Connect to data from a query
c) mailmerge it
d) Save it as a new document containing mailemerged letters.
The code does open the required document as I can see it open.
I put a message box to recheck that filename/path is what I mean, and to to know till which line the code runs. Code runs till that point and that word doc is seen open. I get the error message before
Then I get error message.
I was fooling around and was getting message " This command is not available because no document is open" I tried to remove few lines thinking redundancy. Before getting the message, I was also getting the error trapping message "No documents opened."
Now I am getting different message.
I am getting message " The remote Server Machine does not Exist or is unavailable"
Following is the code I have written.
Dim xlApp As Object
Set xlApp = CreateObject("word.Application")
xlApp.Visible = True
fn = FormPathName("941 2014 AutoOpen.doc")
[Code] .....
We need to replicate an Access report we have in Microsoft Word. The report has a fixed, small image in the header and so we embedded it in the report (it is not in an external file). To put this image in the Word document the only way we have come up with is shown in the code below.
Code:
Dim apWord As Word.Application
Dim doc As Word.Document
Set apWord = CreateObject("Word.application")
doc.Shapes.AddPicture "G:ImagesSinful Banner.bmp", False, True, 0, 0, 540, 42
Which requires an external image file. We really would like to avoid this. We could make a template Word document, but that too would be an external file. We know how to put this image in a table as an OLE object, but can't find any way to get it from the table into the Word document.
When I execute the below function I get an error. If I use the commented out "Open" line it works fine. I get Run-time error '52'. Bad file name or number.
Private Sub GenerateSSA_Click()
Dim strRA As Variant, strFileNew As String, strFileBlank As String
Dim intFileOut As Integer
Dim rstWork As DAO.Recordset
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
[Code] ....
I have been trying to populate a merge document from access. I have the dotx files organised and the recipient file (Query) within my main access project. The following code opens word, opens the selected file (dotx) but can't find the db file.
I am using access 2010 and .accdb database format. I have temporarily put in MsgBox lines to display the running content of fields and they are producing the right info.
Private Sub DocumentsCbo_Click()
' load precedent template as a dotx, merge fields into precedent document, allow user to modify if needed, save it as a pdf
On Error GoTo ErrTrap
Dim MyPath As String ' Path of the source template
Dim DestPath As String ' Path of destination document
[Code] ....
Not sure if this is possible but I am trying to extract data from a word document to set up a database.
Basically I am trying to capture data from completed forms, similar to the sample attached, the actual blank form is 20 pages long and once completed can be as many as 30 pages, or even more, although the format doesnt change (i.e. individidual cells will expand to fit the data in the cell).
I want to be able to scan through the completed forms and extract the data i.e. Full Study Title, Short Study Title, Study Type etc. into a database.
The issues:
1. Each document will have a different name but will be stored in the same location.
2. What is on page 8, for example, in one document is not necessarity going to be the same on every document (due to expanding cells)
3. Each sector is in a separate table but tables can spread across several pages.
4. Some data is stored in a checkbox format rather than text.
5. I dont want to extract all of the data, only certain sections (at least at this stage).
If I use the word template named "ABC.dot" as attached and write the following piece of code to print off the letters with different appropriate background , it works perfectly fine:
Code:
Sub PrintLetters()
Application.DisplayAlerts = False
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Set cn = New ADODB.Connection
[Code] ....
But if I combine two templates together in one template as attached "Capita.dot" then it doesn't display background picture at all in any of the cases. I am using the same piece of code but the background image is not displaying. The background image shows logos for each letter like For capita letters , the logo will be capita . For Friends Life letters the logo background will be different.
I'm using ACCESS 2010. I have a form which is having a embedded word document. What I'm trying to do is i want to enable user to format a mail in the embedded word document which will contain rich text and screenshots then with a click of button an outlook mail should open and content of this document should be pasted there. Once user sends this email, I want to save content of embedded document in the database.
View 4 Replies View RelatedI have hp scanner whose twain driver has the option to scanning in the pdf document.
Microsoft WIA 2.0 have save scanned document only to graphical format.
I want automated (on click any button control) to scan multipage document from glass source or ADF to pdf document and attach it to attachment field.
I'm found VB Module for accessing TWAIN compatible scanner but it is call twain software and scan to bmp format with assigned name of file.
Me need it also only use the pdf format .
The following code is throwing a 429 error when opening the selected document (code in red). How do I correct this?
Code:
Dim f As Object
Dim FileName As String
Set fd = Application.FileDialog(1)
Dim FileChosen As Integer
FileChosen = fd.Show
[Code] ....
The code below allows me to open a publisher document that is already merged to the current record. What I need is to allow the user to open the document according to the group they are reviewing. How to accomplish this?
For example:
IF Group = A1 OR B1 THEN OPEN AB_1.pub
IF Group = A2 OR B2 THEN OPEN AB_2.pub
IF Group = A3 OR B3 THEN OPEN AB_3.pub
Dim pubApp As Publisher.Application
Dim pubDoc As Publisher.Document
Set pubApp = New Publisher.Application
Set pubDoc = pubApp.Documents.Add.Application.Open("c:Publishe rAB_1.pub)
pubDoc.ActiveWindow.Visible = True
I have managed to export the data that I want into a spreadsheet using a command button.
Once my colleagues have completed the spreadsheet, I need a command button on a form with a file search dialogue box which will allow them to transfer the spreadsheet back into the database - updating the relevant records rather that over writing them.
Is there a VB code/Macro I can use which will allow me to do this?
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.
There are two pages in a word document. All the pages have same headers as attached in word document "Capita.doc". I want to display that header on just first page and on second one page, I want to display watermark stored in "J:PAP107.jpg". The following code displays same watermark on all the pages i.e "J:PAP107.jpg" but I want to keep the header as in the attached word document on first page only and display watermark stored in "J:PAP107.jpg" on second page.
Code:
Private Sub CmdPrint_Click()
Call WordSetupQA("C:CAPITA.dot", "J:PAP107.jpg", Format(DateSerial(ComboBox4, ComboBox3, ComboBox2), "mm/dd/yyyy"), pno)
End Sub
Code:
Sub WordSetupQA(fnTemplate As String, fnBackGroundPic As String, b As Date, a As String)
On Error Resume Next
'MsgBox txtbox
Application.DisplayAlerts = False
Dim strworkbookname As String
strworkbookname = "C:System1.mdb"
Set WordApp = GetObject(, "Word.Application")
[code]....