Mail merge then save document as str name

[spnz]

Hi.
I am having problems trying to get some code working that merges data from my db into a word document then saves the document as a strings value.

Heres my code

Dim objword As Word.Application
    Set objword = CreateObject("word.application")
    objword.Visible = True
    objword.Documents.Add ("c:\shane\contract2.doc") ' Location of word file
    Dim strSaveAs As String
    Dim strPath As String
    Dim strContract As String
    
    strPath = "C:\Shane\"
    strSaveAs = Forms![frmMain]![txtTempName] & "\"
    strContract = "Contract" & " " & Date & ".doc"
    
    Debug.Print strPath & strSaveAs & strContract


''''autocompletes txtdate
If Me.chkContractSent.Value = -1 Then
Me.txtDateSent.Value = Date
 On Error Resume Next
  
  
  ''check for null
 ' If IsNull(txtAddress1) Then
  '   MsgBox "First line of address cannot be empty"
   '  Me.txtAddress1.SetFocus
    ' Exit Sub
   'End If

    ' If IsNull(txtJobTitle) Then
     'MsgBox "Job Title cannot be empty"
     'Me.txtAddress1.SetFocus
     'Exit Sub
   'End If


    'If IsNull(Pay_Rate) Then
     'MsgBox "PayRate cannot be empty"
     'Me.txtAddress1.SetFocus
     'Exit Sub
   'End If
  
  
    'If IsNull(dtmStartDate) Then
     'MsgBox "Start Date cannot be empty"
     'Me.txtAddress1.SetFocus
     'Exit Sub
   'End If
   
   
  '  If IsNull(Temp_Name) Then
   '  MsgBox "Name Field cannot be empty"
    ' Me.txtAddress1.SetFocus
     'Exit Sub
   'End If
   
 
objword.ActiveDocument.Bookmarks("address1").Select
objword.Selection.Text = Forms![frmMain]![txtAddress1]
objword.ActiveDocument.Bookmarks("address2").Select
objword.Selection.Text = Forms![frmMain]![txtAddress2]
objword.ActiveDocument.Bookmarks("address3").Select
objword.Selection.Text = Forms![frmMain]![txtAddress3]
objword.ActiveDocument.Bookmarks("address4").Select
objword.Selection.Text = Forms![frmMain]![txtAddress4]
objword.ActiveDocument.Bookmarks("PostCode").Select
objword.Selection.Text = Forms![frmMain]![txtPostCode]
objword.ActiveDocument.Bookmarks("JOBTITLE").Select
objword.Selection.Text = Me.txtJobTitle
objword.ActiveDocument.Bookmarks("Name").Select
objword.Selection.Text = Forms![frmMain]![txtTempName]
objword.ActiveDocument.Bookmarks("STARTDATE").Select
objword.Selection.Text = Me.dtmStartDate
objword.ActiveDocument.Bookmarks("PAYRATE").Select
objword.Selection.Text = Me.curPayRate
objword.ActiveDocument.Bookmarks("DATE").Select
objword.Selection.Text = Date

'objword.ActiveDocument.Close wdSaveChanges:=(strPath & strSaveAs & strContract)
objword.ActiveDocument.SaveAs FileName:=strPath & strSaveAs & strContract
End If

The merge part working fine but the saving part doesn't
I have tried loads of different methods to get the document to save but nothing is working.

Can anyone see what I am doing wrong?


Thanks for your help

 

[adaniele]

hi spnz , the following code worked for me:
strfieldname is the name to be used to save the new word doc.

in the with appword section, merge a template with the sql in a new doc. Then, closes the template without saving it and after that, it saves the new file and closes wrod.

        strFileName = "s:\qf_space\wine\pops\IFSGW" & Me.ifsnumber.Value & "_Item" & Me.winecode.Value & ".doc"
        
        'Set the merge data source to the SQL statement, and do the merge
        strDBName = DBNAME
        strSQL = SQL
       
       With appWord
          .activedocument.MailMerge.OpenDataSource Name:=strDBName, _
             LinkToSource:=True, SQLStatement:=strSQL
          .activedocument.MailMerge.Destination = wdSendToNewDocument
          .activedocument.MailMerge.Execute
          .Documents(strWordDoc).Close savechanges:=wddonotsavechanges
          .activedocument.SaveAs FileName:=strFileName, FileFormat:=wdFormatDocument
          .activedocument.Close savechanges:=wddonotsavechanges
          .Quit
       End With

i hope this hlp. max.