Writing From VB to MS Word - - A form letter

[mgkSHA]

Hello:

I am using the current code to create mailing labels in Word directly from VB..it pulls personnel data straight from the data table and creates the 5160-style mailing labels:


Sub MailMergeLabels()

Dim LabelType As String
Dim SQL1 As String
Dim oAutoText As Object



Set oApp = CreateObject("Word.Application&quot
SQL1 = "SELECT [First], [Last], [ADR], [CITYIN], [STATEIN], [ZIPIN] FROM Master"
Set oDoc = oApp.Documents.Add

With oDoc.MailMerge


With .Fields
.Add oApp.Selection.Range, UCase("First&quot
oApp.Selection.TypeText " "
.Add oApp.Selection.Range, UCase("Last&quot
oApp.Selection.TypeParagraph
.Add oApp.Selection.Range, "Adr"
oApp.Selection.TypeParagraph
.Add oApp.Selection.Range, "Cityin"
oApp.Selection.TypeText " "
.Add oApp.Selection.Range, "Statein"
oApp.Selection.TypeText " "
.Add oApp.Selection.Range, "Zipin"
End With


Set oAutoText = oApp.NormalTemplate.AutoTextEntries.Add("MyLabelLayout", oDoc.Content)
oDoc.Content.Delete

.MainDocumentType = 1:
.OpenDataSource Name:=App.Path & "\OOTSPersonnel.mdb", SQLStatement:=SQL1, ReadOnly:=True


oApp.MailingLabel.CreateNewDocument Name:="5160", Address:="", _
AutoText:="MyLabelLayout", LaserTray:=wdPrinterManualFeed



.Destination = wdSendToNewDocument


oAutoText.Delete

End With

oDoc.Close False
oApp.Visible = True

oApp.NormalTemplate.Saved = True

End Sub

***********************************************************

now I am having a hard time getting this same data (the personal address and name info) into just a standard word document. Basically I want to create a word document with todays date, the employees contact data (from the recordsource) and maybe a couple strings such as "subject", etc...I want to build a form letter.

I have tried several renditions, but I can only get these mailing labels to work...I think I am missing something, but I don't know what. Can anyone tell me another way, or a way by modifying this current code that I can create a simple form letter? Thanks

 

[tty0]

If you are using a template / pre-prepared word doc with bookmarks you could use something like the following. It is a module used in an old program of mine that creates an invoice, just pass the function the correct variables and it will populate the doc.

'*********************************
Public Sub Bookmark(COMPANY, ADDRESS, POSTCODE, DESCRIPTION, COST, GROSS, VAT, DEPOSIT, NET)

'On Error Resume Next
Dim WordObj As Word.Application
Set WordObj = CreateObject("Word.Application&quot
Dim objWord As Word.Document
Set objWord = WordObj.Documents.Open(FileName:=App.Path & "\INVOICE.doc&quot
With objWord.Bookmarks
.Item("COMPANY&quot.Range.Text = COMPANY
.Item("ADDRESS&quot.Range.Text = ADDRESS
.Item("POSTCODE&quot.Range.Text = POSTCODE
.Item("DESCRIPTION&quot.Range.Text = DESCRIPTION
.Item("COST&quot.Range.Text = COST
.Item("GROSS&quot.Range.Text = GROSS
.Item("VAT&quot.Range.Text = VAT
.Item("DEPOSIT&quot.Range.Text = DEPOSIT
.Item("NET&quot.Range.Text = NET
End With
Set objWord = Nothing


End Sub
'************************************
cheers 'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

 

[mgkSHA]

See, right now, I do not have a bookmark or template. I am basically trying to open a blank document and pass the employee data to it, and add some other string values to make a form letter. But, what I am considering doing for this is to create an "EmployeeLetter.doc" document in the same folder as my application. So I can reference an existing object in my code App.Path & "\EmployeeLetter.doc"

So, if I create this blank letter head, how can I go from this point..again, I have made several attempts with the code I am using for mailing labels with little success.

I need something like this on a word document:

Date: Now() (I can do this )

To: rs.fields(0)
Address: rs.fields(1)
etc.

Dear: rs.fields(5), etc...

I have not done much with Word integration, but I am fluent with VB..if someone can lead me in the right direction, I'd be grateful...

P.S - tty0: could I say something like this to populate my word document:

.Item("To&quot.Range.Text = rs.fields(5) & " " & rs.fields(6)

 

[tty0]

I find that the best way to do it if you are using docs that change but use the same information is to use bookmarked word docs.


All you need to do is in the example you gave is to add a bookmark to the word doc called To. the document can be saved as a .doc

Then when the user wants to make a new document your help file just needs to show what bookmarks are needed.

'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

 

[mgkSHA]

I see, but how can I pass the data table value? I understand what you are saying. But say I want to send a letter to "Lorraine Smith"..Lorraine is a record in my data table, we will say for now it is the third and fourth field.

How do I pass the value to the "To" item on my bookmark:

To: = rs.fields(2) & " " & rs.fields(3)

this will be printed at run-time so, I need to pass values.

 

[tty0]

Sorry i meant to say that what you had put in is correct:

.Item("To&quot.Range.Text = rs.fields(3) & " " & rs.fields(4)

Would make the line something like below if your bookmark started one whitespace after the To: ...

To: Lorraine Smith 'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

 

[mgkSHA]

Hello:

Well, I got the overall functionality of it working, I picked up on the coding fairly quickly..but now, my question is, how do I format it correctly (again, sorry, I have little Word integration experience ). This is currently how the document prints: (on a single line

01/06/2003 Firstname Lastname Address City state zip


What I want is:

"Date:" 1/06/2003


firstname, lastname
Address
City"," State "," zip



"Dear:" Firstname, Lastname







"Sincerely:"


Two questions: 1)How can I space out my current data, how do you code link breaks 2) How can I code in my "strings" (the "sincerely", "dear", etc.) I just want to pass though string values at the proper locations.

This is the current code I have which provides me with the current one line of data I have:

************************************************************
Sub CreateLetter()

On Error Resume Next

Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim WordObj As Word.Application
Dim objWord As Word.Document

sSQL = "SELECT [ZIPIN], [STATEIN], [CITYIN], [ADR], [LAST], [FIRST] FROM Master WHERE SSNO = '215-92-4743'"
rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText

Set WordObj = CreateObject("Word.Application&quot
Set objWord = WordObj.Documents.Open(FileName:=App.Path & "\FormLetter.doc&quot


With objWord.Bookmarks
.Item("FirstName&quot.Range.Text = rs.Fields(0).Value & " "
.Item("LastName&quot.Range.Text = rs.Fields(1).Value & " "
.Item("Address&quot.Range.Text = rs.Fields(2).Value & " "
.Item("City&quot.Range.Text = rs.Fields(3).Value & " "
.Item("State&quot.Range.Text = rs.Fields(4).Value & " "
.Item("ZipCode&quot.Range.Text = rs.Fields(5).Value & " "
.Item("Date&quot.Range.Text = Date & " "
End With



WordObj.Visible = True
Set objWord = Nothing
WordObj.NormalTemplate.Saved = True
WordObj.Documents.Close

End Sub

 

[tty0]

Hi mgk,
To code a line break it is vbCrLf (vb control line feed) and string in the usual quotes, see below:

So if you wanted:-
***************************
"Date:" 1/06/2003


firstname, lastname
Address
City"," State "," zip
***************************

you would do

"Date" & date & vbCrLf & vbCrLf & _
rs.Fields(0).Value & ", " & rs.Fields(1).Value & vbCrLf & _
rs.Fields(2).Value & _
rs.Fields(3).Value & ", " & rs.Fields(4).Value & ", " & _
rs.Fields(5).Value

That would write the data straight to a blank word document. If you were using Bookmarks in your doc then it is a different matter as the points are already marked. Do you have an email address i can send you an example doc to?
'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

 

[mgkSHA]

I might be able to play with this now..but yes, I am using bookmarks. If you would like to send a code sample, I would appreicate that....genemkos@msn.com

Thanks,

 

[mgkSHA]

Now that I have added vbCrLf, my bookmarks won't save, and I get nothing...ACK! I have no clue why this is giving me such a headache . I would much rather ditch the bookmarks and write to Word directly, do you an example of that. I tried to pass a recordset to a standard Word.Application object and I got errors.

Here is the current code with the bookmarks which is not working...thanks again

************************************************************

Sub CreateLetter()

On Error Resume Next

Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim WordObj As Word.Application
Dim objWord As Word.Document

sSQL = "SELECT [ZIPIN], [STATEIN], [CITYIN], [ADR], [LAST], [FIRST] FROM Master WHERE SSNO = '745-09-8907'"
rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText

Set WordObj = CreateObject("Word.Application&quot
Set objWord = WordObj.Documents.Open(FileName:=App.Path & "\FormLetter.doc&quot


With objWord.Bookmarks

.Item("FirstName&quot.Range.Text = rs.Fields(5).Value & rs.Fields(6).Value & vbCrLf
.Item("Address&quot.Range.Text = rs.Fields(4).Value & vbCrLf
.Item("CityState&quot.Range.Text = rs.Fields(2).Value & " " & rs.Fields(3).Value & " " & rs.Fields(4).Value & vbCrLf
.Item("Date&quot.Range.Text = "Today's Date: " & Date & vbCrLf & vbCrLf
.Item("Dear&quot.Range.Text = rs.Fields(6) & " " & rs.Fields(5)

End With



WordObj.Visible = True
Set objWord = Nothing
WordObj.NormalTemplate.Saved = True


End Sub