Run-time error '5922' - Mail Merge

[dirtychinch]

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."


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

[Bob Phillips]

Shouldn't your source be in quotes, especially as it has spaces in it.

[Frosty]

I think the connection string (which is the actual argument being passed), has larger problems, as that entire argument is a string, but you're also trying to pass a whole bunch of different double-quotes, as well as what looks to me a null argument. Combine that with line breaks created by a recorded macro, it's pretty tough to see what's going on... is it a syntax error in VBA, or is it a SQL syntax error in your connection string?

Here's your code re-written simply for legibility...

Take it out of the AutoNew routine, and try stepping through the code... and seeing if your connection string is wrong (almost guaranteed) or some other parameter you're passing.


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\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:"

'using your conection string... with the parameter names and the passed values separated nicely
ActiveDocument.MailMerge.OpenDataSource _
Name:=SourceDoc, _
ConfirmConversions:=True, _
ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:=sConnection, _
SQLStatement:="SELECT * FROM `MailMerge$`", _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

End Sub

[Frosty]

FYI - you can use Debug.Print to put the results of something in the immediate window (available under the View menu)
Debug.Print sConnection

Or you can hover over a variable (not as useful, when it's this long) when stepping through

Or you can type:
?sConnection
in the immediate window, and see what it is when stepping through the code.

I get your connection string as this...
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:

I'm no expert, but that doesn't look wrong around the "Extended Properties" area...

[macropod]

Cross-posted at: http://www.mrexcel.com/forum/newreply.php?do=newreply&p=3723128&noquote=1
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

[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]

Strangely enough, if I set SourceDoc in the code, it has no problems, like so:


Sub MailMerge() Dim SourceDoc As String
Dim SelectedItems(1) As String
Dim sConnection As String

SourceDoc = "H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm"

'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=SourceDoc;" & _
"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:=SourceDoc, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`"
'Connection:=sConnection
'SQLStatement1:="", _
'SubType:=wdMergeSubTypeAccess
'PasswordDocument:="", _
'PasswordTemplate:="", _
'WritePasswordDocument:="", _
'WritePasswordTemplate:="", _


End Sub


But, if I have the user select the source document it fails to execute.

[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...

[dirtychinch]

To follow up on my last post, this code does not work (with the sourcedoc thing changed). I get runtime error 4198.


Sub MailMergeFromExcel()

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=SourceDoc;" & _
"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:=SourceDoc, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`"
'Connection:=sConnection
'SQLStatement1:="", _
'SubType:=wdMergeSubTypeAccess
'PasswordDocument:="", _
'PasswordTemplate:="", _
'WritePasswordDocument:="", _
'WritePasswordTemplate:="", _



End Sub


But the problem only occurs if you use a dialogue box to do it. If you set SourceDoc= "xyz" then it does work...

[Frosty]

You should step through your code, and hover over the value of source doc. It sounds like it's not the value you think it is...

[dirtychinch]

I've been trying to do that, but it doesn't get past the point of "SelectedItems(1) = SourceDoc" so I can't read the value. I changed that to "Set SelectedItems(1) = SourceDoc" and now I get error 91...

[Frosty]

Then you should try to see what the value of SelectedItems(1) is... you might need SelectedItems(0).

Here's the thing... that whole File Picker dialog exposes you to a lot of different potential inputs, if you're not validating what you get back. Setting the .AllowMultiSelect to false is good... but then you need to make sure (I believe) that they've also selected a valid file (how do you know they didn't select a power point presentation?

I don't have access to a coding box here... but it sounds like you're on the right path. You're trying to have a way of getting the right info from a dialog picker... so focus on that. You don't have a MailMerge problem, yet.

In the immediate window, try typing:
?SelectedItems(0)
and
?SelectedItems(1)

[dirtychinch]

Hi Frosty,

Sorry I didn't reply sooner - I'm not taking my work home for the weekend :giggle

Yes you've nailed it down - I need to get the right information from the dialogue picker.

I have tried typing those commands into the immediate window, but that doesn't do anything...nor does hovering over them..do I need to run them in a sub or something?

I'm not fixed on how to do the file picking bit, so if you have any other suggestions then please do let me know. I just need the mail merge to work!!

:thumb

[dirtychinch]

I've now made some minor changes to the code, but it comes up with run-time error '91' now, just after I select the source document. Any ideas?!


Sub MailMergeFromExcel()

Dim SelectedItems(1) As Object
Dim sConnection As String

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "Excel Macro-Enabled Workbook", "*.xlsm"
If .Show <> 0 Then
Else
Exit Sub
End If
End With

'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=SelectedItems(1);" & _
"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:=SelectedItems(1), _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""

End Sub

[Frosty]

Yeah, you don't want to start randomly changing data types, to hope something works. You'll get a different error message, but the root of the problem is the same. Why don't you try something like this, just to test that functionality...

Sub Test()
MsgBox fGetFilePath
End Sub
Public Function fGetFilePath() As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "My Filter", "*.dotm"

If .Show = 0 Then
Exit Function
End If
fGetFilePath = .SelectedItems(1)
End With

End Function

I'm not totally familiar with the file picker, and if I had to guess, I would guess that you're getting some kind of incomplete return based on the multiple application of filters on a persistent object. Because the usage is really really simple.

Try modifying just the function above and seeing if your message box result continues to be an empty string, or if it is actually representative of the path you want.

[Frosty]

Oh, jeez... I just realized why it's not working... you're not using .SelectedItems as a property of the file picker... you're using it as your own variable, which you *never set* within the with block. Of course your own variable named "SelectedItems" is going to be blank... what you want is the .SelectedItems array which is a property OF the dialog picker... and my demo code shows the usage of.

So... take my function, then add a variable called "strSourcePath" as a string to your main function (and remove your own SelectedItems(1) variable)... and set the new variable to the result of fGetFilePath... and if the result is "", then exit your main routine.

This gives you a subroutine you can use in other places as well, if you wanted to change filters or something. You'll also need to modify the filter back to the way you want it to be.

See how far you can get with the above guidance, and then post back any questions or your working code.

[dirtychinch]

Hi Frosty,

Thanks for your help. I have actually fixed the problem, but wanted to be sure before I updated the thread. I had to use a loop to get it working, but it's pretty much perfect now. The only problem I have is that it takes about 30 seconds to work, so if you have any hints as to why that might be, I'd appreciate them.

The code now is as follows:



Sub MailMergeFromExcel()

Dim sConnection As String
Dim lngCount As Long

'Open the file dialog
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.Show
For lngCount = 1 To .SelectedItems.Count

'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=SelectedItems(lngCount);" & _
"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:=.SelectedItems(lngCount), _
ConfirmConversions:=True, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""

Next lngCount
End With

End Sub

[Frosty]

There is no way the code you just posted works. Please review with my notes above in mind. Are you using option explicit?

and do you actually want the ability to select multiple source files and perform the mail merge on them? Becuse
youve switched the core way your code was working, from the end-user perspective (allowing multi-select vs not allowing)

[dirtychinch]

Frosty,

The code works perfectly, and the code is only for selecting single documents as the purpose of this is to produce a single quotation with little effort from the operator.

Do you think it's worth modifying the code as you've suggested, considering it works?

E: in fact, I'll give it a go now in the hope that it'll speed the process up.

[dirtychinch]

I've ended up with this, which definitely isn't correct:


Sub Test() MsgBox fGetFilePath
End Sub
Public Function fGetFilePath() As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "My Filter", "*.dotm"

If .Show = 0 Then
Exit Function
End If
fGetFilePath = .SelectedItems(1)
End With

MailMergeFromExcel1

End Function
Sub MailMergeFromExcel1()

Dim sConnection As String
Dim strSourcePath As String
Dim fGetFilePath As String

Set fGetFilePath = strSourcePath

'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=strSourcePath;" & _
"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:=strSourcePath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""

End Sub

[Frosty]

Well, if it's working, it's because you don't need your connection string at all, because it is currently worthless. The fact that it takes 30 seconds is because you're probably finding your source doc from an alt method (30 seconds is probably the time for your bad connection string to give up and go another way).

also, have you tried selecting multiple files? You allow it in your code, someone will do it.

You don't need this loop at all-- you just happened to copy the relevant correct usage of .SelectedItems from wherever you got this code, rather than what you had before.

I know you just want it to work. But I'm more interested in you understanding why it works (or doesn't). Teach someone to fish, and all that

[dirtychinch]

Haha fair enough! I'm about to clock off, so will give your instructions a better shot tomorrow.

I'll make sure I disable the multiple file selection - haven't tested that s yet. I did indeed just copy the correct stuff from some other code...caught me :p

Yes indeed, and I appreciate you teaching me. As I say, I'm about to clock off so I'll try tomorrow and report back :D

Thanks for your help, sir.

[Frosty]

That was a good effort... here is what you're looking for. See if you can understand the main differences:
1. You call a function, so you just care about the result. You don't want to call your "main" function from within that function. Try out the TestFilePicker to see how the function works.
2. when you're trying to pass the value of a variable to a string... you need to make sure you're not passing the name of the variable, instead of the variable.... the difference between

"hello & strTemp & world"
and
"hello " & strTemp & " world"

3. You don't need to dim a variable with the same name as a function... you use the result of the function and plop it into a different variable.


Public Sub TestFilePicker()
MsgBox fGetFilePath
End Sub


'function to return the path of a single selected file
Public Function fGetFilePath() As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
'.Filters.Add "My Filter", "*.dotm"
.Filters.Add "Excel Macro-Enabled Workbook", "*.xlsm"
If .Show = 0 Then
Exit Function
End If
fGetFilePath = .SelectedItems(1)
End With

End Function
Sub MailMergeFromExcel1()

Dim sConnection As String
Dim strSourcePath As String

strSourcePath = fGetFilePath

If strSourcePath = "" Then
Exit Sub
End If

'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=" & strSourcePath & ";" & _
"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:=strSourcePath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""

End Sub

[Frosty]

I still don't know if your connection string is correct in your environment, but this will be closer than before. I still have concerns about that portion-- but you should be able to select a file properly now.

[dirtychinch]

Good morning, sir.

That works beautifully, now! As I understand it, there's a subtle difference in the way that you've attached the the file path to a variable. I didn't realise that you could retain that .SelectedItems(1) value outside of the with loop. But, am I right in saying that's only if it's defined as a function?

Are there clearly defined occasions on which you should use a function, as opposed to a sub? I don't understand the difference...

This works perfectly now, and is almost instantaneous, so thank you very very much!!

[dirtychinch]

OK, oddly enough it works very quickly when standalone, but when combined with my spreadsheet is very slow. Would you mind taking a look at my excel code here, which I use to run the macro? Oddly enough, it becomes slow once Word has its part, like it was doing before you amended my code for me.


Sub MailMerge()

Dim Word As Word.Application

'Ensures workbook saved

MsgBox ("This workbook will be automatically saved before continuing.")
ThisWorkbook.Save

'Create new quotation for template
Set Word = CreateObject("Word.Application")
Word.Documents.Add Template:="Q:\AirMaster\AirMaster Quotation.dotm", NewTemplate:=True, DocumentType:=0
Word.Visible = True


With Word
.Visible = True
.ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docm"
End With


Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub

[Frosty]

You've got four actions which can cause processing time: 2 saves. 1 open/new and a createobject. I assume you can't get around the saves and new (and speed of those obviously depends in size of document as well as network speed, since these appear to be on network locations).

CreateObject *always* creates a new instance of word. You're also not ending that instance of word in this code.

What you should do is try to use GetObjext first (to use an exising word process, if possible). And then if you do have to use CreateObject, make sure end your Word process, if appropriate (if you're just saving the document).

Have you checked your task manager? I'm guessing you have a lot of winword.exe processes in the processes tab. This will definitely slow down your computer over time too

[Frosty]

I'm actually not thrilled with the structure of this... You have the code for the mail merge being triggered from an AutoNew within the Word template, but are calling the code from Excel. Why not just have all the code in your excel template? It would be easier to debug and troubleshoot, unless you also need the ability to open the word document from word and have it automatically generate.

If you can describe the actual desired workflow, there may be a better way to go about what you're doing.

[dirtychinch]

Hi Frosty,

I don't think it's ideal either, but this was the best strategy that I could determine. Of course, I'm happy to take any advice you have about the method.

So the process is quite simple; we use an Excel spreadsheet to generate our quotations. Currently, the operators just copy an image of the quotation in excel, and drop it into a word document, which I think is unacceptable. What I want to be able to do is for them to complete the quotation in excel, as they currently do, and then just have them press a button, which will transfer the quotation into a word document.

I don't need to open the word document from word and have it automatically generate, but I do need to end up with a word document, if that makes any sense.

[Frosty]

They click a button in excel... Magic happens, and they end up with an active/open Word document? Which they can then cut/paste the contents of into another word doc?

i guess I'd have to see the mail merge, but I'm not sure why they start in excel... Why not have them do whatever they want in excel, then click a button in word and have the contents end up in their clipboard? Or pasted where the cursor is?

keep describing as completely as you can until I get a real picture of the process, then I'm betting you'll end up with something better than just "use GetObject before trying CreateObject" (which is the best and only advice I can give you, at the moment)

[dirtychinch]

Haha yes, magic indeed! They just want to churn figures out, which is fine by me.

I could have them copy and paste, but really want to automate the process because, frankly, people are incompetent, and take it upon themselves to modify things etc, and may not even have a rudimentary grasp of how copy & paste work - basically I need it to be as idiot proof as possible.

OK a better break down for you, though I'm not sure I can provide that much detail:

- User fills quotation using excel program. it's basically a process of selecting the unit they want, pressing a button, options come up, they select them

- press another button, and the information that they've just generated gets transferred (using VBA) from the input sheet to a summary sheet, which is a facsimile of the quotation

- repeat this process until the quotation is filled

- at this point they can amend the quotation as they wish, as long as they only touch certain cells. I've warned them off clicking on other things because I know that if they break a formula, they won't be able to fix it. I can only lock them down so far though

- user presses transfer to quote button, and then the mail merge happens

I hope that's helpful...

[Frosty]

And what should happen after the mail merge? User is left with a word document to do something with? Or do they just need the word document saved to a folder but not left open? Do they need to do anything in word? Or do they immediately go back to excel to generate another quote?

[Frosty]

Also, you can protect a sheet in excel, such that only certain cells are able to be edited. This is a good way to protect formulas

[dirtychinch]

I know of the protection, I just haven't set it up because I like them to have some ability to edit. It's the end result that concerns me. If they break the spreadsheet then they'll just have to start again, hah! (translation: I can't be bothered to protect it properly)

After the mail merge they have the word document with the filled quotation. This will then probably be copied and pasted into a letter, the contents of which I'm unsure of. I expect that they will want to save that document, but they don't necessarily need it to open up at that time. They may do another quote immediately after, but they may open the quotation to copy it into this letter...

[Frosty]

Fair enough. Well, I guess my only advice is try getobject first. Sample code is easy to find, or I can post it later. If you knew what your end users wanted to do after pressing the magic button, then you could skip showing word at all, and simply give an "output to where?" dialog, which your code could use for the later SaveAs process of the mail merge result.

I'd probably recommend pulling the code out of autonew of the word template, and putting it into excel (based on your excel code, you already have references to the word object model in your excel project, so this port will be relatively easy and be easier to debug later). Launching word and waiting for an auto macro to process could be slower than explicitly telling word to open a document and then run a mail merge.

I suspect you're doing this because you think you need to use ActiveDocument to trigger the mail merge. You don't. Use a document variable combined with documents.add to set the variable to the document you're going to open.

I'd also question why you're saving the result of the mail merge as a .docm file-- you don't need macros in that file, especially if they are likely to just cut and paste the contents. Use .docx instead.

Let us know how it goes.

[dirtychinch]

Hi Frosty,

Thanks for that. I'll have a look around for some GetObject code, though would appreciate it if you wouldn't mind showing me some code? I would be happy for the document not to open, to be honest. I think it would be best if it just had this save to where dialog.

The autonew code bit...I only did that because I didn't know what to do with integration between excel and word. If there's a way to do it in excel, and you wouldn't mind telling me how to do it, then I'd be more than happy to do that. I can't say that I like the autonew code at all.

I'll try and get it saving as docx. I'm not clear why I set it to docm in the first place, hah.

Thanks

[Frosty]

I just wrote this, but it's the basics... there are a lot of different ways to do this...

'encapsulates getting the word application, using GetObject first, and Create obect
Public Function fGetApp(Optional bCreated As Boolean) As Object
Dim oRet As Object

'attempt to get it, ignoring any error if it isn't launched
On Error Resume Next
Set oRet = GetObject(, "Word.Application")

'if we didn't get it, then attempt to create it, but reset error trapping
On Error GoTo 0
If oRet Is Nothing Then
Set oRet = CreateObject("Word.Application")
bCreated = True
End If

Set fGetApp = oRet
End Function


Usage in your existing code would be to do
Set Word = fGetApp
instead of
Set Word = CreateObject("Word.Application")

HOWEVER -- you should rename your variable "Word" to "appWord" ... otherwise you're using an object library ("Word") and a variable name for an application object ("Word") in the same project. Very bad practice.

From there, just try copy and pasting all of your Word code into your Excel project, and then try to run, work out kinks... post your results, and I'll help from there.

[dirtychinch]

Frosty,

Thanks for the code, and for the advice! I can never get over how helpful people on forums like these are.

This is my code now, for the mail merge only (all in Excel):



'encapsulates getting the word application, using GetObject first, and Create obect
Public Function fGetApp(Optional bCreated As Boolean) As Object
Dim oRet As Object

'attempt to get it, ignoring any error if it isn't launched
On Error Resume Next
Set oRet = GetObject(, "Word.Application")

'if we didn't get it, then attempt to create it, but reset error trapping
On Error GoTo 0
If oRet Is Nothing Then
Set oRet = CreateObject("Word.Application")
bCreated = True
End If

Set fGetApp = oRet
End Function
Sub MailMerge()


Dim appWord As Word.Application

MSG1 = MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm")


If MSG1 = vbYes Then
ElseIf MSG1 = vbNo Then
Exit Sub
End If

'Ensures workbook saved
ThisWorkbook.Save

'Create new quotation for template
Set appWord = fGetApp
Word.Documents.Add Template:="Q:\AirMaster\AirMaster Quotation1.dotm", NewTemplate:=True, DocumentType:=0
Word.Visible = True


With appWord
.Visible = True
.ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"
End With


Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub


I deleted all the code from my word document, as you advised. However, that code above asks me correctly whether I want to merge, and then asks me from which document, but it then just opens the word document and doesn't actually carry out the mail merge.

Should I be using this code also:


Sub MailMergeFromExcel()

Dim sConnection As String
Dim strSourcePath As String

strSourcePath = fGetFilePath

If strSourcePath = "" Then
Exit Sub
End If

'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=" & strSourcePath & ";" & _
"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:=strSourcePath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""

End Sub

[Frosty]

Your replaced the appWord variable... But then left Word.Documents.Add.

Use appWord instead, and also dim a new variable... A document variable.

Dim oDoc As Wird.Document.

Set oDoc = appWord.Documents.Add (Template:= yada yada)

then use that document object variable to run the mail merge. You'll probably need to pass as a parameter. I can't easily give you your code edited back at the moment, since I'm still typing on a phone.

Means yes, we're very helpful-- to someone with a great attitude, like you've brought. So thank you!

[SamT]

What is this supposed to be doing?

If .Show <> 0 Then
SelectedItems(1) = SourceDoc

because it looks like you are trying to set the filepicker output (read only) to be the same as an uninitialized variable (Value = "").


If .Show <> 0 Then
SourceDoc = SelectedItems(1)

[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.

[dirtychinch]

Your replaced the appWord variable... But then left Word.Documents.Add.

Use appWord instead, and also dim a new variable... A document variable.

Dim oDoc As Wird.Document.

Set oDoc = appWord.Documents.Add (Template:= yada yada)

then use that document object variable to run the mail merge. You'll probably need to pass as a parameter. I can't easily give you your code edited back at the moment, since I'm still typing on a phone.

Means yes, we're very helpful-- to someone with a great attitude, like you've brought. So thank you!

Frosty, I've made those changes but it still just opens the word document, and then does nothing. My code is now as follows:


'encapsulates getting the word application, using GetObject first, and Create obectPublic Function fGetApp(Optional bCreated As Boolean) As Object
Dim oRet As Object

'attempt to get it, ignoring any error if it isn't launched
On Error Resume Next
Set oRet = GetObject(, "Word.Application")

'if we didn't get it, then attempt to create it, but reset error trapping
On Error GoTo 0
If oRet Is Nothing Then
Set oRet = CreateObject("Word.Application")
bCreated = True
End If

Set fGetApp = oRet
End Function
Sub MailMerge()


Dim appWord As Word.Application

MSG1 = MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm")


If MSG1 = vbYes Then
ElseIf MSG1 = vbNo Then
Exit Sub
End If

'Ensures workbook saved
ThisWorkbook.Save

'Create new quotation for template
Set appWord = fGetApp
appWord.Documents.Add Template:="Q:\AirMaster\AirMaster Quotation1.dotm", NewTemplate:=True, DocumentType:=0
appWord.Visible = True


With appWord
.Visible = True
.ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"
End With


Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub

I don't understand what you mean about passing a parameter...

Also, do I need to dump this code?


Sub MailMergeFromExcel()

Dim sConnection As String
Dim strSourcePath As String

strSourcePath = fGetFilePath

If strSourcePath = "" Then
Exit Sub
End If

'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=" & strSourcePath & ";" & _
"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:=strSourcePath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""

End Sub


What is this supposed to be doing?

If .Show <> 0 Then
SelectedItems(1) = SourceDoc

because it looks like you are trying to set the filepicker output (read only) to be the same as an uninitialized variable (Value = "").


If .Show <> 0 Then
SourceDoc = SelectedItems(1)

Hi Sam, thanks but that code isn't in use any more. You are right though...I didn't quite understand what was going on with that code at the time.


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.

I did read the link, thank you. As I said before, I'd requested that the post on that forum be removed before I even joined this one.

[Frosty]

In MailMerge, add a variable:
Dim oMailMergeDoc As Word.Document
instead of appWord.Documents.Add yada yada
Set oMailMergeDoc = appWord.Documents.Add (yada yada)
(You'll need to use parens when setting your document object to the result of the documents.add function)

now you have your document variable populated with the document you're about to tell to perform a mailmerge. Use the document variable to do the save, not the appWord.ActiveDocument.

Change your MailMergeToExcel so it can accept a document parameter, like so:
Sub MailMergeToExcel (oDoc As Word.Document)

within the routine, replace the use of ActiveDocument with your passed document object variable for the actual mail merge.

Now back to MailMerge, add the following call to your newly parameterized sub...

MailMergeToExcel oMailMergeDoc

All the code should be in
excel, including the stuff you were asking if you should dump (that's the mail merge code, after all!)

step through the code and see what happens.

[dirtychinch]

Hi Frosty,

Thanks, once again. I've amended the code to what I think you're telling me (a few terms in there that I don't quite understand, but I've done my best to make sense of).

The fGetApp function doesn't seem to do anything, as far as I can tell.

At the bit that's commented "'Create new quotation for template" the word document opens.

As far as I can tell, at this point I cannot choose the source document (I don't know whether it's just using the active document or not), and from that point nothing else happens. I think I may have put the commands for "MailMergeToExcel oMailMergeDoc" in wrong though, as I just copied that straight in...

Here's my code, anyway:



'encapsulates getting the word application, using GetObject first, and Create obect
Public Function fGetApp(Optional bCreated As Boolean) As Object
Dim oRet As Object

'attempt to get it, ignoring any error if it isn't launched
On Error Resume Next
Set oRet = GetObject(, "Word.Application")

'if we didn't get it, then attempt to create it, but reset error trapping
On Error GoTo 0
If oRet Is Nothing Then
Set oRet = CreateObject("Word.Application")
bCreated = True
End If

Set fGetApp = oRet
End Function
Sub MailMerge()


Dim appWord As Word.Application
Dim oMailMergeDoc As Word.Document

MSG1 = MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm")


If MSG1 = vbYes Then
ElseIf MSG1 = vbNo Then
Exit Sub
End If

'Ensures workbook saved
ThisWorkbook.Save

'Create new quotation for template
Set appWord = fGetApp
Set oMailMergeDoc = appWord.Documents.Add("Q:\AirMaster\AirMaster Quotation.dotm")
appWord.Visible = True


With appWord
.Visible = True
.ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"
End With

MailMergeToExcel oMailMergeDoc


Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub
Sub MailMergeToExcel(oDoc As Word.Document)

Dim sConnection As String
Dim strSourcePath As String

strSourcePath = oMailMergeDoc

If strSourcePath = "" Then
Exit Sub
End If

'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=" & strSourcePath & ";" & _
"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:=strSourcePath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""

End Sub


:beerchug:

[Frosty]

The ActiveDocument.MailMerge line of code, with all the parameters I did line breaks for you on... Use the document variable you are now passing to the sub routine "MailMergeToExcel"

oDoc.MailMerge.OpenDataSource etcetc

you're close.

fGetApp doesn't need to do anything if Word is already open... It just provides a "handle" to the word application in order to create documents and run mail merges. Providing that handle doesn't take much time if word is already open.

[Frosty]

Also, you're clearly not using option explicit at the top of your modules. Put that at the top of each code module (there is a setting to do this automatically) under tools/options.

Option Explicit will help you more than any forum. That will help you see other errors in your code... But you need to step through. Alternatively, if you think you're having trouble because of porting to excel, copy the mail merge code back into word, and try it out. Since it has a parameter, you'll either need to create a parameter-less routine that just contains one line:
MailMergeToExcel ActiveDocument
Or you could type that in the immediate window to test (all in Word VBA instead if Excel VBA.

[dirtychinch]

Hi Frosty,

I turned on "Require Variable Declaration" but nothing has actually happened. I think that was what you were meaning?

It still runs through, but there is still no source document, so it's getting to


If strSourcePath = "" Then Exit Sub
End If

And exiting.

This is my code now:


'encapsulates getting the word application, using GetObject first, and Create obect
Public Function fGetApp(Optional bCreated As Boolean) As Object
Dim oRet As Object

'attempt to get it, ignoring any error if it isn't launched
On Error Resume Next
Set oRet = GetObject(, "Word.Application")

'if we didn't get it, then attempt to create it, but reset error trapping
On Error GoTo 0
If oRet Is Nothing Then
Set oRet = CreateObject("Word.Application")
bCreated = True
End If

Set fGetApp = oRet
End Function
Sub MailMerge()


Dim appWord As Word.Application
Dim oMailMergeDoc As Word.Document

MSG1 = MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm")


If MSG1 = vbYes Then
ElseIf MSG1 = vbNo Then
Exit Sub
End If

'Ensures workbook saved
ThisWorkbook.Save

'Create new quotation for template
Set appWord = fGetApp
Set oMailMergeDoc = appWord.Documents.Add("Q:\AirMaster\AirMaster Quotation.dotm")
appWord.Visible = True


With appWord
.Visible = True
.ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"
End With

MailMergeToExcel oMailMergeDoc


Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub
Sub MailMergeToExcel(oDoc As Word.Document)

Dim sConnection As String
Dim strSourcePath As String

strSourcePath = oMailMergeDoc

If strSourcePath = "" Then
Exit Sub
End If

'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=" & strSourcePath & ";" & _
"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
oMailMergeDoc.MailMerge.OpenDataSource _
Name:=strSourcePath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""

End Sub

[Frosty]

Here are some modifications, and clarifications... try this out. This is the ENTIRETY of my module in excel -- notice the Option Explicit at the top...

Option Explicit
'-----------------------------------------------------------------------------------------------------
Public Sub MailMergeMain()

Dim appWord As Word.Application
Dim oMailMergeDoc As Word.Document
Dim strSourcePath As String

'verify no mis-clicks? Perhaps this is where you should get the path instead...
strSourcePath = fGetFilePath
'if blank, user hit cancel... double-check? Or just comment out the msgbox logic and exit
If strSourcePath = "" Then
If MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm") = vbNo Then
Exit Sub
End If
End If

'Ensures workbook saved
ThisWorkbook.Save

'Create new quotation for template
Set appWord = fGetApp
'make it visible
appWord.Visible = True
'create a new document based on a hard coded path? Should you offer a choice here?
Set oMailMergeDoc = appWord.Documents.Add("Q:\AirMaster\AirMaster Quotation.dotm")
'Save the document with a particular name and path
oMailMergeDoc.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"

'run the mail merge
MailMergeToExcel oMailMergeDoc, strSourcePath

'why is this here?
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub
'-----------------------------------------------------------------------------------------------------
'function to return the path of a single selected file
Public Function fGetFilePath() As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
'.Filters.Add "My Filter", "*.dotm"
.Filters.Add "Excel Macro-Enabled Workbook", "*.xlsm"
If .Show = 0 Then
Exit Function
End If
fGetFilePath = .SelectedItems(1)
End With

End Function
'-----------------------------------------------------------------------------------------------------
'encapsulates getting the word application, using GetObject first, and Create obect
Public Function fGetApp(Optional bCreated As Boolean) As Object
Dim oRet As Object

'attempt to get it, ignoring any error if it isn't launched
On Error Resume Next
Set oRet = GetObject(, "Word.Application")

'if we didn't get it, then attempt to create it, but reset error trapping
On Error GoTo 0
If oRet Is Nothing Then
Set oRet = CreateObject("Word.Application")
bCreated = True
End If

Set fGetApp = oRet
End Function
'-----------------------------------------------------------------------------------------------------
'main mail merge routine
Public Sub MailMergeToExcel(oDoc As Word.Document, Optional strSourcePath As String)

Dim sConnection As String

'if we didn't pass in a value, then ask the user
If strSourcePath = "" Then
strSourcePath = fGetFilePath
End If

'if still no info, then indicate that we've cancelled
If strSourcePath = "" Then
'need to inform the user, since an emptry string means nothing was chosen from fGetFilePath
MsgBox "Action Cancelled", vbInformation, "MailMergeToExcel"
Exit Sub
End If

'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=" & strSourcePath & ";" & _
"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
oDoc.MailMerge.OpenDataSource _
Name:=strSourcePath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""

End Sub

[Frosty]

I should have clarified... requiring variable declaration only adds option explicit automatically to the top of *new* code modules... not existing ones.

[dirtychinch]

Wow, that works perfectly Frosty, thank you very much :D I'll have a look through the changes that you made.

Ah right, that makes sense now. I was waiting for some sort of magic to happen and all these option explicit tags to appear everywhere :p

[dirtychinch]

With regards to you comment in the code, I don't want the users to be able to select the template - each dept. has their own quotation program so I'll just make it fixed. The main reason why i'm doing this is to stop them using their on style of quotes, hah!

[Frosty]

Well, in that case, you should encapsulate that with a hard code, so that you can copy paste updates to the code to each of your different locations. I don't know how many departments you have, but this would be a better structure...

'-----------------------------------------------------------------------------------------------------
Public Sub MailMergeDeptA()
MailMergeMain "Q:\AirMaster\AirMaster Quotation.dotm"
End Sub
'-----------------------------------------------------------------------------------------------------
Public Sub MailMergeDeptB()
MailMergeMain "Q:\someotherstyle\otherstyle.dotm"
End Sub
'-----------------------------------------------------------------------------------------------------
Public Sub MailMergeMain(sQuotationDocPath As String)

Dim appWord As Word.Application
Dim oMailMergeDoc As Word.Document
Dim strSourcePath As String

'verify no mis-clicks? Perhaps this is where you should get the path instead...
strSourcePath = fGetFilePath
'if blank, user hit cancel... double-check? Or just comment out the msgbox logic and exit
If strSourcePath = "" Then
If MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm") = vbNo Then
Exit Sub
End If
End If

'Ensures workbook saved
ThisWorkbook.Save

'Create new quotation for template
Set appWord = fGetApp
'make it visible
appWord.Visible = True
'create a new document based on a hard coded path? Should you offer a choice here?
Set oMailMergeDoc = appWord.Documents.Add(sQuotationDocPath)
'Save the document with a particular name and path
oMailMergeDoc.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"

'run the mail merge
MailMergeToExcel oMailMergeDoc, strSourcePath

'why is this here?
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub

[Frosty]

And then I would be really really hesitant to deploy this to multiple departments as .docm and excel macro-enabled spreadsheets (.xlsm) ... what you're doing is deploying spreadsheet documents with whatever version of the macro when the user saved the document. This can create a nightmare.

What you should do is have a single excel addin, which contains your code... and is deployed to each of your end-user's machine... or only accessible in a single template, which everyone opens in order to have access to the code, and you have on the network as a read-only copy of your own code.

The line "ThisWorkbook.Save" concerns me, because it means people are saving the macros in these individual spreadsheets... which means they could easily be using outdated code. For example, let's say your network engineers take down the Q:\ drive and decide to call it the X:\ drive. You need to update your code... so you do. But a number of your users are in-process on a bunch of spreadsheets with macros in them... which no longer work.

[Aussiebear]

As I said before, I'd requested that the post on that forum be removed before I even joined this one.

Please don't ever request that a post be removed from this forum.

[Frosty]

Yeah, although to be fair... even the best-intentioned person has an issue getting around the rule of linking to other cross-posts, but not being able to do internet links until a certain post-count threshold is met. From what I see, dirty chinch has brought a good attitude and willingness to learn, as well as having made an effort to post to an appropriate location after initially posting to the wrong place. He *could* have indicated in his original post that he also asked this question at another forum, but I think everyone's on the same page at this point.

[dirtychinch]

Well, in that case, you should encapsulate that with a hard code, so that you can copy paste updates to the code to each of your different locations. I don't know how many departments you have, but this would be a better structure...

'-----------------------------------------------------------------------------------------------------
Public Sub MailMergeDeptA()
MailMergeMain "Q:\AirMaster\AirMaster Quotation.dotm"
End Sub
'-----------------------------------------------------------------------------------------------------
Public Sub MailMergeDeptB()
MailMergeMain "Q:\someotherstyle\otherstyle.dotm"
End Sub
'-----------------------------------------------------------------------------------------------------
Public Sub MailMergeMain(sQuotationDocPath As String)

Dim appWord As Word.Application
Dim oMailMergeDoc As Word.Document
Dim strSourcePath As String

'verify no mis-clicks? Perhaps this is where you should get the path instead...
strSourcePath = fGetFilePath
'if blank, user hit cancel... double-check? Or just comment out the msgbox logic and exit
If strSourcePath = "" Then
If MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm") = vbNo Then
Exit Sub
End If
End If

'Ensures workbook saved
ThisWorkbook.Save

'Create new quotation for template
Set appWord = fGetApp
'make it visible
appWord.Visible = True
'create a new document based on a hard coded path? Should you offer a choice here?
Set oMailMergeDoc = appWord.Documents.Add(sQuotationDocPath)
'Save the document with a particular name and path
oMailMergeDoc.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"

'run the mail merge
MailMergeToExcel oMailMergeDoc, strSourcePath

'why is this here?
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub



And then I would be really really hesitant to deploy this to multiple departments as .docm and excel macro-enabled spreadsheets (.xlsm) ... what you're doing is deploying spreadsheet documents with whatever version of the macro when the user saved the document. This can create a nightmare.

What you should do is have a single excel addin, which contains your code... and is deployed to each of your end-user's machine... or only accessible in a single template, which everyone opens in order to have access to the code, and you have on the network as a read-only copy of your own code.

The line "ThisWorkbook.Save" concerns me, because it means people are saving the macros in these individual spreadsheets... which means they could easily be using outdated code. For example, let's say your network engineers take down the Q:\ drive and decide to call it the X:\ drive. You need to update your code... so you do. But a number of your users are in-process on a bunch of spreadsheets with macros in them... which no longer work.

Hi Frosty. As always, thank you very much.

When I say it's going to be deployed to multiple departments, it won't be with the same program, although the structure would be the same. I don't want to have to put an add in on each person's computer, and they can't necessarily be trusted to do it themselves. When you say deployed to their machines though, what exactly are we talking about?

With regards to "ThisWorkbook.Save" they could indeed be using outdated code. However, the way I see it is that each time they do a quote, if the program is updated then they just continue to use that program (probably not even noticing the updates). They would also always be using the template document on the network; never a local copy. I do understand your concern, but there will only be one operator for the program (perhaps two) and the company isn't very big so it would be easy for me to get the word to them to hold off while I adjust the code.

I mean, is there really an alternative?


Please don't ever request that a post be removed from this forum.

Don't worry - I won't! I had a hard enough time finding any decent information about mail merging using VBA. This thread would have been a gold mine for me last week!!


Yeah, although to be fair... even the best-intentioned person has an issue getting around the rule of linking to other cross-posts, but not being able to do internet links until a certain post-count threshold is met. From what I see, dirty chinch has brought a good attitude and willingness to learn, as well as having made an effort to post to an appropriate location after initially posting to the wrong place. He *could* have indicated in his original post that he also asked this question at another forum, but I think everyone's on the same page at this point.

Thank you for sticking up for me :p Indeed, I realised at some point that perhaps I wasn't supposed to be coding in excel. The error was mine, and I had no idea that the forums were interconnected.

Anyway, I shan't be cross posting again, don't worry!!

[dirtychinch]

Frosty,

I'm having a problem; once the document has been created, it firstly doesn't toggle the preview (not too big a deal), but also doesn't actually end the mail merge, which seems to slow everything down. Would it be possible to include this (both if possible) in the code somehow? I'm guessing that may have to be done in word.

Thanks,

dirtychinchilla

[dirtychinch]

Also, would it then be possible to sever the link between the source document and the word document. The reason why I ask is that every time you open the document after it has completed the mail merge, it asks if you want to update etc. I just want to sever this tie and tell the operators that if they change the quote, just press the button again.

[Frosty]

Sorry, I missed these responses. I'm not 100% sure what you're asking/describing with it "doesn't actually end the mail merge, which seems to slow everything down." I'm also not sure about toggling the preview... I guess I'm a little lost on where you're at on this (things go in and out of my brain). But I've never actually tried to run this code, I've just assumed it's been running. When I start looking at the .MailMerge object, I'm not sure if .OpenDataSource is actually the method you want to use.

You may want to use the .Execute method off the .MailMerge object, or you might want to make some sort of use of .MailMerge.DataSource.Close.

I was mostly trying to streamline your code, and help you get it all into Excel.... but maybe there was something else having the Word code in AutoNew. At the end of the day, you want to save the RESULTS of the mail merge, rather than a copy of the mail merge template, right?

if you've already solved, maybe try posting some of the code which has gotten you closer. I'd have to take a good bit of time to try and reconstruct this whole scenario from scratch, even with the code above.

[dirtychinch]

Morning Frosty, not to worry! I knew you'd stumble upon them eventually. Ever the hero :D

My code has not changed at all since the last lot that was posted. I was happy that it worked enough for it to be operable, but the operator is finding it a little problematic, and has raised a few issues.

You're absolutely correct about me wanting the results of the merge - I just need a word document with the text in it from my spreadsheet!!

When I said about ending the merge, I mean that firstly it doesn't toggle the preview, which was what AutoNew was doing (badly). Secondly, the documents still seem connected. What I mean by this is that if you were to reopen the word document, it would always ask if you want to refresh the source (not sure of the exact terminology), which isn't ideal. I think you're idea of using .Execute may work, as that sounds like the process would be closed by the end of it. However, if I change .OpenDataSource to .Execute, then "Named argument not found" and the debugger selects "Name:=strSourcePath". I thought it may be as a consequence of it not being defined, but it very much is defined.

One thing I've been trying to get it to do is to add a random number to the name string, to avoid an error whereby you may already have a quote open with an identical name. Additionally, the file names/destinations can be quite long, as some quotations are stored deep within a server here. As a consequence, almost always I get the error with regards to the character string being more than 255 characters. Is there some way round this as it's become quite problematic?

[Frosty]

Only way around too long of file names is mapping a drive to that location as a way of shortening the overall string. Well, that's not entirely true, there is also a "short name" property that includes a tilde and a number, so that c:\Program Files is also accessible via C:\Progra~1. But that's a really dodgy way to approach, as the tilde is constant, but the number is incremented by the presence
of other folders with the same first 6 letters. I don't know what it does if there are 7 of the same... Maybe goes alpha.

Random number is easy to generate, but you're already dealing with long file names, maybe it would be better to simply check if the file name suggested is already there, and then simply increment up a number as a suffix, check again, etc. random numbers in file names are pretty annoying.

As as for the other (.execute, etc). Now is the time to start reading the help file. Put your cursor inside the .Execute part of the line, and press F1. Or look it up online.... Eventually you'll end up at an msdn link. This is the other way to learn how to use objects properly (you got this far using the first way: record a macro doing what you want). Now you know what you want, and the recorded macro doesn't get you far enough. So read up in help!

[dirtychinch]

Hi Frosty,

You say mapping a drive to that location...what does this mean? The other solution doesn't sound wonder - I'd rather have the solid one!

With regards to the random number generation/checking if a file was there, how would one check that a file is already open with that name? My thinking is basically to force it to close any word documents, but this isn't ideal if they're working on something else.

I had a look at the msdn link but it doesn't mention anything do with the name issue...

[Frosty]

Mapping a drive is something you can do in Windows Explorer, manually or automatically via another script... and say you have a network location which is
\\mynetworkshare\afolder\asubfolder\

You can "map" to that network location, and call it the J:\ drive (or all the way up to Z:\). Some mapped drives are hard coded (like A:\ and B:\ traditionally used for floppy drives, C:\ as the hard drive, D:\ and maybe E:\ as CD-ROM drives, etc etc). If you have any network drives (usually something from F:\ or G:\ on up), then you'll see what the "true" path to those network drives is.

If you had a really long location, even if it was already a mapped drive, you could map to the new location...
for example:
F:\subfolder\anothersubfolder\anotherlongsubfoldernamewhichisentirelyridicu lous\shortsub

You could map your Z:\ to be all the way to that ShortSub folder at the end... and then any code could reference the Z:\ in order to get around long file name limitations. I'm sure you could do this in some fashion from VBA, but I'd be a little hesitant to do it dynamically as it could be slow, and network engineers generally like to own the mapping of network drives.

Incidentally, if this is a major company-wide problem in terms of really deeply nested sub folders without the use of mapped drives... you've got bigger issues. Seriously. You'll be unable to perform certain kinds of network backups and a host of other issues far beyond the scope of this thread.

You can use the FileSystemObject scripting to detect whether a file exists or not-- whether it is "locked" (i.e., open by someone else) is a separate issue, although I don't think that matters, since we're talking about saving a document not opening a document. There are a lot of different ways to detect whether a file exists, try searching on it. My only readily available code on that is buried in a class object I re-use in all my projects.

[Frosty]

Ron de Bruin has some good code on testing whether a file exists (and generally has great resources for this stuff, as does Greg Maxey's site, and Chip Pearson's).
http://www.rondebruin.nl/win/s9/win003.htm
http://gregmaxey.mvps.org/word_tips.html
http://www.cpearson.com/Excel/Topic.aspx

Only the rondebruin site has something specific to this topic, but greg has some excellent tips and accessible code. Chip Pearson has some more advanced stuff, but I mention it because if you find his code when doing google searches, it's pretty great.

[snb]

I prefer to:

Design a Worddocument
Include the desired mergefields.
Link the document to the Excelfile that contains the 'database'.
Save that worddocument.(e.g. G:\OF\Maindocument.docx )

The only code in VBA you will need is:


Sub M_snb()
with getobject("G:\OF\Maindocument.docx")
.Mailmerge.execute
.clode 0
end with
End Sub

Only the resulting merged documents will be shown in Word.

If you nevertheless prefer to do the redundant combining of maindocument & database by VBA from Excel you only need:
NB. You can speed up the code considerably by commenting out: .windows(1).visible=true


Sub M_snb()
With CreateObject("word.document")
.Windows(1).Visible = True
.Content = String(20, vbCr)
For j = 1 To Cells(1).CurrentRegion.Rows.Count
.Fields.Add .Paragraphs(j).Range, 59, Cells(1, j).Text
Next
With .MailMerge
.MainDocumentType = 0
.Destination = 0
.SuppressBlankLines = True
.OpenDataSource ThisWorkbook.FullName
.Execute
End With
End With
End Sub

[dirtychinch]

Hi guys,

I must apologise profusely with regards to my lack of reply. I have no excuse, other than laziness, and the fact that the code was all working fine!

Frosty, thank you for the information on mapping, and the detecting of documents. Fortunately, I have managed to get around the mapping issue by having the macro create a temporary document on the desktop of the user, which they can then delete (or if I could get it to work, would be deleted automatically).

snb, thank you very much for the code. I would love to use your simple method, but when I run this code:


Sub M_snb() With GetObject("Q:\Index\Documents\FlatStation Quotation.dotm")
.MailMerge.Execute
.clode 0
End With
End Sub

which refers to the document that I want to merge with, I get the runtime error 4605 on the .MailMerge.Execute line for some reason. It says that the mail merge needs a data source, but I thought that had been set with getobject...have you any idea why this might be?

Another problem, in addition, is that really the document to come from a template file, but am not sure how I might do this. Do you have any ideas?

Thanks, and sorry again for the lack of reply.

dirtychinchilla

[dirtychinch]

I have an entirely different problem now, which occurs with either the complex mail merge or this simple one, if I can get it to work.

I have the Microsoft Word 14.0 Object Library switched on, which is the one used in the mail merge code. However, when I run the merge I get the very aggressive "Error has occurred: External table is not in the expected format." and then it asks me about data connections and stuff.

Please help!! This is extremely annoying as I can't seem to guarantee that I can have this run out of the box on anyone's PC.

The issue goes away if I save the excel document as .xlt, but this is not ideal considering everyone here has Office 2010, and there is some functionality that I'd like to retain, which is lost if I save as .xlt.

Further to this, I still would like to get the mail merge to execute as opposed to leaving the connection open. I think snb's code would resolve this, if I could get it to work...

[snb]

The answer lies in the first 4 lines of this post:

http://www.vbaexpress.com/forum/showthread.php?48989-Run-time-error-5922-Mail-Merge&p=306594&viewfull=1#post306594

If the worddocument isn't a mailmerge maindocument, nothing will happen.

amended a typo


Sub M_snb()
With getobject("G:\OF\Maindocument.docx")
.Mailmerge.execute
.close 0
End With
End Sub

[dirtychinch]

Hi snb,

Thanks for your response. I have made the word document, included the desired merge fields, and then I get stuck. You say link the file - surely it was linked when I managed to put the merge fields in, by carrying out most of a mail merge in excel.

dirtychinchilla

[dirtychinch]

Sorry for the triple post, but in addition to this, I'm wondering if there's a way to add the necessary reference libraries for any user, as I don't want to have to go and enable it on every PC manually. I found the following code online somewhere, and modified it a tiny bit:


Sub AddReference() 'Macro purpose: To add a reference to the project using the GUID for the
'reference library

Dim strGUID As String, theRef As Variant, i As Long

'Update the GUID you need below.
strGUID = "{00062FFF-0000-0000-C000-000000000046}"

'Set to continue in case of error
On Error Resume Next

'Add the reference
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUID, Major:=1, Minor:=0

'If an error was encountered, inform the user
Select Case Err.Number
Case Is = 32813
'Reference already in use. No action necessary
Case Is = vbNullString
'Reference added without issue
Case Else
'An unknown error was encountered, so alert the user
MsgBox "A problem was encountered trying to" & vbNewLine _
& "add or remove a reference in this file" & vbNewLine & "Please check the " _
& "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
End Select
On Error GoTo 0
End Sub


However, this seems to do absolutely nothing!! I tried various pieces of code, and none of them actually did anything. Also, I really struggled finding a library of the GUID's, so if anyone could advise me on that, it would be much appreciated.

[dirtychinch]

Hey guys,

I've made some advancement going on what snb provided. This is my code now:


Function NewMerge()

On Error GoTo ErrHandler:


ThisWorkbook.SaveCopyAs Filename:="C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls"


With GetObject("Q:\Index\Documents\FlatStation Quotation.dotm")
.Mailmerge.Execute
.Close 0
End With

Kill ("C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls")

'Skip error handler
Exit Function


ErrHandler:
Kill ("C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls")
MsgBox ("Run-time Error " & Err.Number)
Exit Function

End Function

I changed the sub to be a function, which meant that I could call the function from elsewhere, and assign the calling macro to a button.

This code works beautifully, for the most part. Having it both open and delete the temp file is great; this wasn't possible before because I was using mailmerge.opendatasource, leaving the files connected, and as such the temp file was always in use, whilst the word document was open.

However, the problem I now have is that I really need to be able to set the data source for the mail merge to be this temp file. I had done that in Word, but because I need this to work on other people's computers and the document must be referring to my own temp file on my desktop, I cannot fully execute the merge. Does anyone know a way round this?

Edit:

I tried a different bit of code that I found online, modified as follows:


Function MergeIt()

Dim objWord As Word.Document
Set objWord = GetObject("Q:\Index\Documents\FlatStation Quotation.dotm")


' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source.
objWord.MailMerge.OpenDataSource _
Name:="C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls", _
LinkToSource:=True, _
Connection:="sConnection", _
SQLStatement:="SELECT * FROM `MailMerge`"


' Execute the mail merge.
objWord.MailMerge.Execute


End Function

This also works and allows me to determine the data source, but then when it carries out the execution, it leaves the original word document open, and then creates the merged document. It's just a Frankenstein combo of the previous two methods...and doesn't work perfectly either!

[snb]

A function has not been designed for that purpose, a sub has.

You can call any macro simply by using it's name:


Sub M_start()
M_snb
End Sub

Sub M_snb()
With getobject("G:\OF\Maindocument.docx")
.Mailmerge.execute
.close 0
End With
End Sub

[dirtychinch]

Mmm I realise now that it won't be possible to execute the merge and select a data source, unfortunately. More work for the operators!

I know how to call a macro, but it was because the macro is in ThisWorkbook, and it seems you cannot call from there?

[snb]

You can 'cal' from everywhere.


Sub M_start()
ThisWorkbook.M_snb
End Sub


In thisworkbook codemodule:


Sub M_snb()
With getobject("G:\OF\Maindocument.docx")
.Mailmerge.execute
.close 0
End With
End Sub

if the macro is in the sheet1 codemodule:


Sub M_start()
Sheet1.M_snb
End Sub

[dirtychinch]

Thanks for that, snb. I didn't end up needing it in the end :)

I'm glad to say that I've mostly got the mail merge working to my satisfaction, but there doesn't seem to be a great deal of consistency and reliability for the program.

My code:


Sub MailMerge()

Dim objDoc As String

objDoc = "Q:\Temp\FSTemp.xls"

On Error GoTo ErrHandler:


If MsgBox("Do you want to continue with the mail merge? If nothing happens, check whether a dialog box has opened in the background, or wait 30 seconds and try again. When the dialog box opens, please press yes to accept the mail merge.", vbYesNo, "Confirm") = vbNo Then
Exit Sub
End If

ThisWorkbook.SaveCopyAs Filename:=objDoc

With CreateObject("Q:\Index\Documents\FlatStation Quotation.dotm")
.MailMerge.Execute
.Close 0
End With

Kill objDoc

'Skip error handler
Exit Sub

ErrHandler:
Kill objDoc
MsgBox ("Run-time Error " & Err.Number)
Exit Sub

End Sub


The first problem is that the dialog box for MS Word appears in the background, and you have to alt+tab to it to get it to work, which isn't great.

Secondly, although the user will then press yes to accept the merge, sometimes MS Word just doesn't open up, although the process might be running in the background. This is causing me a real headache because it gets into a loop whereby every time you run the macro, MS Word is missing.

Any help would be very very much appreciated, as I finally feel that I'm nearly ready to launch the updated programs (7 in all).

[snb]

With CreateObject("Q:\Index\Documents\FlatStation Quotation.dotm")
.Application.visible=true
.MailMerge.Execute
.Close 0
End With

PS. What's the benefit of the combination 'savecopyas' & 'Kill' ?

[dirtychinch]

Hi snb, thanks for that. I'm not entirely sure it works, but I'll keep bashing away in hope of doing it.

I can't remember why I do the copy and kill. I think it's something to do with maximising compatibility. If I remember, I'll let you know :p

[dirtychinch]

snb, can you think of any way to name the word document automatically, or at least give it a name other than "Letter1"?

Edit: just remembered why I do the savecopyas, and kill. The problem I had is that when someone is doing the mail merge, they could be on any computer in the building, or any excel document that they're working on could be stored anywhere at all. This makes it impossible to link the word template to the document properly. So, I just made the destination of the file fixed, by using this temp file, thereby preventing any problems with determining the file location. It works brilliantly :D I do the kill just to clean up afterwards.

Edit2: Just found this, and I'm wondering if perhaps it can be adapted to work from Excel to Word:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=122

I'm yet to look into it properly, but it's food for thought.

[snb]

With CreateObject("Q:\Index\Documents\FlatStation Quotation.dotm")
.Application.visible=True
.MailMerge.Execute
.application.activeworkbook.saveas2 "G:\OF\invoices " & format(date,"yyymmdd") & ".docx"
.Close 0
End With

[dirtychinch]

snb, thanks a lot for that bit of code. As I understand it, though, that would mean that you have to save it first? I thought you might be able to determine the name before you save the document. Thinking about it now, I guess that it's unlikely to work, but normally with documents, if you save a template it uses the template's name. Is this possible? It could even be that the dialogue box asking for save location immediately pops up.

I love your solution, but I'm finding it a tad unreliable and wonder if you have any suggestions. Still, despite the application.visible command, the dialogue box doesn't come to the front. I often have to alt+tab to get to it. This is absolutely fine for me, but other users are so competent/aware. Also, for the first time I run the macro each day, the dialogue box comes up, but then word doesn't come up. very weird!

I really appreciate your help, sir. It's been genuinely excellent for me to have your help.