+ Reply to Thread
Results 1 to 12 of 12

Initiate Word Mail Merge from within Excel - O365

  1. #1
    Registered User
    Join Date
    10-14-2021
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    O365
    Posts
    7

    Initiate Word Mail Merge from within Excel - O365

    Hi,

    I've been playing around with Excel and macros and appear to need to step into the world of VBA.

    My goal is to trigger a Word mail merge from within Excel (presumably via control button and macro), using the current Excel workbook as the data source, and referencing a Word .dotx document in a shared location as the template for the mail merge.

    I've found some code on the Web, but it was written for a much older version of Excel, and I'm having issues with it: can't post link but its from MrExcel.com

    Anyone have insights as to whether this should word for O365?

    I'm getting a VBA Compile error - User-defined type not defined for the third line:
    Sub DoMailMerge()
    'Note: A VBA Reference to the Word Object Model is required, via Tools|References
    Dim wdApp As Word.Application, wdDoc As Word.Document

    What am I doing wrong?

    Any guidance would be appreciated.

    Thanks

  2. #2
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,811

    Re: Initiate Word Mail Merge from within Excel - O365

    You need to set a reference to Word as per the commment 'Note: A VBA Reference to the Word Object Model is required, via Tools|References
    Attached Images Attached Images

  3. #3
    Registered User
    Join Date
    10-14-2021
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    O365
    Posts
    7

    Re: Initiate Word Mail Merge from within Excel - O365

    Thanks ByteMarks. I've referenced the Word Object model as appropriate.

    I've done further troubleshooting, thanks to a colleague and am now getting an error after the code runs fully ( think):

    Sub DoMailMerge()
    'Note: A VBA Reference to the Word Object Model is required, via Tools|References
    Dim wdApp As Object
    Set wdApp = CreateObject("Word.Application")
    Dim wdDoc As Object
    Set wdDoc = CreateObject("Word.Document")
    Dim strWorkbookName As String
    strWorkbookName = ThisWorkbook.FullName
    With wdApp
    'Disable alerts to prevent an SQL prompt
    .DisplayAlerts = wdAlertsNone
    'Open the mailmerge main document
    Set wdDoc = .Documents.Open("C:\Users\[username]\OneDrive\Documents 1\MergeTest\ProposalTemplate.dotx", _
    ConfirmConversion:=False, ReadOnly:=True, AddToRecentFiles:=False)
    With wdDoc
    With .MailMerge
    'Define the mailmerge type
    .MainDocumentType = wdFormLetters
    'Define the output
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    'Connect to the data source
    .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
    LinkToSource:=False, AddToRecentFiles:=False, _
    Connection:="Provider-Microsoft.ACE.OLEDB.16.0;" & _
    "UserID=Admin;Date Source=strWorkbookName;" & _
    "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
    SQLStatement:="SELECT * FROM 'MergeData$'", _
    SubType:=wdMergeSubTypeAccess
    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    End With
    'Execute the merge
    .Execute
    'Disconnect from the data source
    .MainDocumentType = wdNotAMergeDocument
    End With
    'Close the mailmerge main document
    .Close False
    End With
    'Restore the Word alerts
    .DisplayAlerts = wdAlertsAll
    'Display Word and the document
    .Visible = True
    End With
    End Sub

    Error message I'm getting is Runtime Error 448 - Named Argument Not Found

    What is likely causing this error. No line within the code gets highlighted with this message, so I don't know where to start troubleshooting.

    Any further ideas?

    Thanks

  4. #4
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,161

    Re: Initiate Word Mail Merge from within Excel - O365

    Change this line with
    Please Login or Register  to view this content.
    Artik

  5. #5
    Registered User
    Join Date
    10-14-2021
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    O365
    Posts
    7

    Re: Initiate Word Mail Merge from within Excel - O365

    Thanks Artik,

    I'm still having the issue. I caught a different error - "UserID=Admin;Data Source= " & strWorkbookName & ";" & _

    Anyone else have any thoughts?

    Thanks

  6. #6
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,161

    Re: Initiate Word Mail Merge from within Excel - O365

    Here, Paul Edstein (MVP) specifies that the "UserID" parameter is named "User ID" (with space). Also check that your source worksheet is named MergeData ($ sign must remain).

    Artik

  7. #7
    Registered User
    Join Date
    10-14-2021
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    O365
    Posts
    7

    Re: Initiate Word Mail Merge from within Excel - O365

    Thanks Artik,

    I've updated the User ID parameter.

    What I'm trying to do is initiate the mail merge from within the source Excel document, with the data in tab MailMerge.
    I thought that by defining the strWorkbookName as the current/open workbook that would handle the source file.

    Dim strWorkbookName As String
    strWorkbookName = ThisWorkbook.FullName

    No?

    Thanks

  8. #8
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,161

    Re: Initiate Word Mail Merge from within Excel - O365

    It's probably OK. Just remember that ThisWorkbook is the workbook you run the macro from, and ActiveWorkbook is the workbook that is currently active. Often times, both references refer to the same workbook, but you should be aware of the difference.

    Artik

  9. #9
    Registered User
    Join Date
    10-14-2021
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    O365
    Posts
    7

    Re: Initiate Word Mail Merge from within Excel - O365

    Tanks Artik. I'll try to remember that.

  10. #10
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,811

    Re: Initiate Word Mail Merge from within Excel - O365

    I think it should be ConfirmConversions


    Set wdDoc = .Documents.Open("C:\Users\[username]\OneDrive\Documents 1\MergeTest\ProposalTemplate.dotx", _
    ConfirmConversion:=False, ReadOnly:=True, AddToRecentFiles:=False)

  11. #11
    Registered User
    Join Date
    10-14-2021
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    O365
    Posts
    7

    Re: Initiate Word Mail Merge from within Excel - O365

    Thanks ByteMarks. I'll give that a try.

  12. #12
    Registered User
    Join Date
    10-14-2021
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    O365
    Posts
    7

    Re: Initiate Word Mail Merge from within Excel - O365

    ByteMarks - you were right about the typo. Thanks very much!

    Now I'm getting an 9105 error - Application-defined or object defined error.

    Any suggestions anyone?

    Could there be an issue with the VBA reference? There are 3 different VBA references available to me - the default path starts as follows - Program Files (x86)\Common Files\Microsoft Shared\VBA\VB... (name too large for dialogue box), plus 2 others pointing to files Windows\SysWOW64\msvbvm60.dll & VEN2232.olb. I can't seem to reference all 3 at the same time.

    Making progress on this, slowly but surely.

    Thanks again all.

    Current code:

    Sub DoMailMerge()
    'Note: A VBA Reference to the Word Object Model is required, via Tools|References - this is conformed to be set (VBA x 3, Excel 16.0 object Library, OLE Automation, Microsoft Office 16.0 Object Library selected, as well as Office 16.0 Access database engine Object Library
    Dim wdApp As Object
    Set wdApp = CreateObject("Word.Application")
    Dim wdDoc As Object
    Set wdDoc = CreateObject("Word.Document")
    Dim strWorkbookName As String
    strWorkbookName = ThisWorkbook.FullName
    With wdApp
    'Disable alerts to prevent an SQL prompt
    .DisplayAlerts = wdAlertsNone
    'Open the mailmerge main document
    Set wdDoc = .Documents.Open("C:\Users\9user.name]\OneDrive\Documents 1\MergeTest\ProposalTemplate.dotx", _
    ConfirmConversions:=False, ReadOnly:=True, AddToRecentFiles:=False)
    With wdDoc
    With .MailMerge
    'Define the mailmerge type
    .MainDocumentType = wdFormLetters
    'Define the output
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    'Connect to the data source
    .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
    LinkToSource:=False, AddToRecentFiles:=False, _
    Connection:="Provider-Microsoft.ACE.OLEDB.16.0;" & _
    "User ID=Admin;Data Source= " & strWorkbookName & ";" & _
    "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
    SQLStatement:="SELECT * FROM `MergeData$`", _
    SubType:=wdMergeSubTypeAccess
    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    End With
    'Execute the merge
    .Execute
    'Disconnect from the data source
    .MainDocumentType = wdNotAMergeDocument
    End With
    'Close the mailmerge main document
    .Close False
    End With
    'Restore the Word alerts
    .DisplayAlerts = wdAlertsAll
    'Display Word and the document
    .Visible = True
    End With
    End Sub
    Last edited by VBANewbie61; 10-15-2021 at 05:22 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Word-Excel Mail merge
    By MP1989 in forum Excel General
    Replies: 11
    Last Post: 04-12-2018, 05:30 PM
  2. Replies: 0
    Last Post: 01-05-2015, 07:35 AM
  3. Excel Mail Merge, but without Word?
    By mjhaston in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-02-2013, 08:49 AM
  4. Replies: 2
    Last Post: 01-22-2013, 11:37 AM
  5. Mail merge within Excel...not with Word
    By PattiFlick in forum Excel General
    Replies: 10
    Last Post: 01-05-2013, 03:05 PM
  6. need help w/ mail merge excel to word
    By sophia in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-20-2006, 01:09 PM
  7. merge with Word How can I get excel to mail merge zip codes plus 4 correctly?
    By Kathy at Sauder Feeds in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-30-2005, 07:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1