EMail Merge Macro from Word 2003 problem

[colinmitton]

I have a macro that runs from a word (2003) template. This macro runs a e-mail merge from an excel 2003 spreadsheet out via outlook 2003. Everything runs fine but no matter how many records it needs to merge (usually between 1 and 15) it always merges 46?

So for instance if I have 6 records to merge to email, it will merge and send out the 6 relevent emails then continue on until the 46th record, merging blank emails. They do not send out via outlook but it does take a while for this to happen so you sit their waiting a minute or two for no reason?

The Excel spreadsheet (source for the merge) is taken from a list thats been filtered to leave just the relevent records. These records have been copied and pasted (values only) to a fresh sheets which the records merge from. I have also checked the sheet to ensure there is nothing on the 46th / 47th row.

Any thoughts would be welcomed!

My code in the word template :

ActiveDocument.MailMerge.MainDocumentType = wdEMail
ActiveDocument.MailMerge.OpenDataSource Name:= _
"c:\temp\Unpaidbills.xls", ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=R:\Credit Control\Unpaid bills 14 days old to HOD.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Eng" _
, SQLStatement:="SELECT * FROM `Report$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.MailAddressFieldName = "HOD"
.MailSubject = "Unpaid Bills"
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Application.Quit savechanges:=wdDoNotSaveChanges

Thanks folks....

 

[SkipVought]

Hi,

Does your Excel source data sheet have FORMULAS in 46 rows, some just returning [BLANKS]?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[colinmitton]

Hi Skip,

The source data is downloaded from a DB, there are some formula's which are 'filled down' on import but the original data returns at least 200 records, they are then filtered / copied to a new location values only.

The filtered / copied (with only values) data is then whats 'merged' from. I have checked both the sheets (orginal and filtered) for any formula's or entries in the rows to and beyond 46.

Thanks

Colin

 

[macropod]

Hi Colin,

When you open your filtered data sheet and press Ctrl-End,
does that take you to only the last data cell, or all the way down to the 47th row? If it's the latter, Excel is telling the mailmerge process there are excess records and you need to delete the empty rows. To do that, open the Excel worksheet independently of the mailmerge, delete all unused rows. Save, close then start the mailmerge.


Cheers
[MS MVP - Word]

 

[colinmitton]

Thanks, There is something in the 47th Row. I have tried to delete the rows, in fact I've tried to delete the remainding rows and still it thinks something is in row 47!

I shall delete the entire sheet and start again. At least I know its the spreadsheet and not the macro!

I dont suppose anyone knows the 'field' (not sure of the technical term) for CC (when To is .MailAddressFieldName)?

Just had someone ask if the could be CC'd in on the emails!

Thanks once more!

 

[SkipVought]

Hitting the DELETE key will NOT work.

Select the rows to delete.

Right-click and select DELETE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RonRepp]

Maybe I'm missing something here, but WHY are you using both Excel and Access for a simple mail merge?



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[SkipVought]

Ron,

Where do you see MS Access in this thread?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[colinmitton]

Skip,

thanks for the reply, i did try that way but no luck so deleted and recreated the sheet. everthing then worked fine.

Ron,
I'm pulling the data from an old informix db, the odbc driver i can use is quite limited so i have to do the filters in excel to create the merge data.

thanks folks.