Word Mail Merge through Access, small error

[sillykid]

My code is as follows:

Function RunWordMacro()
Dim WordApp As Object
Dim WordDoc As Object
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Open _
("S:\MGMTSRV\INDIVIDUAL STAFF PROJECTS\MSwagle1\mailmerge.docm")
WordApp.Visible = True

With WordApp.ActiveWindow

.Selection.WholeStory
.Selection.TypeText Text:="Dear Dr. "
.ActveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""LAST_NAME"""

.Selection.TypeText Text:=","
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=vbTab
.Selection.TypeText Text:="Your first surgery is on "
.ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""MinOfSCHED_CASE_START"""
.Selection.TypeText Text:="."
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:="Thank You."
With .ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
End With

End With
Set WordApp = Nothing
End Function
​

I'm trying to automate a Word Mail Merge through access. I keep getting the error 424 on the lines with wd in them. I've read in other threads that you go to Tools and References to fix this, but I can't click References. Please let me know.

Thanks!

 

[boblarson]

. I keep getting the error 424 on the lines with wd in them. I've read in other threads that you go to Tools and References to fix this, but I can't click References. Please let me know.

Thanks!

No, it isn't a reference problem - well, it sort of is, but it is that those are Word Constants and if you use late binding, which you are doing, then you need to provide the values for them like:

Const wdFieldMergeField As Long = 59

You can get the constants by just going to the VBA designer in Word and then type this in the Immediate Window:

?wdFieldMergeField

and hit enter.

 

[sillykid]

No, it isn't a reference problem - well, it sort of is, but it is that those are Word Constants and if you use late binding, which you are doing, then you need to provide the values for them like:

Const wdFieldMergeField As Long = 59

You can get the constants by just going to the VBA designer in Word and then type this in the Immediate Window:

?wdFieldMergeField

and hit enter.

Thanks for your help. It says my macros aren't enabled, even though I'm pretty sure they are. How else could I find them? and where in the code do I put that line? I'm very new to this (Just started yesterday). Thanks again for your prompt response.

 

[boblarson]

If on 2007:
http://www.btabdevelopment.com/ts/default.aspx?PageId=13

If on 2010:
http://www.btabdevelopment.com/ts/2010TL

 

[sillykid]

ok, I enabled everything.

Where in the code do I put the line "wdFieldMergeField As Long = 59"? and is that the only thing I need to add?

 

[boblarson]

You put it in the beginning of your procedure, or if you want to use it throughout your database then you would put it in the General Declarations section of a standard module (not form, report, or class module).

And you forgot part - CONST

Const wdFieldMergeField As Long = 59

And no, you would need to add for any of the Word Constants you are using. The othes I see are:
wdDefaultFirstRecord
wdDefaultLastRecord

 

[sillykid]

I'm sorry, I still don't understand where to put it.

using the ? thing, I found:

?wdSendToEmail
2
?wdDefaultFirstRecord
1
?wdDefaultLastRecord
-16

Thank you for your patience.

 

[boblarson]

I'm sorry, I still don't understand where to put it.

You put them right after this:

Function RunWordMacro()

like this:

Function RunWordMacro()
Const wdFieldMergeField As Long = 59



if you want them to be only available for use in that procedure.

If you want them to be available throughout the entire database so you can have other code which does Word programming too, then you would go to an existing standard module and put it here:


Option Compare Database
Option Explicit

Const wdFieldMergeField As Long = 59

 

[sillykid]

My code is now
[q]
Function RunWordMacro()
Const wdFieldMergeField As Long = 59
Const wdSendToEmail As Long = 2
Const wdDefaultFirstRecord As Long = 1
Const wdDefaultLastRecord As Long = -16
Dim WordApp As Object
Dim WordDoc As Object
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Open _
("S:\MGMTSRV\INDIVIDUAL STAFF PROJECTS\MSwagle1\mailmerge.docm")
WordApp.Visible = True

With WordApp.ActiveWindow

.Selection.WholeStory
.Selection.TypeText Text:="Dear Dr. "
.ActveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""LAST_NAME"""

.Selection.TypeText Text:=","
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=vbTab
.Selection.TypeText Text:="Your first surgery is on "
.ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""MinOfSCHED_CASE_START"""
.Selection.TypeText Text:="."
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:="Thank You."
With .ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
End With

End With
Set WordApp = Nothing
End Function[/q]

but it's not working. It starts to make the mail merge, but stops at Dear Dr. The 424 error is still popping up. After I run it and hit Debug, the line
[q] ActveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""LAST_NAME"""[/q]
is highlighted.

 

[sillykid]

You put them right after this:

Function RunWordMacro()

like this:

Function RunWordMacro()
Const wdFieldMergeField As Long = 59

Hello, I've edited it again and still am seeking your help. I'm having an issue calling the database.

Public Function RunWordMacro()
Const wdFieldMergeField As Long = 59
Const wdSendToEmail As Long = 2
Const wdDefaultFirstRecord As Long = 1
Const wdDefaultLastRecord As Long = -16
Dim WordApp As Object
Dim WordDoc As Object
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Open _
("S:\MGMTSRV\INDIVIDUAL STAFF PROJECTS\MSwagle1\mailmerge.docm")
WordApp.Visible = True

With WordApp.ActiveWindow
.ActiveDocument.MailMerge.OpenDataSource Name:= _
"S:\MGMTSRV\INDIVIDUAL STAFF PROJECTS\MSwagle1\Test Database.accdb", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=S:\MGMTSRV\INDIVIDUAL STAFF PROJECTS\MSwagle1\Test Database.accdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDBataba" _
, SQLStatement:="SELECT * FROM `Table_Earliest_Start`", SQLStatement1:="" _
, SubType:=wdMergeSubTypeAccess
.Selection.WholeStory
.Selection.TypeText Text:="Dear Dr. "
.ActveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""LAST_NAME"""
.Selection.TypeText Text:=","
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=vbTab
.Selection.TypeText Text:="Your first surgery is on "
.ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""MinOfSCHED_CASE_START"""
.Selection.TypeText Text:="."
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:="Thank You."
With .ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
End With

End With
Set WordApp = Nothing
End Function