Include date in file name problem

[KCK]

Hello,

I have searched and followed many of the suggestions in the forum and have gotten to the point where I can send a query via email. The only thing I need to complete this project is the abilty to add a specific date to the end of the file name being sent.

Specifically, I have a query that is run using a specific date which the user enters into the field CTSI_Processingdate when the form opens. I need to have the date that was entered into this field concatenated to the query name so the resulting XLS file appears like something like "CTSI Weekly spreadsheet for APAC (5005_SGD & 5006_USD) W_E 10_23_09".
I keep getting an error of "could not find object CTSI Weekly spreadsheet for APAC (5005_SGD & 5006_USD) W_E 10/23/09.

Private Sub Ctl_Email2_Click()

Dim strMsg As String
Dim SL As String, DL As String
Dim emailsubject As String
Dim emailtext As String
Dim stfilname As String
Dim stprocessdate As Date

SL = vbNewLine
DL = SL & SL
stdocname = "CTSI Weekly spreadsheet for APAC (5005_SGD & 5006_USD)"
stprocessdate = Me.CTSI_PROCESSINGDATE
stfilename = stdocname & " W_E " & stprocessdate & ".XLS"

emailsubject = "SGX Frieght Invoice Audit File for process week"
emailtext = "The attached file contains all invoice records for Singapore." & DL & _
"regards," & DL & _
"KK" & SL _

DoCmd.SendObject acSendQuery, stdocname & " w_e " & stprocessdate, acFormatXLS, _
"name@company.com", , , emailsubject, emailtext, True


End Sub

Any help would be very much appreciated.

 

[dcb]

Use the date part function and extract the day month year seperately then create your string from that

 

[boblarson]

How about changing this:
stprocessdate = Me.CTSI_PROCESSINGDATE

to this:

stprocessdate = Format(Me.CTSI_PROCESSINGDATE,"mm_dd_yy")

 

[KCK]

Thanks for the input guys. I have tried both suggestions and the process advised by Bob worked like a charm to format the date correctly. I must be doing something wrong using the DatePart function as VBA would not allow it to accept that format. However, I continue to get the same error message so it appears the SendObject statement wants to read the file name as the full name I want it to appear rather than the original query name that it needs to run to create the attachment.

Here is the code:
Private Sub Ctl_Email2_Click()

Dim strMsg As String
Dim SL As String, DL As String
Dim emailsubject As String
Dim emailtext As String
Dim stfilname As String
Dim processdate As String
Dim datemonth As String
Dim dateday As String
Dim dateyear As String
Dim weekend As String

SL = vbNewLine
DL = SL & SL
weekend = " w_e "
stdocname = "CTSI Weekly spreadsheet for APAC (5005_SGD & 5006_USD)"
processdate = Forms.sgx_weekly_audit_file.CTSI_PROCESSINGDATE
'dateyear = DatePart('yy',CTSI_PROCESSINGDATE)
'datemonth = DatePart('M',CTSI_PROCESSINGDATE)
'dateday = DatePart('d',CTSI_PROCESSINGDATE)
'processdate = datemonth & "_" & dateday & " _" & dateyear
stprocessdate = Format(Me.CTSI_PROCESSINGDATE, "mm_dd_yy")

emailsubject = "SGX Frieght Invoice Audit File for process week"
emailtext = "The attached file contains all invoice records for Singapore." & DL & _
"regards," & DL & _
"KK" & SL _

DoCmd.SendObject acSendQuery, stdocname & weekend & stprocessdate & ".xls", acFormatXLS, _
"name@company.com", , , emailsubject, emailtext, True


End Sub

Any other thoughts on how to get this accomplished?

 

[Atomic Shrimp]

SendObject sends an object from within your database (a named table or query) - you're trying to get it to send a named file - it can't do that.

If you want to export the file with a specific name, you could use DoCmd.OutputTo to create it somewhere in the filesystem, then attach it to an email and send it using Outlook automation as described here: http://support.microsoft.com/kb/209948

 

[KCK]

Thanks Atomic Shrimp. I'll give it a try!

I was also thinking of trying to have the macro copy the query to the desired name, run the query and email the results, then delete the new query.

Any thoughts on this approach?

 

[DCrake]

When including the date/time element into a file name it is always best to reverse the format for sorting purposes. Also, and more importantly remove any seperators between yy mm dd. Windows naming conventions state that you cannot use "\" or "/" as part of a file name. Therefore use Format([AnyDate],"YYMMDD") as the stringed element of the date which is being parsed into the file name.

David

 

[KCK]

Thanks David, that is a great advice! Will do.

I am still unable to get around the fact that DoCmd.SendObject acSendQuery will only recognize original query name and will not use the new file name. Any ideas on how to get around this issue?

 

[DCrake]

DoCmd.SendObject acSendQuery, stdocname & weekend & stprocessdate & ".xls", acFormatXLS, _
"name@company.com", , , emailsubject, emailtext, True

The reason why it is not working is that you do not have a query called

stdocname & weekend & stprocessdate & ".xls"


You have a query called stdocname

Access says...

expression.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

I think what you are trying to do is to tell it to send a query but as you send it change it to a workbook and give it this name. Which in all fairness is not possible. (The name changing element part of it).

David

 

[KCK]

Yes, that makes sense. I will try Atomic Shrimps idea using automation and also try using CopyObject to copy the query to the desired name and then send the new query.

Thanks for the feedback.

 

[Atomic Shrimp]

Copying the query object to create a new query named the way you want it sounds like it should work, but you run the risk of filling up your database with multiple copies of the query.

Your code can take this into account, deleting the copied object after it has been used, but if something happens to interrupt that, you could still end up with an accumulation of garbage.

If you do choose to do it by copying objects, I suggest prefixing all of their names with some common string, so that they appear in a block for easy deletion (assuming that you intend to use the same approach for a number of differently-named reports).

 

[KCK]

Yes, I plan to delete the new file immediately after sending to avoid the garbage. They will all have the same unique name with the exception of the date so spotting any non-deleted ones will be an easy task.

Thanks for all the feedback!