Use DoCmd.SendObject to Send an Excel .xls with Filename Based on an Order Number

Hi Experts!

I have an Access 2003 Db that uses DoCmd.SendObject to send a vendor an order based on a query called "Qry_Order". This works great, but it would be nice if the filename was unique to each order . As it is now, the attached .xls file is always named after the query so: "Qry_Order.xls".

There is an order number that I would like to pass to create the filename from. It is called "PR_Number" and looks like "PR1058", for example and this would, ideally be passed to create "PR1058.xls"  I have no idea how to do this. I have a combo box I can reference for the PR_Number. Just don't know it it can be done.

Here is the DoCmd statement I have now:
DoCmd.SendObject acSendQuery, "Qry_Order", "Microsoft Excel", _
myVendor, _
, , StrSubject

Thanks,

Kevin
k_smeeAsked:
Who is Participating?
 
Chuck WoodCommented:
You are correct.

You can use the attached code together with the attached class module (change txt to cls and import) to email your spreadsheet.

Public Sub SendEmail()
    Dim cls As New clsSendEmail, blnSent As Boolean
    blnSent = cls.Email("someone@somewhere.com", "Subject", "Body", , "ExcelPathAndName")
    If Not blnSent Then MsgBox "The Email Failed", vbInformation, "Email Failed"
End Sub

Open in new window

clsSendEmail.txt
0
 
Chuck WoodCommented:
Try this:
Use the TransferSpreadsheet method to export the query to a spreadsheet with the name you want to use then use the SendObject method to send the spreadsheet to the vendor.
0
 
k_smeeAuthor Commented:
I got the DoCmd.TransferSpreadsheet to make the file in my C:\Temp folder, but I don't see that the DoCmd.SendObject method has provision to send anything other than an Access object like a query, table, etc...Am I missing something?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
k_smeeAuthor Commented:
Thanks! I'll give it a shot in the morning. The db is at work..
0
 
k_smeeAuthor Commented:
This works great. Is there a way to have it pop up the Outlook mail object to pick recipients or do i need to hard code them? Some of my users like to be able to add other recipients. Thanks! Kevin
0
 
Chuck WoodCommented:
You can use an InputBox or a UserForm to let them add recipients. Opening the mail item is something I haven't done before.
0
 
k_smeeAuthor Commented:
I figured out how to do the display object before sending. I just added:
outMsg.Display True

This is right before the .Send command.
Thanks for all the help !
0
 
k_smeeAuthor Commented:
Excellent help on this one, Thanks!
0
 
Chuck WoodCommented:
I am very glad you found the answer. And thank you for sharing it with the rest of us. Thank for the kind words and the high grade. Good luck on your project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.