SendObject and Filename Change

[D.Tulenson]

The following module, which works just fine, is designed to mass
e-mail info to individual projects but the attachment is always
"rptexpendituredetail.xls".

Is there any way to change the attachment's filename each during each
loop so each project can receive a uniquely named file??

FYI, rptexpendituredetail is the name of the Access report that
filters the data for each project based on a query.
===============================================

Private Sub cmdMassEmail_Click()
Dim stDocName As String
Dim ProjManager As String
Dim ProjName As String
Dim rst As Recordset
stDocName = "rptexpendituredetail"

For i = 0 To ProjNumber.ListCount - 1

ProjectNumber = ProjNumber.ItemData(i)

Set rst = New Recordset
rst.Open "select projectmanager, projectname from [1641
Projects] where ProjectNumber = " & ProjectNumber,
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not IsNull(rst("ProjectManager")) Then
ProjManager = rst("ProjectManager")
ProjName = rst("ProjectName")
DoCmd.SendObject acReport, stDocName, acFormatXLS,
ProjManager, , , SubjectLine & " - " & ProjName & " (" & ProjectNumber
& ")", "Attached is your EDR for project number " & ProjectNumber,
False
End If
rst.Close
Next i
End Sub
========================

[James Fortune]

dt*******@comcast.net (D.Tulenson) wrote in message news:<e6**************************@posting.google. com>...

The following module, which works just fine, is designed to mass
e-mail info to individual projects but the attachment is always
"rptexpendituredetail.xls".

Is there any way to change the attachment's filename each during each
loop so each project can receive a uniquely named file??

I got the following code sample from OutlookCode.com:

Sub NewMailMessage()
Dim ol As New Outlook.Application
Dim ns As Outlook.NameSpace
Dim newMail As Outlook.MailItem

'Return a reference to the MAPI layer.
Set ns = ol.GetNamespace("MAPI")

'Create a new mail message item.
Set newMail = ol.CreateItem(olMailItem)
With newMail
'Add the subject of the mail message.
.Subject = "Training Information for October 1997"
'Create some body text.
.Body = "Here is the training information you requested:" &
vbCrLf

'Add a recipient and test to make sure that the
'address is valid using the Resolve method.
With .Recipients.Add("mi****@imginc.com")
.Type = olTo
If Not .Resolve Then
MsgBox "Unable to resolve address.", vbInformation
Exit Sub
End If
End With

'Attach a file as a link with an icon.
With .Attachments.Add _
("\\Training\training.xls", olByReference)
.DisplayName = "Training info"
End With

'Send the mail message.
.Send
End With

'Release memory.
Set ol = Nothing
Set ns = Nothing
Set newMail = Nothing
End Sub

The corresponding code for Access is almost identical. Besides
allowing for different attachment names on each loop, this technique
looks like it is more robust than using SendObject in general. Also,
get to know the difference between early and late binding for
Automation Objects. Make sure your References include the Outlook
Object Model.

James A. Fortune

MIME is a series of specifications that describe how to represent
binary data as text so that they may be sent via text-based electronic
mail. --- David Wood, Programming Internet Email, O'Reilly 1999