Link to home
Start Free TrialLog in
Avatar of k_smee
k_smee

asked on

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
Avatar of Chuck Wood
Chuck Wood
Flag of United States of America image

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.
Avatar of k_smee
k_smee

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Chuck Wood
Chuck Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of k_smee

ASKER

Thanks! I'll give it a shot in the morning. The db is at work..
Avatar of k_smee

ASKER

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
You can use an InputBox or a UserForm to let them add recipients. Opening the mail item is something I haven't done before.
Avatar of k_smee

ASKER

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 !
Avatar of k_smee

ASKER

Excellent help on this one, Thanks!
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.