Problems mail merging

[aronw]

(Hope I'm the correct forum)

I have a template (word dot.) with field names in all the right places. I want to create a button on my form that when clicked will throw open this word template and populate the fields with the currently active Access record.

However I am unsure of how do this. If anyone can help I would be very greatful.

TIA

Aron

 

[chrismcbride]

Attached is some code I use to make a fax cover sheet from one of the forms in my database. It gets some values straight from the form and looks up some other values. It uses a template in the same way as you decide. This will work for one record. If you have multiple records that you want to print, then you need a different method.

Repost or email if you have question...
Chris

 

[aronw]

Thanks,

It sounds great! However i'm currently working for a company on a 2 week contract and it takes 4 working days to have WinZip added to the NT / Novell node i'm working on! (I don't have that) Installing stuff for myself is not an option sadly, tighter than a ducks ....

So please any more help would be bordering on magnificent,

As usual TIA

Aron

 

[chrismcbride]

Sorry. The initial file is a word doc, which this forum does not allow as an attachment. Here is the code...

Private Sub MakeFax()
On Error GoTo EH

Dim cont_full_name As String
Dim word_Up As Boolean
Dim template_path As String
Dim file_path As String
Dim wrd As Object
Dim template_present As String
Dim rngBookmark As Word.Range
Dim doc As Word.Document
Dim rng As Word.Range
Dim save_as_name As String
Dim path_date As String
Dim phone As String
Dim fax As String
Dim email As String
Dim saved_path As String

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Check to see if Word is open. If not, open it
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
Set wrd = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set wrd = CreateObject("Word.Application")
End If
On Error GoTo 0

template_path = "C:\MeridianApps\Meridian_Fax.dot"

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Check for the template that must be present in order to make this document
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

template_present = Nz(Dir(template_path))
If template_present = "" Then
MsgBox "Template for the Meridian Fax Cover is not present in the MeridianApps " _
& "Folder. Without this Template the Fax Cover can not be generated.", vbOKOnly, _
"Missing Template"
Exit Sub
End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Make the instance of Word visible and set an object ariable using the path to the Template
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

wrd.Visible = True
Set doc = wrd.Documents.Add(Template:=template_path)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' set the Database variable,
' Set up the recordset and fill up some handy variable to use to populate the bookmarks
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


path_date = Format(Now(), "dd-mm-yy")

save_as_name = "C:\MeridianApps\FaxCover_"
save_as_name = save_as_name & Me.cboContact.Column(1)
save_as_name = save_as_name & "_"
save_as_name = save_as_name & path_date
save_as_name = save_as_name & ".doc"

cont_full_name = IIf(IsNull(Me.cboContact.Column(1)), " ", Me.cboContact.Column(1))

fax = Format(DLookup("cont_fax", "tblSupContact", "cont_no = " _
& Me.cboContact), "(000) ###-####")
phone = Format(DLookup("cont_phone", "tblSupContact", "cont_no = " _
& Me.cboContact), "(000) ###-####")

If Me.cboEmployee > 0 Then
email = DLookup("email", "tblEmployee", "emp_no = " & Me.cboEmployee)
Else
email = "Null"
End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' using the doc object, fill the bookmarks with form and recordset values
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If email <> "Null" Then
If doc.Bookmarks.Exists("emp_email") Then
Set rng = doc.Bookmarks("emp_email").Range
rng.text = email
doc.Bookmarks.Add name:="emp_email", Range:=rng
Else
Set rng = doc.Range
rng.Collapse wdCollapseStart
End If
End If

If cont_full_name <> "Null" Then
If doc.Bookmarks.Exists("cont_full_name") Then
Set rng = doc.Bookmarks("cont_full_name").Range
rng.text = cont_full_name
doc.Bookmarks.Add name:="cont_full_name", Range:=rng
Else
Set rng = doc.Range
rng.Collapse wdCollapseStart
End If
End If

If IsNull(Me.cboEmployee) = False Then
If doc.Bookmarks.Exists("employee") Then
Set rng = doc.Bookmarks("employee").Range
rng.text = IIf(IsNull(Me.cboEmployee.Column(1)), " ", Me.cboEmployee.Column(1))
doc.Bookmarks.Add name:="employee", Range:=rng
Else
Set rng = doc.Range
rng.Collapse wdCollapseStart
End If
End If

If fax <> "Null" Then
If doc.Bookmarks.Exists("cont_fax") Then
Set rng = doc.Bookmarks("cont_fax").Range
rng.text = fax
doc.Bookmarks.Add name:="cont_fax", Range:=rng
Else
Set rng = doc.Range
rng.Collapse wdCollapseStart
End If
End If

If phone <> "Null" Then
If doc.Bookmarks.Exists("cont_phone") Then
Set rng = doc.Bookmarks("cont_phone").Range
rng.text = phone
doc.Bookmarks.Add name:="cont_phone", Range:=rng
Else
Set rng = doc.Range
rng.Collapse wdCollapseStart
End If
End If

If doc.Bookmarks.Exists("date_now") Then
Set rng = doc.Bookmarks("date_now").Range
rng.text = Format(Now(), "Medium Date")
doc.Bookmarks.Add name:="date_now", Range:=rng
Else
Set rng = doc.Range
rng.Collapse wdCollapseStart
End If

If IsNull(Me.regarding) = False Then
If doc.Bookmarks.Exists("regarding") Then
Set rng = doc.Bookmarks("regarding").Range
rng.text = Me.regarding
doc.Bookmarks.Add name:="regarding", Range:=rng
Else
Set rng = doc.Range
rng.Collapse wdCollapseStart
End If
End If

If IsNull(Me.cc) = False Then
If doc.Bookmarks.Exists("cc") Then
Set rng = doc.Bookmarks("cc").Range
rng.text = Me.cc
doc.Bookmarks.Add name:="cc", Range:=rng
Else
Set rng = doc.Range
rng.Collapse wdCollapseStart
End If
End If

If IsNull(Me.letter_body) = False Then
If Me.fraType = 1 Then
If doc.Bookmarks.Exists("comments") Then
Set rng = doc.Bookmarks("comments").Range
rng.text = "Comments:" & vbCrLf & Me.letter_body
doc.Bookmarks.Add name:="comments", Range:=rng
Else
Set rng = doc.Range
rng.Collapse wdCollapseStart
End If
End If
End If

doc.Fields.Update

saved_path = Nz(Dir(save_as_name))
If saved_path <> "" Then
save_as_name = Mid(save_as_name, 1, Len(save_as_name) - 4)
save_as_name = save_as_name & "_"
save_as_name = save_as_name & DatePart("n", Now())
save_as_name = save_as_name & DatePart("s", Now())
save_as_name = save_as_name & ".doc"
End If

doc.SaveAs save_as_name

If Me.fraType = 1 Then
MsgBox "Procedure Completed.", vbOKOnly, "Word Report Generated"
End If

wrd.Activate

SeeYa:
Set wrd = Nothing
Set rngBookmark = Nothing
Set doc = Nothing
Set rng = Nothing
Exit Sub

EH:
MsgBox Err.Number & " - " & Err.description
Resume SeeYa
End Sub


---------------------

Enjoy!
Chris

 

[aronw]

As I may or may not have mentioned, i'm new to access.

Thanks for the post but what do I do with it?

Can I just insert it somewhere?

TIA

Aron

 

[chrismcbride]

If you are all that new, then you may have a bit of trouble with this code. Basically you need it in an Event (likely a button click event). Once you have decided how to fire this procedure, you will have to edit it to fit your needs. In order to do this you will need to go through this code and get an idea of what is going on. If you have specific questions, give me a shout...

Chris

 

[aronw]

parameter query?

Would it be simpler or possible to achieve through the use of a parameter query?

If so, does anyone know how?

TIA
Aron

 

[chrismcbride]

You may need to use a Parameter query to get the information that you want to place into the template. But you will still need to use some sort of automation procedure (like the one I posted) in order to interface with Word, I think.
Chris