How to rename an attachment when using sendObject to email

[jasonoski]

I need to rename the emailed object I'm sending so it attaches as the name of a variable I define, "MyTerr" not the name of the object. I am using a MAPI compliant email system (GroupWise) so I don't think I can use any of the snazzy Outlook commands. Here is the simple code I have:

Private Sub emailStuff_Click()
On Error GoTo Err_emailStuff_Click
    Dim MyTerr As String
    Dim MyAddr As String
    Dim stDocName As String
    Dim strSubject As String
    Dim strBody As String
    MyTerr = "1101"
    stDocName = "qry_emailfile"
    MyAddr = "me@anyemail.com"
    strSubject = "Attachment Test"
    strBody = "Look! I can insert a message into the body of an email!"
    DoCmd.SendObject acSendQuery, stDocName, acFormatXLS, strAddress, , , strSubject, strBody, 0
Exit_emailStuff_Click:
    Exit Sub
Err_emailStuff_Click:
    MsgBox Err.Description
    Resume Exit_emailStuff_Click
End Sub

Any assistance is GREATLY appreciated.

 

[toe222]

i use this code that copies it, saves it as variable filename, and the deletes/kills it.

'Defines the recipient of the email
Dim strRECIPIENT As String
strRECIPIENT = Sheet1.cboSALESREP
'Defines the subject of the email
Dim strSUBJECT As String
SubjOfEmail = Sheet1.txtGROUPNAME & " #" & Sheet1.txtGROUPNUMBER
'Defines the body of the email
Dim strBODY As String
BodyOfEmail = "Attached are the incrementals for the above group."
'Defines the copied filename
Dim strSAVE As String
strSAVE = Sheet1.txtGROUPNUMBER
Dim wbPath
wbPath = ActiveWorkbook.Path
Dim wbName
wbName = ActiveWorkbook.Name
'Makes a copy of the Memo
Sheet2.Copy
'Saves a copy of the Memo
ActiveWorkbook.SaveAs Filename:=("c:\" & strSAVE)
'Code to send email using Groupwise
With New GroupwareTypeLibrary.Application
With .Login
With .MailBox.Messages.Add(Class:="GW.MESSAGE.MAIL")
.Subject.PlainText = SubjOfEmail
.BodyText.PlainText = BodyOfEmail
.Attachments.Add ("c:\" & strSAVE & ".xls")
.Recipients.Add strRECIPIENT
'.Recipients.Add "Another Recipient Name "
On Error GoTo Failed:
.Send
End With
End With
.Quit
End With

'Closes the copy of the Memo
ActiveWorkbook.Close
'Deletes copy of the Memo
Kill "c:\" & strSAVE & ".xls"