Hi everyone I have a command button which when clicked, a mail merge using a word template occurs, the only thing is the code which is set for the command button has the default path of were the location of the word mail merge doc is,however if for sum reason the location was changed it would not find it, is there anyway it can look up a value in a text box and use that value? also I would need it to have liek a history list of the locations?
here is the code for the command button:
Public Sub sbMergeIt()
Dim strPath As String
'Create the object variables need
'Make sure there is a reference to MS Word
Dim objWordDoc As Word.Document
Dim objWordApp As Word.Application
'Set the variable that holds the path to the files used in the procedure
'Change it to match your path \\Stealth\DB_BACKEND\
'strPath = "C:\Documents and Settings\Administrator\My Documents\Access Databases\"
strPath = "\\Stealth\DB_BACKEND\"
'Export the data from the selected query. Notice the file name is not the same as the word document we are trying to merge it with
DoCmd.TransferText acExportMerge, , Me.cboSelectMailOut, strPath & "MailOutMergeData.txt", True
'Create our object variable
Set objWordApp = CreateObject("Word.Application")
'Open the word document
Set objWordDoc = objWordApp.Documents.Open(strPath & "MailOutList.doc")
objWordApp.ActiveDocument.MailMerge.OpenDataSource Name:=strPath & "MailOutMergeData.txt", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1:=""
'Used to refresh the data in the document
'We have seen that word would remember the previous information
'and if we didn't toggle you could get the data from the
'previous letter
objWordApp.ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
objWordApp.ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
'Send the letters to the printers merged with the records
objWordApp.ActiveDocument.MailMerge.Destination = wdSendToPrinter
'If you want it to be automatic, then uncomment the next 3 lines and comment the visible line
objWordApp.Visible = True
objWordApp.ActiveDocument.MailMerge.Execute
objWordApp.Quit wdDoNotSaveChanges
Set objWordApp = Nothing
End Sub
Private Sub cmd_PrevMail_Click()
Call sbMergeIt
MsgBox "List exported to printer"
End Sub
this is the line were it gets the location:
strPath = "\\Stealth\DB_BACKEND\"
please let me know thanks.
by: flavoPosted on 2004-08-03 at 01:10:43ID: 11701448
You can do a test to see if it does exist
Function pathExists(sPath As String) As Boolean
If Len(Dir(sPath)) > 0 Then
'it exists
pathExists = True
Else
'it doesn't exist
pathExists = False
End If
End Function
then maybe you could use this (it will look for the directory using the function above, if it doesnt exist, it will do a pop-up box asking for it)
If Not pathExists(strPath) Then
strPath = cStr(InputBox("Enter the path"))
End if
Dave