Run-time error '5922' - Mail Merge

[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...
[vba]
'-----------------------------------------------------------------------------------------------------
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
[/vba]

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 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!!