Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / May 2004

Tip: Looking for answers? Try searching our database.

Help with Postal Code VBA macro (Peter Jamieson)

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bob S - 08 May 2004 19:22 GMT
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?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=5ea
579dbc0a20510&seekm=%23PWmwws%23DHA.2412%40TK2MSFTNGP12.phx.gbl#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 - 09 May 2004 11:25 GMT
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

Signature

Peter Jamieson

> 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?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=5ea
579dbc0a20510&seekm=%23PWmwws%23DHA.2412%40TK2MSFTNGP12.phx.gbl#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
[quoted text clipped - 6 lines]
> Thank you for your help.
> Bob
Bob S - 09 May 2004 23:09 GMT
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.

> Instead of
>
[quoted text clipped - 36 lines]
> > 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?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=5ea
579dbc0a20510&seekm=%23PWmwws%23DHA.2412%40TK2MSFTNGP12.phx.gbl#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
[quoted text clipped - 8 lines]
> > Thank you for your help.
> > Bob
Peter Jamieson - 10 May 2004 00:12 GMT
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.

Signature

Peter Jamieson

> Thanks Peter.  I think from the duplicate code you supplied in the section
> of code starting with ".OpenDataSource" and with the duplication of code
[quoted text clipped - 68 lines]
> a
> > > computer crash in the mean time.  You can find the thread at:

http://groups.google.co.uk/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=5ea
579dbc0a20510&seekm=%23PWmwws%23DHA.2412%40TK2MSFTNGP12.phx.gbl#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
[quoted text clipped - 9 lines]
> > > Thank you for your help.
> > > Bob
Bob S - 10 May 2004 05:24 GMT
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.

> 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
[quoted text clipped - 113 lines]
> > a
> > > > computer crash in the mean time.  You can find the thread at:

http://groups.google.co.uk/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=5ea
579dbc0a20510&seekm=%23PWmwws%23DHA.2412%40TK2MSFTNGP12.phx.gbl#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
[quoted text clipped - 14 lines]
> > > > Thank you for your help.
> > > > Bob
Peter Jamieson - 10 May 2004 09:29 GMT
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

Signature

Peter Jamieson

> Thanks Peter.  That is much clearer.  I have made the changes as you
> described them.  I just have one question.
[quoted text clipped - 28 lines]
>
> Thank you Peter.

<snip
Bob S - 10 May 2004 21:24 GMT
Thanks Peter for postnng the entire macro.  It worked fine.  Thanks again!
Bob

> 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
[quoted text clipped - 168 lines]
> >
> <snip
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.