Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Word, PowerPoint, Outlook, and Other Office Products > URGENT! Mail Merge w/VB


Reply
 
Thread Tools Display Modes
  #1  
Old 09-10-2003, 01:40 PM
MDodd73's Avatar
MDodd73 MDodd73 is offline
Junior Contributor
 
Join Date: Mar 2003
Location: PA, USA
Posts: 303
Exclamation URGENT! Mail Merge w/VB


Hey:

When doing a Cover Letter (form letter) mail merge, I wanna save each page as it's own file. Is there a way to Automate this w/VB?

The scenario is:

Each row in Excel fills up 1 letter (some may be 1 p., some 2, depending on the row of Data)

I'm using Excel as my Source...

If anyone has an idea... this is urgent, as I need to start stuffing envelopes tomorrow morning!

I'm well versed in VB for Excel, but I know oh so little about MS Word.

Thanks for you help!

--Dodd
__________________
"Princekimmer Skimple... Primable Skimster... I, I found something!!!!"
--Ralph Wiggum
Reply With Quote
  #2  
Old 09-11-2003, 01:33 AM
Crittar's Avatar
Crittar Crittar is offline
Junior Contributor
 
Join Date: Jul 2003
Location: Somerset, England
Posts: 234
Default

As a suggestion:

Drive it from word, pick up the appropriate excel row and save the word doc, close the doc and start a new one picking up the next excel row.
__________________
Chris Lawery
Reply With Quote
  #3  
Old 09-11-2003, 07:27 AM
MDodd73's Avatar
MDodd73 MDodd73 is offline
Junior Contributor
 
Join Date: Mar 2003
Location: PA, USA
Posts: 303
Question I need more specifics...

Quote:
Originally Posted by Crittar
As a suggestion:

Drive it from word, pick up the appropriate excel row and save the word doc, close the doc and start a new one picking up the next excel row.


Hey: Would you happen to have the code for finding the row in Excel? I know so little of VB for Word... all I've been able to identify the MailMerge Field from Word VB is the Title (.Name) of the Merge Field (The Column Header in Excel), not the actual .Value or .Text of the Cell in Question. (I can't find anything of that sort in Word's Object Browser and I don't know how to call such a thing from Excel's Library in Word VB)

As you can see, I'm quite lost...

--Dodd
__________________
"Princekimmer Skimple... Primable Skimster... I, I found something!!!!"
--Ralph Wiggum
Reply With Quote
  #4  
Old 09-12-2003, 01:37 AM
Crittar's Avatar
Crittar Crittar is offline
Junior Contributor
 
Join Date: Jul 2003
Location: Somerset, England
Posts: 234
Default

Code:
Sub Expenses() ' ' Expenses Macro ' Macro created 14/08/2003 by Chris Lawery ' Dim MyShortMonth, MyShtId, MyYear, MyShortDate ' Define appropriate objects to access the sheet. Dim MyXl As Object, MyBook As Object, MySheet As Object, MyRange As Object Load UserForm2 UserForm2.Show MyShortMonth = Left(MyMonth, 3) MyYear = Year(Now) If MyShortMonth = "Jan" Then MyYear = MyYear - 1 End If MyShtId = MyShortMonth & " " & MyYear ' Open excel, error code is to handle already open or not. On Error Resume Next Set MyXl = GetObject(, "Excel.Application") If Err = 429 Then Err = 0 Set MyXl = CreateObject("Excel.Application") End If ' Open appropriate workbook and sheet Set MyBook = MyXl.Workbooks.Open("c:\data\excel\New Expenses.xls") Set MySheet = MyBook.Sheets(MyShtId) ' Select appropriate area of sheet, move to correct place in word document and copy from sheet to word. Set MyRange = MySheet.Range("C3") With MyRange .Select .Copy End With Selection.MoveDown Unit:=wdLine, Count:=9 Selection.Paste Selection.Font.Size = 12 ' Select appropriate area of sheet, move to appropriate area of document and copy from sheet to word. Set MyRange = MySheet.Range("C4") With MyRange .Select .Copy End With Selection.MoveDown Unit:=wdLine Selection.Paste Selection.Font.Size = 12 ' Repeat the above select / copy as required. ' Finished with spreadsheet, close it. MyBook.Close MyXl.Quit

Hope this helps.
__________________
Chris Lawery
Reply With Quote
  #5  
Old 09-12-2003, 07:56 AM
MDodd73's Avatar
MDodd73 MDodd73 is offline
Junior Contributor
 
Join Date: Mar 2003
Location: PA, USA
Posts: 303
Default

Quote:
Originally Posted by Crittar
Code:
Sub Expenses() ' ' Expenses Macro ' Macro created 14/08/2003 by Chris Lawery ' Dim MyShortMonth, MyShtId, MyYear, MyShortDate ' Define appropriate objects to access the sheet. Dim MyXl As Object, MyBook As Object, MySheet As Object, MyRange As Object Load UserForm2 UserForm2.Show MyShortMonth = Left(MyMonth, 3) MyYear = Year(Now) If MyShortMonth = "Jan" Then MyYear = MyYear - 1 End If MyShtId = MyShortMonth & " " & MyYear ' Open excel, error code is to handle already open or not. On Error Resume Next Set MyXl = GetObject(, "Excel.Application") If Err = 429 Then Err = 0 Set MyXl = CreateObject("Excel.Application") End If ' Open appropriate workbook and sheet Set MyBook = MyXl.Workbooks.Open("c:\data\excel\New Expenses.xls") Set MySheet = MyBook.Sheets(MyShtId) ' Select appropriate area of sheet, move to correct place in word document and copy from sheet to word. Set MyRange = MySheet.Range("C3") With MyRange .Select .Copy End With Selection.MoveDown Unit:=wdLine, Count:=9 Selection.Paste Selection.Font.Size = 12 ' Select appropriate area of sheet, move to appropriate area of document and copy from sheet to word. Set MyRange = MySheet.Range("C4") With MyRange .Select .Copy End With Selection.MoveDown Unit:=wdLine Selection.Paste Selection.Font.Size = 12 ' Repeat the above select / copy as required. ' Finished with spreadsheet, close it. MyBook.Close MyXl.Quit

Hope this helps.



Hey:

Nice, man!

Certainly does help. I'll modify it a bit with a For/Next Loop, since every single Organization Name needs to be used to save single Cover Letter page as it's own file.

Thanks a heap. Now if I can just get it up and running by noon. These needed to go out yesterday...

--Dodd
__________________
"Princekimmer Skimple... Primable Skimster... I, I found something!!!!"
--Ralph Wiggum
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Call a Word Mail Merge from Access? brain Word, PowerPoint, Outlook, and Other Office Products 2 08-19-2003 05:42 AM
Mail merge without automation KowCiller Word, PowerPoint, Outlook, and Other Office Products 3 09-27-2002 03:56 AM
Word Mail Merge Object "execute" method MikeHAdams Word, PowerPoint, Outlook, and Other Office Products 2 04-14-2002 12:11 PM
Automation and Mail Merge tris General 5 09-11-2001 10:08 AM
Mail Merge kiprimshot General 0 10-02-2000 12:41 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->