Sue is right. You may need to re-write your routine to use Outlook to
attach the messages. I use Excel to find the attachment, open Word and
do the mail merge, then tell Outlook to craft a msg and send it out
with the attachment. If you use a unique filename to identify each
recipient, you can tell Outlook to include the appropriate attachment.
Here is a sample of what I use (heavily scrubbed to protect the
innocent).
Dim FName As String, sRecipName As String, X As String
Dim Outlook As Outlook.Application
Dim OutlookMsg As Outlook.MailItem
Dim OutlookRecip As Outlook.Recipient
Dim OutlookAttach As Outlook.Attachment
FName = Dir("C:\Documents And Settings\jsmith\Desktop\*.doc")
On Error Resume Next
Set Outlook = GetObject("outlook.application")
If Err.Number <> 0 Then
Set Outlook = CreateObject("outlook.application")
End If
On Error GoTo 0
Do While Len(FName) > 0
' this uses the filename to identify the recipient
' i.e. if the filename is johnsmith.doc then X would =
"johnsmith"
X = Left(FName, WorksheetFunction.Find(".", X) - 1)
Set OutlookMsg = Outlook.CreateItem(olMailItem)
With OutlookMsg
.SentOnBehalfOfName =
"(e-mail address removed)"
.ReplyRecipients.Add
("(e-mail address removed)")
.Subject = "Please review the attachment, it is customized
for your location."
.Importance = olImportanceHigh
.Sensitivity = olConfidential
.BodyFormat = olFormatHTML
.HTMLBody = "<p>Here's the file you asked for " & _
" to review prior to our meeting.</p><br><p>Please send
me your feedback.</p>"
Select Case X
Case "johnsmith"
sRecipName = "(e-mail address removed)"
Case "maryjones"
sRecipName = "(e-mail address removed)"
Case "robertsmith"
sRecipname = "(e-mail address removed)"
' add additional case statements here as needed to
identify the recipient
Case Else
End Select
Set OutlookRecip = .Recipients.Add(sRecipName)
OutlookRecip.Type = olTo
' this attaches the correct file for the correct recipient
Set OutlookAttach = .Attachments.Add("C:\Documents And
Settings\jsmith\Desktop\" & FName)
.Send
' or .Display
FName = Dir
End With
Loop
Set OutlookRecip = Nothing
Set OutlookAttach = Nothing
Set OutlookMsg = Nothing
Set Outlook = Nothing
You would need to do your mail merge in Excel or Word and have the
program name the files with a unique filename (location code,
recipient name, etc) that you could use to identify the recipient.
Assuming you did your mail merge and placed all the files on the
desktop, the variable FName uses Dir to get the first desktop file
that ends in .doc. Then it takes the filename and uses it to determine
the correct recipient (The "Select Case" statement) and attaches
the .doc to the email.
Beware Outlook security warning!
HTH,
JP
