Solved

How to automate MailMerge in word

Posted on 2004-08-27
30
569 Views
Last Modified: 2008-02-07
HI,

  I am tyring to use Mail Merge and i need help doing this. I found article in Microsoft site, but i could not able to accomplish the task.

I would like to create a Letterform which generates by clikcing button with all the info from SQL Query string.

0
Comment
Question by:puja24
  • 14
  • 13
  • 2
30 Comments
 
LVL 4

Expert Comment

by:computerg33k
ID: 11917708
   Private Sub CreateMailMergeDataFile()
        Dim wrdDataDoc As Word.Document

        Dim iCount As Integer
        ' Create a data source at C:\DataDoc.doc containing the field data.
        wrdDoc.MailMerge.CreateDataSource(Name:="C:\DataDoc.doc", _
              HeaderRecord:="1")
        ' Open the file to insert data.
        wrdDataDoc = wrdApp.Documents.Open("C:\DataDoc.doc")
        For iCount = 1 To 0
            wrdDataDoc.Tables.Item(1).Rows.Add()
        Next iCount
        'Put info you want to merge here

                Dim null As String = ""
                ' Fill in the data.
                FillRow(wrdDataDoc, 2, lbl of info above)
                FillRow(wrdDataDoc, 3, lbl of info above)
                FillRow(wrdDataDoc, 4, lbl of info above)
                FillRow(wrdDataDoc, 5, lbl of info above)
                End If
        End If
        ' Save and close the file.
        wrdDataDoc.Save()
        wrdDataDoc.ActiveWindow.Close()
    End Sub

Private Sub WordReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WordReport.Click
        WaitCursor()
        Dim objJobGroup As New ExcelJobGroup()
        Dim objJob As New ExcelJob()
        Dim wrdSelection As Word.Selection
        Dim wrdMailMerge As Word.MailMerge
        Dim wrdMergeFields As Word.MailMergeFields

        ' Create an instance of Word  and make it visible.
        wrdApp = CType(CreateObject("Word.Application"), Word.Application)
        wrdApp.Visible = True

        ' Add a new document.
        wrdDoc = wrdApp.Documents.Add()
        wrdDoc.Select()

        wrdSelection = wrdApp.Selection()
        wrdMailMerge = wrdDoc.MailMerge()

        ' Create MailMerge Data file.
        CreateMailMergeDataFile()

                        wrdSelection.PageSetup.LeftMargin = 65
                        wrdSelection.PageSetup.RightMargin = 30
                        wrdSelection.PageSetup.TopMargin = 50

                        Dim oPara As Word.Paragraph

                        oPara = wrdDoc.Content.Paragraphs.Add(wrdDoc.Bookmarks.Item("\endofdoc").Range)
                        oPara.Range.Text = CType(selWord.Text, String)

                        oPara.Range.Bookmarks.Add("IV")
                        oPara = wrdDoc.Content.Paragraphs.Add(wrdDoc.Bookmarks.Item("IV").Range)
                        wrdApp.Selection.GoTo(What:=Word.WdGoToItem.wdGoToBookmark, Name:="IV")
                        ' Variable t use to type the text
                        selWord = wrdApp.Selection()
                        ' With Block with the text you wanted
                        With selWord
                            ' Chosen font
                            .Font.Name = "Times New Roman"
                            ' Font size
                            .Font.Size = 10
                            ' Normal text
                            .Font.Bold = 0
                            ' Normal text to start
                            .TypeText("IV.    The most diligent inquiry, the place of residence of the aforesaid Defendants cannot be ascertained and/or their last known place of residence is: ")
                            ' Bold font
                            .Font.Bold = 1
                            ' Recipientsname
                            .TypeText(rAddressString)
                            ' Normal font
                            .Font.Bold = 0
                        End With

            ' Go to the end of the document.
            wrdApp.Selection.GoTo(Word.WdGoToItem.wdGoToLine, _
                       Word.WdGoToDirection.wdGoToLast)

            ' Perform mail merge.
            wrdMailMerge.Destination = _
                       Word.WdMailMergeDestination.wdSendToNewDocument
            wrdMailMerge.Execute(False)

            ' Close the original form document.
            wrdDoc.Saved = True
            wrdDoc.ActiveWindow.Close()

            ' Release References.
            wrdSelection = Nothing
            wrdMailMerge = Nothing
            wrdMergeFields = Nothing
            wrdDoc = Nothing
            wrdApp = Nothing

            ' Clean up temp file.
            System.IO.File.Delete("C:\DataDoc.doc")
        End If
    End Sub


This is using Word object office 11--you have to add the word object office to your references as well
0
 
LVL 1

Expert Comment

by:Androminos
ID: 11941143
'Here's a simple solution I used that works great. I use a stored procedure.

    Dim strSQL as String
    strSQL = "Exec mySQLStoredProcedureNameHere 'Variable1', 'Variable2'"
    Dim objWord As Word.Application
    Set objWord = New Word.Application
    With objWord
        .Application.Visible = True
        .Documents.Open ("PathToDocument\DocNameHere.doc")
        .Documents(1).Activate
        'You don't necessarily need to fill in a name.  Just make sure the connection string is valid.
        .ActiveDocument.MailMerge.OpenDataSource Name:="", _
            Connection:="DSN=SQLUserDSN;UID=UserIDGoesHere;PWD=PasswordGoesHere" _
            , SQLStatement:=strSQL
        Set objWord = Nothing
    End With
0
 

Author Comment

by:puja24
ID: 11942456
Hi,
  First i would like to say thanks for the reply.   I got some resources from MSDN library and used to write Mailmerge function in VB.  The code generates the Letter form but i am looking for is to create a web based form so user can click the button and it would create a letter form using SQL connection string.

How can i accomplish this?  
0
 

Author Comment

by:puja24
ID: 11964974
Hi,

 I already have VB program which generates a Letter form once you run the program.  However, I want this to use in Web browser so user can click the button and can generate letter form by just clikcing button from web browse.

How can i make this accomplish since i'm new to Visual Basic program?

0
 
LVL 1

Expert Comment

by:Androminos
ID: 11965112
With the various security changes being made, getting an .exe to run consistently from a Web browser with all operating systems is going to be next to impossible.  Security systems don't generally like web apps launching executables.  I would opt for a non-web based solution.  But, to get a web-based solution to work, I believe you would need to have Word licensed and installed on the Web server in question and then transplant your code (with modifications) from your VB .exe to VBScript on webpage.  I haven't tried it, so I'm not sure it is a viable option, because it would still involve initiating a new instance of an application (Word) from the web.  You'd also have to check on licensing issues.  Best of luck!
0
 

Author Comment

by:puja24
ID: 11974840
Hi,

  I am not understanding your question. I already have Visucal Basic program which runs as command clikc and generates the lette form.

what i would like to do accomplish is that, User can see the Form and can able to click the Button to generate the letter form.

how can i make this possible?

Also, if you know any company that already has somthing like this build, can you please recommand it? I rather buy somthing that does this.

Thanks
0
 
LVL 1

Expert Comment

by:Androminos
ID: 11975271
Perhaps it is I who does not understand your question.  Your initial question did not mention anything about this being web-based, so I interpretted your question as to be regarding the vb code necessary to dynamically create a Word-based merge letter (which necessarily means launching Word--i.e. running an .exe via the Word object model).  I see that some others contributed similar code samples.  Your subsequent post indicated you wanted this to be web-based.  I interpretted that as meaning you wanted the user to see a button on the web-based application and click that button, resulting in a Word-based merge letter being created, (either directly or via your custom vb app), which would again entail launching an .exe from the Web-based application.  My response to that was to indicate my belief that there are inherent difficulties in launching an .exe (either your custom VB application or Word) from your web application due to security considerations.  If these answers do not address what you are trying to accomplish, perhaps you could elaborate so that we could better assist you.
0
 

Author Comment

by:puja24
ID: 11975576
Hello Androminos,

  I used the Microsoft code to generate a letter form:  http://support.microsoft.com/?kbid=285332

since I wrote in Visual Basic 6.0 editor and i want user to see a form with click button and it generate a lette form.

Do you know this can be done using ASP or Can i use Crystal Report to do this?

I would really appreciate your help

0
 
LVL 1

Expert Comment

by:Androminos
ID: 11975954
In theory, you would need to install Word on the ASP server since Word would be run server side, not client side, thereby giving ASP access to the necessary files.  (This may present a licensing issue?)  Unlike vb, you would not Dim oApp.  Simply start with:

Set oApp = Server.CreateObject("Word.Application")

'basically, it's the same except you stick the "Server." before the CreateObject.

I don't know if that would work or not, but that's the basic idea.  Look for more ASP examples to implement the ASP version of the code you've already got.  Good luck!
0
 
LVL 1

Expert Comment

by:Androminos
ID: 11976082
You can also accomplish a similar thing with Crystal Reports--essentially mimicking a mail merge by looping through a recordset and creating an instance of a report for each record.  We have implemented this solution at our company.  The licensing can be a pain in the butt to figure out, though.  We ended up purchasing Crystal 9 Advanced Server (I think that's what it is called).  It comes with 5 concurrent client access licenses, so obviously that would only work if you have fewer than 5 people trying to view a report at any one time.  (We don't worry about someone leaving a report open on their desktop because after 30 (?) seconds or so, the session variable times out and the license is available for someone else.  (Of course, we also had to purchase Crystal Reports 9.0 Developer versions for myself and our other developers who develop the reports).  Anther trick to managing it is by exporting the Crystal Report as a .pdf and then allowing the user to view that.  That frees up the access license almost immediately.
0
 

Author Comment

by:puja24
ID: 11976144
I'm going to have only one user will be maintaining the program after is ready to go.

Actually, i was looking for something simple form that would go to SQL connection and generate a letter form without changing any field.

therefore, i thought by using word MailMerge, i can accomplish this.

well, i am writing into Asp to do in web based form where user clicks the button and it will generate a letter form.  

i have that in Asp that creates in Mailing labels using MailMerge.

0
 
LVL 1

Expert Comment

by:Androminos
ID: 11976173
Excellent.  Good luck!
0
 

Author Comment

by:puja24
ID: 11998835
Hi,

  I was trying to code in ASP and receivig an error. Is there a way you can tell me why my program is not working for letter?
I am trying to convert inot ASP,
here is my code:

Dim WithEvents oApp As Word.Application

Private Sub Form_Load()
    'start word
    Set oApp = CreateObject("Word.Application")
End Sub
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
       
        'set up the mail merge data source to uimcc_mail
        .OpenDataSource Name:="", _
         Connection:="DSN=pubs;DATABASE=Pubs;uid=sa;pwd=;", _
         SQLStatement:="Select * from Authors", _
         SubType:=wdMergeSubTypeWord2000
         
         'Add the field codes to the document to create the form letter.
         With .Fields
            Set oSel = oApp.Selection
           
            oSel.TypeParagraph
            oSel.TypeParagraph
            .Add oSel.Range, "Address"
            oSel.TypeParagraph
            .Add oSel.Range, "City"
            oSel.TypeText ", "
             .Add oSel.Range, "State"
            oSel.TypeParagraph
            .Add oSel.Range, "ZipCode"
            oSel.TypeParagraph
            oSel.TypeParagraph
            oSel.TypeText "Dear "
            .Add oSel.Range, "Name"
            oSel.TypeText ":"
            oSel.TypeParagraph
            oSel.TypeParagraph
            oSel.TypeParagraph
            oSel.TypeText "Thank YOu!."
            oSel.TypeParagraph
        End With
    End With
     'Perform the mail merge to a new document.
     With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute Pause:=False
     End With
End Sub
Private Sub oApp_MailMergeAfterMerge(ByVal Doc As Word.Document, ByVal DocResult As Word.Document)
  'When the mail merge is complete 1) make word visible,
  '2) Close the mail merge document leaving the resulting document
  'open and 3) display message.
  Doc.Close False
  oApp.Visible = True
  MsgBox "Mail Merge Complete: " & oApp.ActiveDocument.Name
 
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set oApp = Nothing
End Sub
0
 
LVL 4

Expert Comment

by:computerg33k
ID: 11998853
whats the error ur getting?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:puja24
ID: 11998992
This one runs using Visual Basic Program and it does creates a letter form and retriving info from DSN connection;however, i am trying to accomplish same format but in ASP and when i tried convert into ASP the letter does not generates into separate document.  

Also, I am trying to run this VB program onto other machines, it does not run.

0
 
LVL 1

Accepted Solution

by:
Androminos earned 155 total points
ID: 11999072
If you will see my recent post, I indicated that instead of:

Dim WithEvents oApp As Word.Application
Set oApp = CreateObject("Word.Application")

you must instead use:

'Note that in ASP, one does NOT Dim oApp as a Word.Application as one does in VB
Set oApp = Server.CreateObject("Word.Application")

I also mentioned you must give ASP access to the Word object  by installing Word on the ASP server.  Have you done this, or taken other steps to give ASP access to the Word object?  If not, your code will fail.

0
 
LVL 1

Expert Comment

by:Androminos
ID: 11999083
Do the other machines have the necessary .dll's installed to run your application?  Have you created a setup program?
0
 

Author Comment

by:puja24
ID: 11999528
i installed VB 6.0 on my xp machine to test the apps but i am getting an error.  How can i accomplish this?

i'll try to add server.createobject into asp code.
0
 
LVL 1

Expert Comment

by:Androminos
ID: 11999755
Again, I ask the question:  Did you install Word on the ASP server?
0
 

Author Comment

by:puja24
ID: 12000406
I added server.createObject on my asp code but still it's not working.

Also, i am trying to use my VB code to another machine and try to run the vb application but getting an error message too.

I am trying both: ASP functionality and VB apps to run on other machines.

neither of seems to be working.

0
 
LVL 1

Expert Comment

by:Androminos
ID: 12000447
Perhaps we are having a communication difficulty.  I need to know if you have installed Word on the ASP server.  The code sample I gave will not work unless you first install Word on the ASP server.
0
 

Author Comment

by:puja24
ID: 12001775
Hi,

   We don't have ASP server. I installed Visual Stuio on my machine and created ASP pages.

I guess i am not understanding what do you mean by install word on ASP Server.

please help me to understand this

0
 
LVL 1

Expert Comment

by:Androminos
ID: 12005216
I'm afraid the learning curve you may have to go through for this project is more than I can assist you with in this forum.  From what I have gathered from your previous posts, you will need to learn about how ASP pages are run from an ASP server.  You will need to learn about setting up an ASP server and programming in ASP. You will need to learn about creating a setup program so that your vb application will run on other machines.  I wish you well--it sounds like you are off to a good start.
0
 

Author Comment

by:puja24
ID: 12020006
Hi,

  I guess it's kind of misunderstanding situation. The reason i asked if i can create mail merge in ASP to generate Letter form becuase user can go to web page and can able to create a letter form. However, i found that ASP would not work creating Mail Merge function.

Since i already have working VB program which generates a letter into word application.  I had quick question if i can run exe file onto my other test box(xp) should able to run the program.

but i am getting an error:  459  can you suggest what should be done to prevent this error?

0
 
LVL 1

Expert Comment

by:Androminos
ID: 12020304
Which exact line of code prompts this error?
0
 
LVL 1

Expert Comment

by:Androminos
ID: 12225821
I'd like some points, please.  I spent a while working on this issue and have given some valuable advice, sticking with this even as the question appears to have evolved as the user made progress and encountered different obstacles.  I know my solutions work as I have implemented them in successful projects.
0
 

Author Comment

by:puja24
ID: 12227036
Thanks  Androminos,

      Thanks for clearing some point i was not aware of it. Finally, my lette form is working

using VB and other users can even create the lettter form on their machine.

Thanks for all help
0
 
LVL 1

Expert Comment

by:Androminos
ID: 12227620
Excellent!  I'm glad you were able to get your solution working!
0
 

Author Comment

by:puja24
ID: 13364930
Hi,
  If i would like to Bold one of the column here, what would be the syntax for creating bold from city.

I tried doing it but does not work

********************************************************************
With .Fields
            Set oSel = oApp.Selection
            .Add oSel.Range, "CompanyName"
            oSel.TypeParagraph
            .Add oSel.Range, "Address"
            oSel.TypeParagraph
            .Add oSel.Range, "City"
            oSel.TypeText ", "
            .Add oSel.Range, "Country"
            oSel.TypeParagraph
            oSel.TypeParagraph
            oSel.TypeText "Dear "
            .Add oSel.Range, "ContactName"
            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
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now