Mail Merge VBA

File Name Changes, Code "Breaks"

Page 1 of 1

5 Replies - 5844 Views - Last Post: 20 May 2010 - 12:20 PM Rate Topic: -----

#1 P4L   User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,789
  • Joined: 07-February 08

Mail Merge VBA

Posted 19 May 2010 - 11:42 AM

OK, so I have to admit that until recently, I've never had to use VB in conjuncture with Access to this type of a capacity. I have created a 6 functions inside a module. The issue I am having is when I change the file name (not the filepath AND name), the mail merge doesn't get sent to the printer. Here is the "working" code:

Function MergeIt()

Dim objWord As Word.Document
Set objWord = GetObject("U:\AWDLibrary\Josh's_Work\IllusCvrLtr_Illus only print.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source.
    With objWord.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:="U:\AWDLibrary\Josh's_Work\tblClient.xls", LinkToSource:=True, Connection:="TABLE tblClients"
        
    End With
' Execute the mail merge.
objWord.MailMerge.Execute
objWord.MailMerge.Destination = wdSendToPrinter 'wdSendToNewDocument; wdSendToFax; wdSendToEmail
'Closes Word when done
objWord.Application.Quit (False)

End Function



The "Broken" Code

Function MergeIt_IMO()

Dim objWord As Word.Document
Set objWord = GetObject("U:\AWDLibrary\Josh's_Work\IMO_welcome_letter.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source.
    With objWord.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:="U:\AWDLibrary\Josh's_Work\tblClientIMO.xls", LinkToSource:=True, Connection:="TABLE tblClients"
        
    End With
' Execute the mail merge
objWord.MailMerge.Execute
objWord.MailMerge.Destination = wdSendToPrinter 'wdSendToNewDocument; wdSendToFax; wdSendToEmail
'Closes Word when done
objWord.Application.Quit (False)

End Function



I do know that I can clean up some of the stuff that's commented out at a later date. I just need all the functions to work properly first.

Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: Mail Merge VBA

#2 raziel_   User is offline

  • Like a lollipop
  • member icon

Reputation: 469
  • View blog
  • Posts: 4,281
  • Joined: 25-March 09

Re: Mail Merge VBA

Posted 19 May 2010 - 12:48 PM

is it possible that the file is with wrong name? i`ts the only reason i can think of
Was This Post Helpful? 0
  • +
  • -

#3 P4L   User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,789
  • Joined: 07-February 08

Re: Mail Merge VBA

Posted 19 May 2010 - 12:55 PM

View PostNoBrain, on 19 May 2010 - 01:48 PM, said:

is it possible that the file is with wrong name? i`ts the only reason i can think of


Sorry , they are supposed to be different. There are 6 functions in the module, with each function being called by a radio button event procedure. All the code is EXACTLY the same, just fule names and function names are different. The kicker is, one of my company's TOP VB programmers was just at my desk, and he's dumbfounded as to why it doesn't work. He originally thought that the issue was that the one that works is linked back to the database, and the others aren't. So I linked one of the "non-working" Merge files to the database, and it still doesn't work.
Was This Post Helpful? 0
  • +
  • -

#4 raziel_   User is offline

  • Like a lollipop
  • member icon

Reputation: 469
  • View blog
  • Posts: 4,281
  • Joined: 25-March 09

Re: Mail Merge VBA

Posted 19 May 2010 - 01:26 PM

hmm try using
objWord.Documents.Open ("U:\AWDLibrary\Josh's_Work\IMO_welcome_letter.doc")



not getobject. dont know if that will help you but i am stunned how it can work with 1 file and not working with another

This post has been edited by NoBrain: 19 May 2010 - 01:26 PM

Was This Post Helpful? 0
  • +
  • -

#5 P4L   User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,789
  • Joined: 07-February 08

Re: Mail Merge VBA

Posted 20 May 2010 - 11:07 AM

RESOLVED!!!!


Needed a DDE connection to connect the Word Doc back to the DB, as well as connecting to the xls file. If an ODBC connection is used, the code fails.

This post has been edited by P4L: 20 May 2010 - 11:35 AM

Was This Post Helpful? 1
  • +
  • -

#6 raziel_   User is offline

  • Like a lollipop
  • member icon

Reputation: 469
  • View blog
  • Posts: 4,281
  • Joined: 25-March 09

Re: Mail Merge VBA

Posted 20 May 2010 - 12:20 PM

interesting. ty for sharing. :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1