Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail merge via vb code (Access2000)

    Could someone PLEASE tell me how I could mail merge a query via ODBC link using vb codes? PLEASE?

    I've already got the following code which opens the mail merged document but this is done using the office links button on the menu bar of access. This is via DDE link.
    <pre>Sub CitcoFax()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim objWord As Object
    Set db = CurrentDb
    Set rst = db.OpenRecordset("CitcoCrosstab")
    If rst.BOF And rst.EOF Then
    vResult = MsgBox("There are no records. Would you like to send a fax?", _
    vbQuestion + vbYesNo)
    If vResult = vbYes Then
    Set rst = Nothing
    Set db = Nothing
    'Open fax cover
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    objWord.FileOpen "C:TradarDevelopmentFax.doc"
    Exit Sub
    Else
    Exit Sub
    End If
    Else
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    objWord.FileOpen "C:TradarDevelopmentCitcoFax.doc"
    End If
    Set rst = Nothing
    Set db = Nothing
    End Sub
    </pre>


    Edited to eliminate horizontal scrolling--Charlotte

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail merge via vb code (Access2000)

    Well, for starters, where did you get the idea that was DDE? It isn't, it's perfectly normal automation.

    If you want to know how to execute the merge, look up the mailmerge object in the VBE. There is sample code in the help article.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge via vb code (Access2000)

    Im sorry but I've searched & searched but couldn't fine any examples of codes. PLEASE attch it to this post or do something. PLEASE

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge via vb code (Access2000)

    Function MergeIt()
    Dim objWord As Word.Document
    Set objWord = GetObject("C:TradarDevelopmentCitco.doc", "Word.Document")
    'Make word visible.
    objWord.Application.Visible = True
    'Set the mail merge data source
    objWord.MailMerge.OpenDataSource _
    name:="C:TradarTrdSDK(Copy)", _
    LinkToSource:=True, _
    Connection:="TABLE tblClassic", _
    SQLStatement:="Select * from [tblClassic]"
    'Execute the mail merge.
    objWord.MailMerge.Execute
    End Function

    I found the above code on the net but for some reason i don't think its right and It gives me error messages as well.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Mail merge via vb code (Access2000)

    Hi,
    You haven't included the full name of your database in the Name argument (you need for example something like Name:="G:TestsTestCode.mdb") and I don't believe you need the SQLStatement argument since you've specified that the connection is to the table.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge via vb code (Access2000)

    Hi Rory
    Thanx for the reply. I fixed the Name argument. The error message is on line
    Dim objWord As Word.Document

    The error is: Compile Error: User-defined type not defined
    I don't know what type Im supposed to define.

    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Mail merge via vb code (Access2000)

    Hi,
    You need to set a reference to Word's object library. On the menubar in the VBEditor, select Tools-References and then find Microsoft Word 9.0 Object library (assuming you're using Word2000) and check the box next to it. Then try compiling your database.
    The red text usually means your syntax is missing something - your code should read something like:
    Function MergeIt()
    Dim objWord As Word.Document
    Set objWord = GetObject("C:TradarDevelopmentCitco.doc", "Word.Document")
    'Make word visible.
    With objWord
    .Application.Visible = True
    'Set the mail merge data source
    .MailMerge.OpenDataSource _
    name:="C:TradarTrdSDK(Copy)", _
    LinkToSource:=True, _
    Connection:="TABLE tblClassic"
    'Execute the mail merge.
    .MailMerge.Execute
    End with
    set objword = nothing
    End Function

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge via vb code (Access2000)

    Thanx Rory,
    What do I have to do if I want to changr the data source to a query instead of a table?
    Do I change
    Connection:="TABLE tblSFMReportSource"
    to
    Connection:="QUERY Citco"
    or
    SQLStatement:="Select * from [Citco]"
    ?

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Mail merge via vb code (Access2000)

    Hi,
    Assuming you have an existing query called Citco you can use Connection:="QUERY Citco". If the query doesn't exist then you can use the SQLStatement:="Select * from [Citco]"
    method.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge via vb code (Access2000)

    THANK U!
    Is there anyway I could open this link via ODBC link using the codes?
    My program is a password protected program and when I try to open the merged document it promts me to enter the password and opens another instance of access once I enter the password. I don't want this.

    I tried to merge it from word via ODBC but it wouldn't allow me to do that for some reason. PLEASE help if you can.

    THANX 4 all the other help.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •