Use Merge Field in Email "To" field

[smar]

Created a macro for mail merge with the data coming from SQL database. I
would like to use one of the merge fields(toemail) in the mail merge to also
populate the "To" field in the e-Mail bar. Have already created the function
call EMail, but need help with passing the email address data from the merge
field(toemail) in the mail merge. Below is the code from the mail merge and
email. Thanks in advance for your help...

Sub MailMergeELA(filename As String, subject As String, pdno As String,
Subcase As String, item As String)
' Retrieve the selected letter
Application.DisplayAlerts = wdAlertsNone
ChangeFileOpenDirectory "C:\Program Files\Merlin\Letters\"
Documents.Open filename:=filename, ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto

' ELA Letters

' Retrieve data from Merlin database using SQL statement
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:="", ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=False,
AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"DSN=MerlinRPT" _
, SQLStatement:= _
"SELECT vendorName, title1, pdnosub, hpadmin, aoaddr, aph, afax,
aemail, ocrefno " _
, SQLStatement1:=Chr(13) & Chr(10) & _
" FROM merlin_prod.dbo.LtrELA WHERE (pdno= " & pdno & ") AND
(sub_case= " & Subcase & ") AND (item= " & item & ")",
subtype:=wdMergeSubTypeWord2000

' Merge data with the form letter
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
Windows(1).Activate
ChangeFileOpenDirectory "C:\TEMP\"
ActiveDocument.SaveAs filename:="junk.doc",
FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True,
WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False,
SaveAsAOCELetter:= _
False
ActiveDocument.Close

End Sub


Function Email(subject As String, toemail As String, ccemail As String)

Dim env As Office.MsoEnvelope
Set env = ActiveDocument.MailEnvelope
With env
' .Introduction = "My introduction"
.item.subject = subject
.item.to = toemail
.item.cc = ccemail
End With
Set env = Nothing

ActiveWindow.EnvelopeVisible = Not ActiveWindow.EnvelopeVisible
End Function

 

[ryguy7272]

I'm certainly not a Word expert, but the code seems good.
Maybe this resource will help:
http://word.mvps.org/Tutorials/index.htm
Scroll to: Merging External Data

General Link:
http://word.mvps.org/


Regards,
Ryan--

 

[Doug Robbins - Word MVP]

See the second method in the article "How to send an email from Word using
VBA" at:

http://www.word.mvps.org/FAQs/InterDev/SendMail.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP