Results 1 to 15 of 15
  1. #1
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544

    Question "Another" Mail Merge question

    I have the below code for a mail merge. It works just fine (in my previous post I could not get it to work....I had mis-spelled the document name). From my limited understanding of this code it assigns the "data source" to the mail merge document. Therefore, if my logic is....logical, I don't need the "SELECT" statement within the document itself. However, if I don't have the "SELECT" statement in the document I cannot tell Word where to put the merged fields. The reason I would like to assign the "data source" in VBA is because I believe it will not show the "SELECT" warning message that you get when opening a Mail Merge document. This is most definitely desired because I intend on passing this off to an end-user. Everything I have read states you must make some registry changes to "suppress" the message...Not an option for me. I guess my question is this...Can you insert the merge fields using VBA, and tell Word where to put them on the page?



    Code:
    Function funMergeItDM()
        Dim strPathFile As String, strTable As String
        Dim objDesktop As Object
        Dim ObjWord As Word.Document
        Dim wordApp As Word.Application
        Dim wordDoc As Word.Document
        
        Set objDesktop = CreateObject("WScript.Shell")
        strPathFile = objDesktop.SpecialFolders("Desktop") & "\DMContactListEnvelope.docx"
        
        Set ObjWord = GetObject(strPathFile, "Word.Document")
        
        With ObjWord
            .Application.Visible = True
            .MailMerge.OpenDataSource _
                Name:="C:\Data\DMPhoneList_Be.accdb", _
                LinkToSource:=True, Connection:="QUERY qryDMMailMerge", _
                SQLStatement:="SELECT * FROM [qryDMMailMerge]"
            .MailMerge.Execute
        End With
    End Function

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    I don't believe you do that. the merge document has the fields already placed on it, and you simply merge it with access. at that juncture, Access throws the data into the placeholders that should already be on the word document.

    did the mail merge option not work for you?

    I have a sample mail merge function that I use sometimes, and if I remember right, I never have gotten a security warning message using it. I'll post the code here if you want me to. and NO, changing the registry should NOT be required to get rid of that security warning.

    A good thing to note is that assistance posted by people affiliated or employed by MS usually says something about modifying the registry. Most of the time that's not even necessary, but for some reason, MS's people don't even know the freakin windows interface!

  3. #3
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Adam,
    I would much appreciate your sample code. I have been pulling my hair out over this for some time now! Thank you in advance!!!!

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    if I dont post it by tonight send me a message and let me know. I might lose track of this, and I'm not home right now, which is where that sample is at.

  5. #5
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Will do. Thanks.

  6. #6
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    For those curious, attached is the "SELECT" error/warning message I am referring to...

  7. #7
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Adam - Looks like both of us forgot about this! Any chance you could get this up tonight?

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    oh yeah, sure. what was I posting again? the mail merge function I use?

  9. #9
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Quote Originally Posted by ajetrumpet View Post
    oh yeah, sure. what was I posting again? the mail merge function I use?
    Correct. I believe you had a mail merge function that does not throw stupid MS security errors.

  10. #10
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    yeah, but you have to have the db set to SHARED, otherwise the security errors are inevitable. or they should be, rather...

    hang on a sec..

  11. #11
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    it's been a while since i used this bud. hope it works for ya:

    Code:
    Function MailMerge(mDoc As String, _
                       strSql As String)
    
    On Error GoTo Err_Handle
    
    '******************************************************************************
    '_____________________________________________________________________________*
    '                                                                             |
    'THIS FUNCTION USES THE CURRENT DATABASE AS THE MAIL MERGE SOURCE.  IT ALSO   |
    'USES LETTERS AS THE MERGE FORMAT.  THERE IS NO ARGUMENT TO CHANGE THIS.      |
    '_____________________________________________________________________________|
    '                                                                             *
    'Author: Adam Evanovich                                                       *
    'Date: 5/9/2005                                                               *
    'Purpose:  To run an automated Mail Merge with a MS Word template.            *
    '                                                                             *
    'Arguments:                                                                   *
    'mDoc > Full path of the Merge document.                                      *
    'strSQL> The query to run the Merge with.                                     *
    '                                                                             *
    '******************************************************************************
    
    
    Dim oApp As New Word.Application
    Dim oMainDoc As Word.Document
    Dim sData As String
    
       oApp.Visible = True
          sData = CurrentProject.Path & "\" & CurrentProject.name
    
       Set oMainDoc = oApp.Documents.Open(mDoc)
       
          With oMainDoc.MailMerge
              .MainDocumentType = wdFormLetters
              .OpenDataSource name:=sData, _
                              SQLStatement:=strSql
          End With
       
       With oMainDoc
           .MailMerge.Destination = wdSendToNewDocument
           .MailMerge.Execute
       End With
       
       oApp.Activate
       oApp.Documents.Parent.Visible = True
       oApp.Application.WindowState = 1
       oApp.ActiveWindow.WindowState = 1
    
    Set oApp = Nothing
    Set oMainDoc = Nothing
    
    Exit Function
    
    Err_Handle:
       Set oApp = Nothing
       Set oMainDoc = Nothing
          MsgBox "An error occurred..." & vbCrLf & vbCrLf & err.Description
    
    End Function '//LL

  12. #12
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Quote Originally Posted by ajetrumpet View Post
    'USES LETTERS AS THE MERGE FORMAT. THERE IS NO ARGUMENT TO CHANGE THIS. |
    Adam,
    Before I continue I want to ask you about this statement. I am trying to use the Mail Merge for labels. Do I understand this statement correctly in that this mail merge function only works for standard letter, and will not work for my labels?

  13. #13
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by jgelpi16 View Post
    Adam,
    Before I continue I want to ask you about this statement. I am trying to use the Mail Merge for labels. Do I understand this statement correctly in that this mail merge function only works for standard letter, and will not work for my labels?
    this:
    Code:
    MainDocumentType = wdFormLetters
    is the relevant code. to do labels, change that piece of code. what that comment means is simply that there is no ''argument'' to change that format. (I think)

  14. #14
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Excellent. Thank you!

  15. #15
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    yipppiiieeee!!!!!! solved! =)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM
  2. How to Filter When Using The "Create E-Mail" Wizard
    By MFriend in forum Import/Export Data
    Replies: 3
    Last Post: 07-16-2010, 09:45 AM
  3. Replies: 3
    Last Post: 04-10-2010, 10:22 AM
  4. Replies: 8
    Last Post: 02-24-2010, 01:49 PM
  5. aSTR = Dir("C:\*.*") >> "Type Mismatch"
    By JGrant in forum Programming
    Replies: 1
    Last Post: 03-28-2009, 05:17 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums