email merge code issues

[Michelle Hanan]

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.

 

[Peter Jamieson]

Pleas post the code here.

Peter Jamieson

 

[Michelle Hanan]

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 OLEDBatabase 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?

 

[Peter Jamieson]

Sorry to take so long on this one.

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?

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