Wednesday, March 28, 2012

how to create mail merge with word 2003 and sql server 2000 as database

hi, i have the following command and generated an error :
"word was unable to open the data source"

any help will be greatly appreciated.

Thanks in advance.

Jude

Private Sub Command1_Click()

Dim oMainDoc As Word.Document
Dim oSel As Word.Selection
Dim sDBPath As String

'Start a new main document for the mail merge.
Set oMainDoc = oApp.Documents.Add

With oMainDoc.MailMerge

.MainDocumentType = wdFormLetters

.OpenDataSource Name:="", _
connection:="DSN=JLU;DATABASE=PAMI_DB;UID=;pwd=;", _
sqlstatement:="select * from TBLUSERINFO"
'Add the field codes to the document to create the form letter.
With .Fields
Set oSel = oApp.Selection
.Add oSel.Range, "USERID"
oSel.TypeParagraph
.Add oSel.Range, "LASTNAME"
oSel.TypeParagraph
.Add oSel.Range, "FIRSTNAME"
oSel.TypeText ", "
.Add oSel.Range, "MI"
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText "Dear "
.Add oSel.Range, "FULLNAME"
oSel.TypeText ","
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText " This letter is to inform you..."
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText "Sincerely, [Your Name Here]"
End With
End With

'Perform the mail merge to a new document.
With oMainDoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute Pause:=False
End With

End SubMake sure your connection string is valid.|||hi, tried this one but no luck again. it gave no error but my computer does not have any activity.

With oMainDoc.MailMerge

.MainDocumentType = wdFormLetters
.OpenDataSource Name:="", _
connection:="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pami_db;Data Source=JLU", _
sqlstatement:="select * from TBLUSERINFO", SubType:=wdMergeSubTypeWord2000
With .Fields
Set oSel = oApp.Selection
.Add oSel.Range, "USERID"
oSel.TypeParagraph
.Add oSel.Range, "LASTNAME"
oSel.TypeParagraph
.Add oSel.Range, "FIRSTNAME"
oSel.TypeText ", "
.Add oSel.Range, "MI"
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText "Dear "
.Add oSel.Range, "FULLNAME"
oSel.TypeText ","
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText " This letter is to inform you..."
oSel.TypeParagraph
oSel.TypeParagraph
oSel.TypeText "Sincerely, [Your Name Here]"
End With
End With

With oMainDoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute Pause:=False
End With

No comments:

Post a Comment