Run-time error '5922' - Mail Merge
Afternoon all,
Sorry for the mislead title - I had that error previously and will probably have it again, but another hump got in the way, so I amended my post.
I'm trying to set up a mail merge from Excel to Word. I've got it working manually, but need to automate the process for our users.
I have the macro below run in an AutoNew sub, when the document opens. However, every time I try and use it, after one clicks Yes to continue with the mail merge, it comes up with the error "Object doesn't support this property or method."
Code:
Sub MailMerge()
Dim SourceDoc As String
Dim SelectedItems(1) As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
If .Show <> 0 Then
SelectedItems(1) = SourceDoc
Else
Exit Sub
End If
End With
ActiveDocument.MailMerge.OpenDataSource Name:=SourceDoc, _
ConfirmConversions:=True, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=H:\Jonathon Hunter Hill\Mail Merge\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:" _
, SQLStatement:="SELECT * FROM `MailMerge$`", SQLStatement1:="", SubType _
:=wdMergeSubTypeAccess
End Sub
If I click No to the question of whether I want to carry out the mail merge or not, I can then run this macro again and it will get past that initial Object error stage. However, it then allows me to select a document (which I am able to mail merge manually), and then I can select the Provider (OLED), despite that choice already being filled (I think) at which point the 5922 error occurs and the sub terminates.
Any help would be very much appreciated,
dirtychinchilla