[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Call a doc template

Posted on 2000-03-22
7
Medium Priority
?
249 Views
Last Modified: 2008-02-01
I have a form in VB6.  I have a combo box that displays a list of forms (10 forms).  

I need to mail merge based on which form is selected.  

2 Questions:
1.  How do I loop thru the combo box to see which letter is selected?  (can only select one letter)

2.  When I click 'send letter' I have it hard coded at this time.  Text and address etc, all hard coded.  How do I call the specific path based on the selected letter?

For example, I select Doc 3 from the combo box, when I click the command button, I want the template for doc 3 to come up.  How?

Thanks in advance....
-A
0
Comment
Question by:ambulance
  • 4
  • 3
7 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 2646958
I assume that your ComboBox Style is 2 - Dropdown List.

1)  You can find out which item in the list is selected by using the ListIndex property.  If it is -1, then nothing has been selected.  Otherwise, you can get the List entry by using this to reference the ittems in the list:

DocName$ = cmbDocuments.List(cmbDocuments.ListIndex)


2)  Assuming that the values in your combobox are the actual document names, then just put DocName$ in your string.  I don't know how you are opening the letter (there is more than one way to do it), but if your code has something like "C:\My Documents\Document1.Doc", and the ComboBox contained "Document1" as the item you picked, then you could use:

"C:\My Documents\" & DocName$ & ".Doc"

Or

"C:\My Documents\" & cmbDocuments.List(cmbDocuments.ListIndex) & ".Doc"

in its place.
0
 

Author Comment

by:ambulance
ID: 2650291
I am right now trying to open a specific template when the button is clicked.  Template is named 'Appendix_C.dot'  

I hard coded the data to be merged.  Merge fields do exist.  This is the code......

The way I have it now...it's calling the documetn, but ONLY displaying the merged fields, not the text of the document.  Does this mean I will have to hard code each letter?

Please help!!!!

Private Sub cmdSendLetter_Click()

    Dim wrdSelection    As Word.selection
    Dim wrdMailMerge    As Word.MailMerge
    Dim wrdMergeFields  As Word.MailMergeFields
    Dim StrToAdd        As String
    Dim DocName$
   
    On Error GoTo Error_Handler
   
    DocName$ = cbxLetter.List(cbxLetter.ListIndex)
   
    Screen.MousePointer = vbHourglass
       
    ' Create an instance of Word  and make it visible
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
   
    ' Add a new document
    Set wrdDoc = wrdApp.Documents.Add
    wrdDoc.Select
    Set wrdSelection = wrdApp.selection
    Set wrdMailMerge = wrdDoc.MailMerge
   
    ' Create MailMerge Data file
    CreateMailMergeDataFile
   
   
   
    ' Create a string and insert it into the document
    'StrToAdd = "Department of Agriculture" & vbCr & "Pet Animals Care and Facility"
    'wrdSelection.ParagraphFormat.Alignment = wdAlignParagraphCenter
    'wrdSelection.TypeText StrToAdd
    'InsertLines 4   ' Insert merge data
    'wrdSelection.ParagraphFormat.Alignment = wdAlignParagraphLeft

    Set wrdMergeFields = wrdMailMerge.Fields
    wrdMergeFields.Add wrdSelection.Range, "FirstName"
    wrdSelection.TypeText " "
    wrdMergeFields.Add wrdSelection.Range, "LastName"
    wrdSelection.TypeParagraph
    wrdMergeFields.Add wrdSelection.Range, "Address"
    wrdSelection.TypeParagraph
    wrdMergeFields.Add wrdSelection.Range, "City"
    wrdSelection.TypeText ", "
    wrdMergeFields.Add wrdSelection.Range, "State"
    wrdSelection.TypeText " "
    wrdMergeFields.Add wrdSelection.Range, "Zip"
   
    'InsertLines 2
   
    ' Right justify the line and insert a date field' with the current date
   ' wrdSelection.ParagraphFormat.Alignment = wdAlignParagraphRight
    'wrdSelection.InsertDateTime _
    'DateTimeFormat:="dddd, MMMM dd, yyyy", InsertAsField:=False
    'InsertLines 2
   
    ' Justify the rest of the document
    'wrdSelection.ParagraphFormat.Alignment = wdAlignParagraphJustify
    'wrdSelection.TypeText "Dear Shelter Manager "
    'InsertLines 2
   
    ' Create a string and insert it into the document
    'StrToAdd = "After careful review of your request for exemption from license fees for the Pet Animal Care " & _
                "Facilities Act (PACFA) program, we find that we are able to accept your request to waive the fee " & _
                "for the 1999 license year.  Therefore, you are not required to send the fee with your yearly " & _
                "application for the PACFA license.  " & vbCr & vbCr & _
                "The review for exemption takes into consideration the level of your operation, staffing " & vbCr & _
                "requirements, and available budget to determine whether the fee is overly burdensome.  " & vbCr & vbCr & _
                "You will be required to petition the Commissioner in writing for this exemption on a yearly basis, " & _
                "prior to licensing.  Your petition must include a copy of your budget and total operating expenses " & _
                "of the previous year.  " & vbCr & vbCr & _
                "Please contact our office if you have any questions concerning your exemption. " & _
                "Sincerely,   " & vbCr & vbCr & vbCr & vbCr & _
                "Keith Roehr, DVM " & vbCr & _
                "PACFA Veterinarian"

    'wrdSelection.TypeText StrToAdd
   
    ' Go to the end of the document
    wrdApp.selection.GoTo wdGoToLine, wdGoToLast
     
    ' Where to send the document?'
    wrdMailMerge.Destination = wdSendToNewDocument
'    wrdMailMerge.Destination = wdSendToEmail
'    wrdMailMerge.Destination = wdSendToFax
'    wrdMailMerge.Destination = wdSendToPrinter
   
    ' --- Perform MAIL MERGE --- '
    wrdMailMerge.Execute False
   
    wrdDoc.PrintPreview
       
    ' Close the original form document
    wrdDoc.Saved = True
'    wrdDoc.Close False
   
    ' Notify user we are done.
    MsgBox "Mail Merge Complete.", vbMsgBoxSetForeground
   
    ' Release References
    Set wrdSelection = Nothing
    Set wrdMailMerge = Nothing
    Set wrdMergeFields = Nothing
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
   
    ' Cleanup temp file
'    Kill "C:\DataDoc.doc"
    Screen.MousePointer = vbDefault
Exit Sub

Error_Handler:
    Screen.MousePointer = vbDefault
    MsgBox "Error: " & Err.Number & vbLf & vbLf & Err.Description, vbExclamation, "Mail Merge Error!"
End Sub



Public Sub InsertLines(LineNum As Integer)
    Dim iCount As Integer
    'INSERT BLANK LINES IN MS WORD
    For iCount = 1 To LineNum
        wrdApp.selection.TypeParagraph
    Next iCount
End Sub
   
Public Sub FillRow(Doc As Word.Document, Row As Integer, Text1 As String, Text2 As String, _
                   Text3 As String, Text4 As String, Text5 As String, Text6 As String)
                   
    With Doc.Tables(1)    ' Insert the data into the specific cell
        .Cell(Row, 1).Range.InsertAfter Text1
        .Cell(Row, 2).Range.InsertAfter Text2
        .Cell(Row, 3).Range.InsertAfter Text3
        .Cell(Row, 4).Range.InsertAfter Text4
        .Cell(Row, 5).Range.InsertAfter Text5
        .Cell(Row, 6).Range.InsertAfter Text6
    End With
End Sub

Public Sub CreateMailMergeDataFile()
    Dim wrdDataDoc  As Word.Document
    Dim X           As Integer
   
    ' Create a data source at C:\DataDoc.doc containing the field data
    wrdDoc.MailMerge.CreateDataSource Name:="C:\Pacfa\Documents\Templates\Appendix_C.doc", HeaderRecord:="FirstName, LastName, Address, City, State, Zip"
   
    ' Open the file to insert data
    Set wrdDataDoc = wrdApp.Documents.Open("C:\Pacfa\Documents\Templates\Appendix_C.doc")
    For X = 1 To 2
        wrdDataDoc.Tables(1).rows.Add
    Next X
   
    ' Fill in the data
    FillRow wrdDataDoc, 2, "James", "Brown", "4567 Main Street", "Grand Junction", "CO", "82541"
    FillRow wrdDataDoc, 3, "Jan", "Miksovsky", "1234 5th Street", "Vail", "CO", "85214"
    FillRow wrdDataDoc, 4, "Brian", "Valentine", "12348 78th Street  Apt. 214", "Denver", "CO", "85796"
   
    ' Save and close the file
    wrdDataDoc.Save
    wrdDataDoc.Close False
End Sub

0
 

Author Comment

by:ambulance
ID: 2650296
In the previous comment, I commented out the actual letter text so I can call a doc from the file.  

I haven't put in the .ListIndex bit to see which letter was selected from the drop down box.  Where would I put that?  I am not EXACTLY sure what the best location would be to do that.

This may not be hard for you experts, but I'm new and really struggling here.  Thanks so much for all your help!!!!

I will increase points!!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 32

Accepted Solution

by:
Brendt Hess earned 600 total points
ID: 2650419
Okay, so you have your template documents (1.dot, 2.dot, ...)

Using your code above:

Private Sub cmdSendLetter_Click()

    Dim wrdSelection    As Word.selection
    Dim wrdMailMerge    As Word.MailMerge
    Dim wrdMergeFields  As Word.MailMergeFields
    Dim StrToAdd        As String
    Dim DocName$
     
    On Error GoTo Error_Handler
     
    DocName$ = cbxLetter.List(cbxLetter.ListIndex)
     
    Screen.MousePointer = vbHourglass
         
    ' Create an instance of Word  and make it visible
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True

    ' Instead of adding a document, open the template document    
    Set wrdDoc = wrdApp.Documents.Open "C:\My Documents\Templates\" & DocName, False, True, False, , , True   ' Change path as needed
        ' parameters are:
        '   ConfirmConversions = False  -- If doc needs to be converted, just do it
        '   ReadOnly = True   -- If you need to save the document, do a Save As
        '   AddToRecentFiles = False    -- you don't need all your templates in the recent files list
        '   PasswordDocument, PasswordTemplate - Ignored
        '   Revert = True    - if doc is already open, revert to saved version on disk and lose changes
       
    wrdDoc.Select
    Set wrdSelection = wrdApp.selection
    Set wrdMailMerge = wrdDoc.MailMerge
     
    ' Create MailMerge Data file
    CreateMailMergeDataFile
     
    Set wrdMergeFields = wrdMailMerge.Fields
    wrdMergeFields.Add wrdSelection.Range, "FirstName"
    wrdSelection.TypeText " "
    wrdMergeFields.Add wrdSelection.Range, "LastName"
    wrdSelection.TypeParagraph
    wrdMergeFields.Add wrdSelection.Range, "Address"
    wrdSelection.TypeParagraph
    wrdMergeFields.Add wrdSelection.Range, "City"
    wrdSelection.TypeText ", " 
    wrdMergeFields.Add wrdSelection.Range, "State"
    wrdSelection.TypeText " "
    wrdMergeFields.Add wrdSelection.Range, "Zip"
     
wrdSelection.ParagraphFormat.Alignment = wdAlignParagraphRight
     
    ' Go to the end of the document
    wrdApp.selection.GoTo wdGoToLine, wdGoToLast
       
    ' Where to send the document?'
    wrdMailMerge.Destination = wdSendToNewDocument
'    wrdMailMerge.Destination = wdSendToEmail
'    wrdMailMerge.Destination = wdSendToFax
'    wrdMailMerge.Destination = wdSendToPrinter
     
    ' --- Perform MAIL MERGE --- '
    wrdMailMerge.Execute False
     
    wrdDoc.PrintPreview
         
    ' Close the original form document
    wrdDoc.Saved = True
'    wrdDoc.Close False
     
    ' Notify user we are done.
    MsgBox "Mail Merge Complete.", vbMsgBoxSetForeground
     
    ' Release References
    Set wrdSelection = Nothing
    Set wrdMailMerge = Nothing
    Set wrdMergeFields = Nothing
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
     
    ' Cleanup temp file
'    Kill "C:\DataDoc.doc"
    Screen.MousePointer = vbDefault
Exit Sub

Error_Handler:
    Screen.MousePointer = vbDefault
    MsgBox "Error: " & Err.Number & vbLf & vbLf & Err.Description, vbExclamation, "Mail Merge Error!"
End Sub



Public Sub InsertLines(LineNum As Integer)
    Dim iCount As Integer
    'INSERT BLANK LINES IN MS WORD
    For iCount = 1 To LineNum
        wrdApp.selection.TypeParagraph
    Next iCount
End Sub
     
Public Sub FillRow(Doc As Word.Document, Row As Integer, Text1 As String, Text2 As String, _
                   Text3 As String, Text4 As String, Text5 As String, Text6 As String)
                   
    With Doc.Tables(1)    ' Insert the data into the specific cell
        .Cell(Row, 1).Range.InsertAfter Text1
        .Cell(Row, 2).Range.InsertAfter Text2
        .Cell(Row, 3).Range.InsertAfter Text3
        .Cell(Row, 4).Range.InsertAfter Text4
        .Cell(Row, 5).Range.InsertAfter Text5
        .Cell(Row, 6).Range.InsertAfter Text6
    End With
End Sub

Public Sub CreateMailMergeDataFile()
    Dim wrdDataDoc  As Word.Document
    Dim X           As Integer
     
    ' Create a data source at C:\DataDoc.doc containing the field data
    wrdDoc.MailMerge.CreateDataSource Name:="C:\Pacfa\Documents\Templates\Appendix_C.doc", HeaderRecord:="FirstName, LastName, Address, City, State, Zip"
     
    ' Open the file to insert data
    Set wrdDataDoc = wrdApp.Documents.Open("C:\Pacfa\Documents\Templates\Appendix_C.doc")
    For X = 1 To 2
        wrdDataDoc.Tables(1).rows.Add
    Next X
     
    ' Fill in the data
    FillRow wrdDataDoc, 2, "James", "Brown", "4567 Main Street", "Grand Junction", "CO", "82541"
    FillRow wrdDataDoc, 3, "Jan", "Miksovsky", "1234 5th Street", "Vail", "CO", "85214"
    FillRow wrdDataDoc, 4, "Brian", "Valentine", "12348 78th Street  Apt. 214", "Denver", "CO", "85796"
     
    ' Save and close the file
    wrdDataDoc.Save
    wrdDataDoc.Close False
End Sub

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

Try this - this should do what you need.
0
 

Author Comment

by:ambulance
ID: 2650641
Adjusted points from 100 to 150
0
 

Author Comment

by:ambulance
ID: 2650642
You were a HUGE help!! Thank you so much!

-A
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 2651059
No problem - glad I could help
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question