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