Consulting

Results 1 to 7 of 7

Thread: Auto run Word mail merge to Email

  1. #1
    VBAX Newbie
    Joined
    Sep 2020
    Posts
    3
    Location

    Auto run Word mail merge to Email

    I need to generate an email to send to guest of a resort the day before they are scheduled to arrive. The Email contains a QR code to identify them at the gate when they arrive. I have completed several task to accomplish this.
    1. I have an Excel routine that extracts data from the scheduling database and grooms it for a mail merge.
    2. I have a mail merge in word that uses the mailing list from excel to create an email and generate the QR code.
    3. I have a macro in word that sends the email to the list. I have it set to run when the document is close.

    If I open the word doc manually, it ask (Yes / No - “Data from your database will be placed in this document”. I answer (Yes) and when I close the document the macro runs and it sends the emails.

    When I use a Visual Basic Script to open and close the document, the macro encounters an error.
    (Run-Time error: “5852”: Requested object is not available)
    When you debug it points out: .Destination = wdSendToEmail

    It seems that it does not know who to send the letter to.
    Perhaps the question “Data from your database will be placed in this document” needs to be answered in the VBS script?

    Any thoughts?

  2. #2
    VBAX Contributor
    Joined
    Jul 2020
    Location
    Sun Prairie
    Posts
    118
    Location
    Save your primary merge document as a non-merge ordinary Word document.
    Have your vba code that opens the document next change it to an email merge and attach your data source.

  3. #3
    VBAX Newbie
    Joined
    Sep 2020
    Posts
    3
    Location

    Thank You

    Quote Originally Posted by Chas Kenyon View Post
    Save your primary merge document as a non-merge ordinary Word document.
    Have your vba code that opens the document next change it to an email merge and attach your data source.
    Thank you! That makes sense.
    But I'm afraid that as a novice programmer I don't know how to write the code in the VB Script to change the doc to an email merge and attach the data.

  4. #4
    VBAX Contributor
    Joined
    Jul 2020
    Location
    Sun Prairie
    Posts
    118
    Location
    Here is some code I wrote about 15 years ago that may help.

        With ActiveDocument.MailMerge
            .MainDocumentType = wdFormLetters
            .OpenDataSource Name:=strFileName, _
                SQLStatement:="SELECT * FROM `Clients$`" ', _
    '
    '       Show merge data
            .ViewMailMergeFieldCodes = False
        End With
    That is part of a macro in a global template that is called whenever I create a new document based on one of my merge templates. If you want to use documents rather than templates, it would be part of a macro that runs whenever the document is opened. You would want to exit the document without saving once you've performed the merge.

    The global macro is called by (hopefully unique) name:
    Application.Run "AttachClients"
    The name could be made more specific by specifying the project and module.
    Application.Run "pKenyonMenus.mMerge.AttachClients"
    For running from Excel it may have to be Word.Run instead. I am unsure of that.

  5. #5
    Without seeing your code it is difficult to determine the nature of the problem, however see https://www.gmayor.com/email_merge_addin.html which should achieve what you want and if the appropriate field is in your merge document it will also produce the bar code {MERGEBARCODE "Fieldname" QR \t }
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    VBAX Newbie
    Joined
    Sep 2020
    Posts
    3
    Location

    Thank You

    Quote Originally Posted by gmayor View Post
    Without seeing your code it is difficult to determine the nature of the problem, however seehttps://www.gmayor.com/email_merge_addin.html which should achieve what you want and if the appropriate field is in your merge document it will also produce the bar code {MERGEBARCODE "Fieldname" QR \t }
    Thank you Graham. We are soooo close!
    My intention is to run the merge and email at a specific time each day without user interaction. I built a VBS to open the word doc and run your script "Merge_Document". But I still need to manually hit the button on your app to "complete the merge" Is there a way to script the action of that button?

  7. #7
    I have replied to you privately with a possible solution using the add-in.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

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
  •