Consulting

Results 1 to 5 of 5

Thread: VBA Error - Mail Merge

  1. #1
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location

    VBA Error - Mail Merge

    For the life of me, I cannot fix this run time error-5273 (document name or path is not valid). on the code. Can someone with better eyes tell me what the issue is with the code below? Thank you.

    Sub RunMerge()<br>
    <br>
    <br>
    Dim wd As Object<br>
    Dim wdocSource As Object<br>
    <br>
    <br>
    Dim strWorkbookName As String<br>
    <br>
    <br>
    On Error Resume Next<br>
    Set wd = GetObject(, "Word.Application")<br>
    If wd Is Nothing Then<br>
    Set wd = CreateObject("Word.Application")<br>
    End If<br>
    On Error GoTo 0<br>
    <br>
    <br>
    Set wdocSource = wd.Documents.Open("C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\HR_Email_One_Docs\Visa Mail Merge.docx")<br>
    <br>
    <br>
    strWorkbookName = ThisWorkbook.Path &amp; "\" &amp; ThisWorkbook.Name &amp; "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"<br>
    <br>
    <br>
    wdocSource.MailMerge.MainDocumentType = wdFormLetters<br>
    <br>
    <br>
    wdocSource.MailMerge.OpenDataSource _<br>
    Name:=strWorkbookName, _<br>
    AddToRecentFiles:=False, _<br>
    Revert:=False, _<br>
    Format:=wdOpenFormatAuto, _<br>
    Connection:="Data Source=" &amp; strWorkbookName &amp; ";Mode=Read", _<br>
    SQLStatement:="SELECT * FROM `Visa$`"<br>
    <br>
    <br>
    With wdocSource.MailMerge<br>
    .Destination = wdSendToNewDocument<br>
    .SuppressBlankLines = True<br>
    With .DataSource<br>
    .FirstRecord = wdDefaultFirstRecord<br>
    .LastRecord = wdDefaultLastRecord<br>
    End With<br>
    .Execute Pause:=False<br>
    End With<br>
    <br>
    <br>
    wd.Visible = True<br>
    wdocSource.Close SaveChanges:=False<br>
    <br>
    <br>
    Set wdocSource = Nothing<br>
    Set wd = Nothing<br>
    <br>
    <br>
    End Sub<br>

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,098
    Location
    Once I formatted it to remove the HTML (below) I suspect it's this line


    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name & "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"


    
    Sub RunMerge()
    
    
    
    
    Dim wd As Object
    Dim wdocSource As Object
    
    
    
    
    Dim strWorkbookName As String
    
    
    
    
    On Error Resume Next
    Set wd = GetObject(, "Word.Application")
    If wd Is Nothing Then
    Set wd = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    
    
    
    
    Set wdocSource = wd.Documents.Open("C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\HR_Email_One_Docs\Visa Mail Merge.docx")
    
    
    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name & "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"
    
    
    
    
    wdocSource.MailMerge.MainDocumentType = wdFormLetters
    
    
    
    
    wdocSource.MailMerge.OpenDataSource _
    Name:=strWorkbookName, _
    AddToRecentFiles:=False, _
    Revert:=False, _
    Format:=wdOpenFormatAuto, _
    Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
    SQLStatement:="SELECT * FROM `Visa$`"
    
    
    
    
    With wdocSource.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
    End With
    
    
    
    
    wd.Visible = True
    wdocSource.Close SaveChanges:=False
    
    
    
    
    Set wdocSource = Nothing
    Set wd = Nothing
    
    
    
    
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location
    thanks Paul. Sadly it did not work. I think the error is due to this line, so it look correct to you?

    strWorkbookName = ThisWorkbook.Path & "" & ThisWorkbook.Name & "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"
    Quote Originally Posted by Paul_Hossler View Post
    Once I formatted it to remove the HTML (below) I suspect it's this line


    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name & "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"


    
    Sub RunMerge()
    
    
    
    
    Dim wd As Object
    Dim wdocSource As Object
    
    
    
    
    Dim strWorkbookName As String
    
    
    
    
    On Error Resume Next
    Set wd = GetObject(, "Word.Application")
    If wd Is Nothing Then
    Set wd = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    
    
    
    
    Set wdocSource = wd.Documents.Open("C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\HR_Email_One_Docs\Visa Mail Merge.docx")
    
    
    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name & "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"
    
    
    
    
    wdocSource.MailMerge.MainDocumentType = wdFormLetters
    
    
    
    
    wdocSource.MailMerge.OpenDataSource _
    Name:=strWorkbookName, _
    AddToRecentFiles:=False, _
    Revert:=False, _
    Format:=wdOpenFormatAuto, _
    Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
    SQLStatement:="SELECT * FROM `Visa$`"
    
    
    
    
    With wdocSource.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
    End With
    
    
    
    
    wd.Visible = True
    wdocSource.Close SaveChanges:=False
    
    
    
    
    Set wdocSource = Nothing
    Set wd = Nothing
    
    
    
    
    End Sub

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,098
    Location
    No need to quote the entire message

    I'm guessing that it should be something like one of these??


    strWorkbookName = "C:\Users\stephenc1\OneDrive - Carnival Corporation\Joiners_Docs\Seabourn Recruitment Tracker.xlsm"
    
    
    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name 
    
    
    strWorkbookName = ThisWorkbook.Fullname
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Dec 2021
    Posts
    8
    Location
    Thank you Paul. The first one worked a treat. Appreciate your time.

Posting Permissions

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