email merge code issues

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michelle Hanan - 08 Mar 2007 19:01 GMT

I am merging a document to outlook 07 email. The document has different
mergefields from an excel sheet, which also holds the email address to be
merged to. I'm having problems with the code for some reason. The macro is
in the excel worksheet if that makes a difference.

Reply to this Message

Peter Jamieson - 14 Mar 2007 20:26 GMT

Pleas post the code here.

Peter Jamieson

>I am merging a document to outlook 07 email. The document has different
>mergefields from an excel sheet, which also holds the email address to be
>merged to. I'm having problems with the code for some reason. The macro is
>in the excel worksheet if that makes a difference.

Reply to this Message

Michelle Hanan - 19 Mar 2007 22:23 GMT

Sub Assessor()
'This macro merges data from worksheet one of Referals.xls into the document
stated below.
Dim myDoc As Word.Document
Set myDoc =
Word.Documents.Open("\\powervault2\home_pl\common\Referrals\Referal Agency -
Assessor.doc")
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\powervault2\home_pl\common\Referrals\Referals.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=\\powervault2\home_pl\common\Referrals\Referals.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password=" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
myDoc.Close wdDoNotSaveChanges
End Sub

This is the macro that I am currently using. The only change that I want to
make is to send it to email instead of the printer. I keep getting error
codes when I change the wdSendToPrinter to wdSendToEmail. Any suggestions?

> Pleas post the code here.
>
[quoted text clipped - 4 lines]
>>merged to. I'm having problems with the code for some reason. The macro is
>>in the excel worksheet if that makes a difference.

Reply to this Message

Peter Jamieson - 26 Mar 2007 09:17 GMT

Sorry to take so long on this one.

>>The document has different
>>>mergefields from an excel sheet,

What do you mean by this? Are you saying that the merge fields in the Mail
Merge Main Document have different names from the columns in the sheet, or
what?

> I keep getting error codes when I change the wdSendToPrinter to
> wdSendToEmail.

What errors are you getting? Does the process definitely work if you do it
manually, e.g. the merge to email button isn't greyed out?

>>which also holds the email address to be
>>>merged to.

For starters, I would try:

Sub Assessor()
'This macro merges data from worksheet one of Referals.xls into the document
stated below.
Dim myDoc As Word.Document
Set myDoc =
Word.Documents.Open("\\powervault2\home_pl\common\Referrals\Referal
Agency -
Assessor.doc")
' have the object so use it
' change to wdEMail probably not significant
myDoc.MailMerge.MainDocumentType = wdEMail

' You can probably cut a lot of this stuff
' (but leave it as was if there are further problems)
' reconstitute lines as necessary
myDoc.MailMerge.OpenDataSource _
Name:="\\powervault2\home_pl\common\Referrals\Referals.xls", _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\\powervault2\home_pl\common\Referrals\Referals.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";", _
SQLStatement:="SELECT * FROM `Sheet1$`"
With MyDoc.MailMerge
.Destination = wdSendToEmail
' Don't forget to set up this stuff and if necessary
' set up the email format etc.
.MailAddressFieldName = "youremailaddressfield"
.MailSubject = "your subject"
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
myDoc.Close wdDoNotSaveChanges
End Sub

My guess is that if anything the change from ActiveDocument to myDoc will do
it.

Peter Jamieson

> Sub Assessor()
> 'This macro merges data from worksheet one of Referals.xls into the
[quoted text clipped - 46 lines]
>>>merged to. I'm having problems with the code for some reason. The macro
>>>is in the excel worksheet if that makes a difference.

Reply to this Message