Link to home
Start Free TrialLog in
Avatar of MDauphinais1
MDauphinais1

asked on

Create and Save Word Doc from MS Access

I am trying to make it so that when a user clicks a button on a form, MS Access will automatically generate a word doc with the data from the form, save the doc in a temp location with the formatting (font size, bold, etc.) that I specify and a file name that I specify, attach the doc to a new e-mail message with the subject and To already filled in, and then delete the temp doc.

With the code below, I can get as far as generating the new document with the data from the form, but then for some reason it creates two documents instead of 1 and doesn't hide word, prompting the user to save the file. I don't want the user to see Word or select what to name the file.

The code I have so far is attached.

Please let me know if you have any ideas.

Dim fsObj As New Scripting.FileSystemObject
Dim text As TextStream
Dim filepath As String
 
filepath = "C:\Documents and Settings\" & Environ$("UserName") & "\Desktop\TT_Request.doc"
 
If fsObj.FileExists(filepath) = True Then
fsObj.deleteFile (filepath)
End If
   
   Dim WordApp As Word.Application
   Dim WordDoc As Word.Document
   Dim strLetter As String
   Dim strConnect As String
 
   ' Create an instance of Microsoft Word 2000.
   Set WordApp = CreateObject("Word.Application")
 
   ' Create a new, empty document.
'   Set WordDoc = WordApp.Documents.Open(filepath)
 Set WordDoc = WordApp.Documents.Add
  With WordDoc.MailMerge
        strConnect = "TABLE TT"
   .OpenDataSource _
        Name:="S:\File\Path\To\Database.mdb", _
         ReadOnly:=True, LinkToSource:=True, _
        Connection:=strConnect, _
       SQLStatement:="SELECT * FROM [TT] WHERE ID=" & Me.ID
 
  End With
 
   ' Define the body of the letter in the merge document.
   With WordApp.Selection
        .Font.Name = "Arial"
    .Font.Size = 16
        .Font.Bold = True
    .TypeText text:="                         Trouble Ticket Request"
      .TypeParagraph
      .TypeParagraph
    .Font.Size = 12
      .Font.Bold = True
      .TypeText text:="Last Name: "
      .Font.Bold = False
      .TypeText text:=Me.LastName & "       "
      .Font.Bold = True
      .TypeText text:="First Name: "
      .Font.Bold = False
      .TypeText text:=Me.FirstName & " "
      .Font.Bold = True
      .Font.Bold = True
      .TypeText text:="Organization: "
      .Font.Bold = False
      .TypeText text:=Me.Organization & " "
      .TypeParagraph
      .Font.Bold = True
      .TypeText text:="Role: "
      .Font.Bold = False
      .TypeText text:=Me.Role & " "
      .TypeParagraph
      .Font.Bold = True
      .TypeText text:="E-Mail Address: "
      .Font.Bold = False
      .TypeText text:=Me.EMailAddress & " "
      .TypeParagraph
      .Font.Bold = True
      .TypeText text:="Problem Description: "
      .TypeParagraph
      .Font.Bold = False
      .TypeText text:=Me.Problem & " "
      .TypeParagraph
      .Font.Bold = True
      .TypeText text:="Action Requested: "
      .TypeParagraph
      .Font.Bold = False
      .TypeText text:=Me.ActionRequested & " "
      .TypeParagraph
   End With
 
  With WordDoc.MailMerge
   .DESTINATION = wdSendToNewDocument
   .Execute
    .Application.Options.PrintBackground = False
 End With
 
   ' Show the instance of Microsoft Word.
  WordApp.Application.Documents.Save NoPrompt
   WordApp.Visible = False
    WordDoc.SaveAs filepath
   WordApp.Application.Quit
   WordApp.Visible = False
 
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
        Set OutMail = OutApp.CreateItem(0)
         
  With OutMail
         
          .To = "support@someemail.com"
          .Subject = "Trouble Ticket Request"
         .Attachments.Add (filepath)
          .Display
 
    End With
 
If fsObj.FileExists(filepath) = True Then
fsObj.deleteFile (filepath)
End If

Open in new window

Avatar of aanymg
aanymg
Flag of United States of America image

In your code above, do the modifications mentioned below and give it a try

Comment the following line  (Line no 86) .
WordApp.Application.Documents.Save NoPrompt

Comment the following line  (Line no 90) .
WordApp.Visible = False

Avatar of MDauphinais1
MDauphinais1

ASKER

It doesn't show Word anymore but I still get a prompt "Do you want to save Letter1 Yes/No". Then the e-mail opens with the atteched doc "TT_Request.doc" but when I double-click to open it I get the prompt:" Do you want to run the query SELECT * FROM [TT] WHERE ID=1 Yes/No"?

I'm not sure why it is trying to save a doc called Letter1 or why it isn't saving the data to the Word doc completely (I shouldn't be prompted to run the SQL query).
Can you attach your sample database and any word template your are using so that i can look into it
Here is the sample database. Open the TT form and click the button on it to test the code.

Please note: I have Office 2007 so my reference to MS Word is version 12. You may need to go into References and change it to version 11 if you are on Office 2003. I have tested it on both and they produce the same problems.

https://filedb.experts-exchange.com/incoming/ee-stuff/7079-Max-Order-No.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/6920-database_sample.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/6939-database_sample_win2000.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/6927-database_sample2.zip
 
I am sorry i may not be able to help you, because i dont have versions of the MS apps you are using. My version is still 2000
Should still work in 2000 also. Just change the Reference to whatever version of MS Word is available in the list.
I mean i cannot open the mdb file because yours is 2007. Does 2007 allow you to save file in older version. If so, pls save your mdb file in 2000 and send me again. i will look into it
It is in 2003 format. I just opened it with my 2003 version at work and it opened fine.

I have re-saved it with my 2003 version and uploaded a new copy. Please see if you can open it now.

https://filedb.experts-exchange.com/incoming/ee-stuff/6927-database_sample2.zip
Sorry, what i meant was that i have version 2000. So i cannot open the higher versions of access application.
Do the following
Open your access file and Save it in MS Access 2000 format and attach the file again.
MDauphinais1
thanks for the file.
Could you try the following and let me know the result

Comment the following line  (Line no 81) .
.Execute

The Word doc still opens up wanting to run the SQL query.
I am running the same code with all the above said modifications and i am unable to sumulate the problem.
ASKER CERTIFIED SOLUTION
Avatar of MDauphinais1
MDauphinais1

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial