Run-time error '5922' - Mail Merge

**Frosty** · 02-26-2014, 09:32 AM

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** · 02-26-2014, 04:03 PM

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** · 02-27-2014, 12:22 AM

Originally Posted by dirtychinch

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** · 02-27-2014, 01:33 AM

Originally Posted by 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!

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

Originally Posted by 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)

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.

Originally Posted by Aussiebear

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** · 02-27-2014, 07:03 AM

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** · 02-27-2014, 08:00 AM

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

**Frosty** · 02-27-2014, 08:15 AM

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** · 02-27-2014, 08:23 AM

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** · 02-27-2014, 08:42 AM

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** · 02-27-2014, 08:51 AM

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

**Frosty** · 02-27-2014, 08:52 AM

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

**dirtychinch** · 02-27-2014, 08:59 AM

Wow, that works perfectly Frosty, thank you very much

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

**dirtychinch** · 02-27-2014, 09:16 AM

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** · 02-27-2014, 09:31 AM

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]

**Frosty** · 02-27-2014, 09:36 AM

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** · 02-27-2014, 04:25 PM

Originally Posted by dirtychinch

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** · 02-27-2014, 04:41 PM

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** · 02-28-2014, 04:03 AM

Originally Posted by 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...
[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]

Originally Posted by 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.

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?

Originally Posted by Aussiebear

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

Originally Posted by 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.

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

**dirtychinch** · 02-28-2014, 05:13 AM

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** · 02-28-2014, 07:44 AM

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.

Thread: Run-time error '5922' - Mail Merge

Thread Tools

Display

Posting Permissions