Run-time error '5922' - Mail Merge

[dirtychinch]

Apologies for the cross post...I didn't realise that the forums were connected at all, and thought that this forum was more applicable for the issue. I requested that topic be removed on the other forum, before I posted in this one.

xld, thanks for your input. I think Frosty sorted that out. It didn't make difference, unfortunately.

Frosty, thank you very much. It's at least legible now, if not working! I think the problem is that I've got

    ActiveDocument.MailMerge.OpenDataSource _    Name:=SourceDoc, _

and it doesn't like how I've got SourceDoc as an input. This can't be avoided, due to the nature of how the program will be used.

I've got it working like this:

Sub MailMerge()
    Dim SourceDoc As String
    Dim SelectedItems(1) As String
    Dim sConnection As String
     
     
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        If .Show <> 0 Then
            SelectedItems(1) = SourceDoc
        Else
            Exit Sub
        End If
    End With
     
     'your connection string, also more easily separated with line breaks and arguments
    sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "User ID=Admin;" & _
    "Data Source=""H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm"";" & _
    "Mode=Read;" & _
    "Extended Properties=""HDR=YES;IMEX=1;"";" & _
    "Jet OLEDB:System database="""";" & _
    "Jet OLEDB:Registry Path="""";" & _
    "Jet OLEDB:Engine Type=35;" & _
    "Jet OLEDB:"
     
     'using your conection string... with the parameter names and the passed values separated nicely
    ActiveDocument.MailMerge.OpenDataSource _
    Name:="H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm", _
    ConfirmConversions:=True, _
    SQLStatement:="SELECT * FROM `MailMerge`"
    'ReadOnly:=False, _
    'LinkToSource:=True, _
    'AddToRecentFiles:=False, _
    'PasswordDocument:="", _
    'PasswordTemplate:="", _
    'WritePasswordDocument:="", _
    'WritePasswordTemplate:="", _
    'Revert:=False, _
    'Format:=wdOpenFormatAuto, _
    'Connection:=sConnection, _
    'SQLStatement1:="", _
    'SubType:=wdMergeSubTypeAccess
    
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
     
End Sub

But it's not acceptable for me to have the source in there, as I've said. So, does anyone have any suggestions of how to get around this? Also, I have commented out most of the ActiveDocument bit because the link to that document makes it over 255 characters, which is apparently unacceptable...

Thanks for your help, chaps.

[dirtychinch]

I have a further, very strange, thing going on. I have put this macro where macros record to in the project browser (under Normal, Modules, NewMacros), but this seems to have gone across all my documents??

E: Fixed it, had to go under TemplateProject

[macropod]

Apologies for the cross post...I didn't realise that the forums were connected at all, and thought that this forum was more applicable for the issue. I requested that topic be removed on the other forum, before I posted in this one.

Evidently you haven't read what's in the link. The whole point about cross-posting etiquette that you'll read there is that the forums aren't linked and, by not applying the etiquette (which amounts to nothing more than common courtesy), you could end up having different people working on the same issue, possibly unnecessarily traversing the same ground and maybe even working at cross purposes.

As for:

I think the problem is that I've got

    ActiveDocument.MailMerge.OpenDataSource _    Name:=SourceDoc, _

and it doesn't like how I've got SourceDoc as an input. This can't be avoided, due to the nature of how the program will be used.

I've got it working like this:

Sub MailMerge()
    Dim SourceDoc As String
'...
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        If .Show <> 0 Then
            SelectedItems(1) = SourceDoc
        Else
            Exit Sub
        End If
    End With
     
     'your connection string, also more easily separated with line breaks and arguments
    sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "User ID=Admin;" & _
    "Data Source=""H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm"";" & _
    '...
    Name:="H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm", 
'...     
End Sub

But it's not acceptable for me to have the source in there, as I've said.

there's not much point in defining and selecting a source document, then not using it!

Your two references to:
"H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm"
should be replaced with:
SourceDoc

[dirtychinch]

Evidently you haven't read what's in the link. The whole point about cross-posting etiquette that you'll read there is that the forums aren't linked and, by not applying the etiquette (which amounts to nothing more than common courtesy), you could end up having different people working on the same issue, possibly unnecessarily traversing the same ground and maybe even working at cross purposes.

I appreciate that, and no, I hadn't read the link. As I said, I had requested that that thread be closed.

As for:

there's not much point in defining and selecting a source document, then not using it!

Your two references to:
"H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm"
should be replaced with:
SourceDoc

If I replace those references with SourceDoc, it then has the execution problem...

[Aussiebear]

Apologies for the cross post...I didn't realise that the forums were connected at all,

The internet make the earth a very small world particularly when people join multiple forums to gain a wider experience in subject such as Excel and VBA etc. Please take the time to read the link provided by Paul.