Mail merge leaves excel process running

[Anthony]

Hi,

I amtrying to mail merge information from a worksheet into a mail merge word
document. Everything works fine, except it laves a process running once I
quit excel.

The mail merge is called from the same file as the informationto merge and
excel is closed manually by the user.

The code works correctly in 07 (well no process I can see is left), but it
needs to run in excel 2003 and this is where the left over process is
happening.

the code is as follows;

Sub print_Click()

Dim FName As String
Dim appWD As Object ' Word.Application
Dim UserN As String
Dim DesktopPath As String

On Error Resume Next

Application.StatusBar = "Starting mail merge ..."

FName = Dir(ThisWorkbook.Path & "\addresses.xls")

If appWD Is Nothing Then
Set appWD = CreateObject("Word.Application") ' New Word.Application
End If
appWD.Documents.Open Filename:=ThisWorkbook.Path & "\label
merge-auto.doc"

With appWD.ActiveDocument.MailMerge
.OpenDataSource Name:=ThisWorkbook.Path & "\" & FName,
sqlstatement:="SELECT * FROM [print list$]"
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute Pause:=False
End With

Application.StatusBar = "Now creating document for " & Left(FName,
Len(FName) - 4)

appWD.ActiveDocument.SaveAs (ThisWorkbook.Path & "\merged" &
Format(Date, "dd-mm-yyyy") & "-week" & _
Worksheets("label order").Range("Q4").Value),
FileFormat:=wdFormatDocument
appWD.ActiveDocument.Close


appWD.Documents("label merge-auto.doc").Close savechanges:=False

appWD.Quit

Set appWD = Nothing

Application.StatusBar = False

End Sub

 

[JLGWhiz]

You need to make sure that all files are closed other than the one
containing the code. Otherwise VBA will not allow the application to close.
It thinks you are still working.

 

[Anthony]

The code closes the only file opened, which is the word document. and the
user closes the excel file.

No files are left open, but the excel process still remains active.

The workbook used as the data source is the boo k with the code in it.

Regards

Anthony

You need to make sure that all files are closed other than the one
containing the code. Otherwise VBA will not allow the application to
close. It thinks you are still working.

Hi,

I amtrying to mail merge information from a worksheet into a mail merge
word document. Everything works fine, except it laves a process running
once I quit excel.

The mail merge is called from the same file as the informationto merge
and excel is closed manually by the user.

The code works correctly in 07 (well no process I can see is left), but
it needs to run in excel 2003 and this is where the left over process is
happening.

the code is as follows;

Sub print_Click()

Dim FName As String
Dim appWD As Object ' Word.Application
Dim UserN As String
Dim DesktopPath As String

On Error Resume Next

Application.StatusBar = "Starting mail merge ..."

FName = Dir(ThisWorkbook.Path & "\addresses.xls")

If appWD Is Nothing Then
Set appWD = CreateObject("Word.Application") ' New Word.Application
End If
appWD.Documents.Open Filename:=ThisWorkbook.Path & "\label
merge-auto.doc"

With appWD.ActiveDocument.MailMerge
.OpenDataSource Name:=ThisWorkbook.Path & "\" & FName,
sqlstatement:="SELECT * FROM [print list$]"
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute Pause:=False
End With

Application.StatusBar = "Now creating document for " & Left(FName,
Len(FName) - 4)

appWD.ActiveDocument.SaveAs (ThisWorkbook.Path & "\merged" &
Format(Date, "dd-mm-yyyy") & "-week" & _
Worksheets("label order").Range("Q4").Value),
FileFormat:=wdFormatDocument
appWD.ActiveDocument.Close


appWD.Documents("label merge-auto.doc").Close savechanges:=False

appWD.Quit

Set appWD = Nothing

Application.StatusBar = False

End Sub

 

[Anthony]

Solved it, with a bit of a work around.

thisworkbook.savecopyas (file backup)
link backup to mail merge
kill backup

Becasue the caling worbook is never linked it doesn leave anything in memory
when it closes.

Regards

Anthony

The code closes the only file opened, which is the word document. and the
user closes the excel file.

No files are left open, but the excel process still remains active.

The workbook used as the data source is the boo k with the code in it.

Regards

Anthony

You need to make sure that all files are closed other than the one
containing the code. Otherwise VBA will not allow the application to
close. It thinks you are still working.

Hi,

I amtrying to mail merge information from a worksheet into a mail merge
word document. Everything works fine, except it laves a process running
once I quit excel.

The mail merge is called from the same file as the informationto merge
and excel is closed manually by the user.

The code works correctly in 07 (well no process I can see is left), but
it needs to run in excel 2003 and this is where the left over process is
happening.

the code is as follows;

Sub print_Click()

Dim FName As String
Dim appWD As Object ' Word.Application
Dim UserN As String
Dim DesktopPath As String

On Error Resume Next

Application.StatusBar = "Starting mail merge ..."

FName = Dir(ThisWorkbook.Path & "\addresses.xls")

If appWD Is Nothing Then
Set appWD = CreateObject("Word.Application") ' New Word.Application
End If
appWD.Documents.Open Filename:=ThisWorkbook.Path & "\label
merge-auto.doc"

With appWD.ActiveDocument.MailMerge
.OpenDataSource Name:=ThisWorkbook.Path & "\" & FName,
sqlstatement:="SELECT * FROM [print list$]"
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute Pause:=False
End With

Application.StatusBar = "Now creating document for " & Left(FName,
Len(FName) - 4)

appWD.ActiveDocument.SaveAs (ThisWorkbook.Path & "\merged" &
Format(Date, "dd-mm-yyyy") & "-week" & _
Worksheets("label order").Range("Q4").Value),
FileFormat:=wdFormatDocument
appWD.ActiveDocument.Close


appWD.Documents("label merge-auto.doc").Close savechanges:=False

appWD.Quit

Set appWD = Nothing

Application.StatusBar = False

End Sub