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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! I'll give it a shot in the morning. The db is at work..
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.
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 !
outMsg.Display True
This is right before the .Send command.
Thanks for all the help !
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.
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.