Help with Postal Code VBA macro (Peter Jamieson)

[Bob S]

Hi Peter, on March 4 you posted in this ng a message with a VBA macro to
match the Postal Code/Zip Code in Word with that originally set in Outlook.
Unfortunately, that message has rotated off of this ng now. Sorry for the
delay in responding. I have taken a long business trip to Asia and had a
computer crash in the mean time. You can find the thread at:

http://groups.google.co.uk/groups?h...=#PWmwws#[email protected]#link19

I wanted to report that the macro works beautifully! Thank you much.
Through a link you provided in the post I also learned how to put a new
button on a toolbar and attach the macro to it. I know this sounds very
basic. But I was wondering if you could tell me the code (or whatever) to
add to the macro to have it add (at the end) the function of the "Merge to
new document" button (on the mail merge toolbar) and to select "all records"
(and OK) so that I do not have to press either of those buttons either.

Thank you for your help.
Bob

 

[Peter Jamieson]

Instead of

.OpenDataSource Name:=sMergeDataSourceName
.MainDocumentType = vMergeType
.Destination = vMergeDestination

you need something like:

.OpenDataSource Name:=sMergeDataSourceName
.MainDocumentType = vMergeType
' don't restore the original destination, just make it a new document
.Destination = wdSendToNewDocument
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False

After the merge, the ActiveDocument will be the result document. So if you
want to close the merge main document automatically as well, it is better to
set an object at the beginning of the macro:

Dim vMergeDestination As WdMailMergeDestination
Dim oMMMD As Word.Document
On Error GoTo finish

Set oMMMD = ActiveDocument
With oMMMD.MailMerge

..
..
End With
oMMMD.Close SaveChanges:=False

 

[Bob S]

Thanks Peter. I think from the duplicate code you supplied in the section
of code starting with ".OpenDataSource" and with the duplication of code
found in the section of code starting with "Dim vMergeDestination" I believe
I know where to put those two segments of code.

However, I am not sure where to put the section

End With
oMMMD.Close SaveChanges:=False

I tried putting it just before the following batch of code and also between
this code and the file line of the subroutine "End Sub".

Exit Sub
finish:
Close #1
MsgBox "Error " & Err.Number & _
" when trying to modify the ZIP field name: " & _
vbCrLf & Err.Description
Err.Clear
On Error Resume Next
Set oSourceStream = Nothing
Set oDestStream = Nothing
Set oFileSystemObject = Nothing


Peter, in both cases I got the error message "Compile Error, Expected End
With."
What did I do wrong? Thanks again.

 

[Peter Jamieson]

If you use the oMMMD stuff, the End With should replace the existing End
With that would match the With oMMMD.MailMerge, i.e. instead of

End With

Exit Sub
finish:

use

End With
oMMMD.Close SaveChanges:=False

Exit Sub
finish:


but in fact it should be

End With
oMMMD.Close SaveChanges:=False
Set oMMMD = Nothing
Exit Sub
finish:
Close #1
MsgBox "Error " & Err.Number & _
" when trying to modify the ZIP field name: " & _
vbCrLf & Err.Description
Err.Clear
On Error Resume Next
Set oSourceStream = Nothing
Set oDestStream = Nothing
Set oFileSystemObject = Nothing
Set oMMMD = Nothing

End Sub

However, if that is what you already did I'll have to look again as I can't
see what the problem is right now.

 

[Bob S]

Thanks Peter. That is much clearer. I have made the changes as you
described them. I just have one question.
Below you seem to suggest putting

oMMMD.Close SaveChanges:=False
Set oMMMD = Nothing

between
End With
anf
Exit Sub
finish:
Close #1

Ok, but in your initial code you had a line between there already. It was
.Execute Pause:=False

such that it looked like the following.


End With
.Execute Pause:=False

Exit Sub
finish:
Close #1


Do I need that line? If so, where in relation to this statement of
"Execute: Pause=False" do I put these two lines?
oMMMD.Close SaveChanges:=False
Set oMMMD = Nothing


Thank you Peter.

 

[Peter Jamieson]

I suppose I should have just posted the entire thing :But there were one
or two options. Here'sthe code with the oMMMD stuff inserted. I hope it's
correct:


Sub ModifyZipFieldName()

' Macro by PJ Jamieson, March 2004
' v1.1 Modified May 2004 to do a merge to a document
' This macro looks for a field name called "ZIP/Postal Code"
' in the header record of a mail merge data source and modifies
' the name so that Word automatically maps its ZIP/Postal code
' to the field.
' Written for use with Word 2003/Outlook 2003, but it may work
' in earlier versions
' Requires the "Microsoft Scripting Runtime" object library:
' You will need to use the VBA Editor menu command
' Tools|References to add a reference to this library

' This macro is intended to be used when a merge is initiated
' from Outlook. In that case, I believe the following assumptions
' can be made:
' The merge data file is a Unicode format text file
' (notice that by default it has a .doc extension)
' Outlook reconnects to the data source and removes any
' sort/filter options you may have defined

' The macro also assumes that it is OK to create/overwrite a
' file with the same name as the data source + ".tmp"

Dim oFileSystemObject As Scripting.FileSystemObject
Dim oSourceStream As Scripting.TextStream
Dim oDestStream As Scripting.TextStream
Dim sHeaderRecord As String
Dim sMergeDataSourceName As String
Dim vMergeType As WdMailMergeMainDocType
' v1.1 don't need this line any more
' Dim vMergeDestination As WdMailMergeDestination
Dim oMMMD As Word.Document
On Error GoTo finish

Set oMMMD = ActiveDocument
With oMMMD.MailMerge
If .MainDocumentType = wdNotAMergeDocument Then
MsgBox "This document is not a mail merge main document"
Else
' Save merge type
vMergeType = .MainDocumentType
' v1.1 don't need this line any more
' vMergeDestination = .Destination
sMergeDataSourceName = .DataSource.Name
.MainDocumentType = wdNotAMergeDocument

Set oFileSystemObject = CreateObject("Scripting.FileSystemObject")
With oFileSystemObject
' This assumes the file is Unicode format (I think)
Set oSourceStream = .OpenTextFile( _
FileName:=sMergeDataSourceName, _
IOMode:=ForReading, _
Create:=False, _
Format:=TristateTrue)

' Verify that the required string exists before going any further
sHeaderRecord = oSourceStream.ReadLine
If InStr(1, sHeaderRecord, "ZIP/Postal Code") = 0 Then
MsgBox "Could not find the field name 'ZIP/Postal Code'" & _
"in the merge data source." & _
vbCrLf & "You will need to match fields manually"
oSourceStream.Close
Set oSourceStream = Nothing
Else
' create the output file
Set oDestStream = .CreateTextFile( _
FileName:=sMergeDataSourceName + ".tmp", _
overwrite:=True, _
unicode:=True)

' Make the substitution. The field names "ZIP" and "Postcode"
' seem to be recognised automatically by Word

oDestStream.WriteLine _
Text:=Replace(sHeaderRecord, "ZIP/Postal Code", "ZIP")

' copy the rest of the file
Do Until oSourceStream.AtEndOfStream
oDestStream.WriteLine Text:=oSourceStream.ReadLine
Loop

' Close everything and replace the old file by the new one
oDestStream.Close
Set oDestStream = Nothing
oSourceStream.Close
Set oSourceStream = Nothing
oFileSystemObject.DeleteFile _
filespec:=sMergeDataSourceName, _
force:=True
oFileSystemObject.MoveFile _
Source:=sMergeDataSourceName + ".tmp", _
Destination:=sMergeDataSourceName
End If
End With
Set oFileSystemObject = Nothing
End If

' Set up the mail merge data source etc. again
' You may find that you need to save and restore
' other settings
.OpenDataSource Name:=sMergeDataSourceName
.MainDocumentType = vMergeType

' v1.1 don't restore the original destination,
' just make it a newdocument
' .Destination = vMergeDestination
.Destination = wdSendToNewDocument
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False

End With
oMMMD.Close SaveChanges:=False
Set oMMMD = Nothing
Exit Sub

Exit Sub
finish:
Close #1
MsgBox "Error " & Err.Number & _
" when trying to modify the ZIP field name: " & _
vbCrLf & Err.Description
Err.Clear
On Error Resume Next
Set oSourceStream = Nothing
Set oDestStream = Nothing
Set oFileSystemObject = Nothing
Set oMMMD = Nothing
End Sub

--
Peter Jamieson

Thanks Peter. That is much clearer. I have made the changes as you
described them. I just have one question.
Below you seem to suggest putting

oMMMD.Close SaveChanges:=False
Set oMMMD = Nothing

between
End With
anf
Exit Sub
finish:
Close #1

Ok, but in your initial code you had a line between there already. It was
.Execute Pause:=False

such that it looked like the following.


End With
.Execute Pause:=False

Exit Sub
finish:
Close #1


Do I need that line? If so, where in relation to this statement of
"Execute: Pause=False" do I put these two lines?
oMMMD.Close SaveChanges:=False
Set oMMMD = Nothing


Thank you Peter.

<snip>

 

[Bob S]

Thanks Peter for postnng the entire macro. It worked fine. Thanks again!
Bob