vba emailing report & assigning file name

[holden_1]

Hi, is it possible to assign a file name from fields in a form to a report you are emailing? i have the code below, but not sure on how to change the filename...

DoCmd.SendObject acReport, stdocname, "Rich Text Format (*.rtf)", to_list, cc_list, , subject, msgText

any help appriciated. Rob

 

[maxmangion]

the filename is stdocname so you can use the following

stdocname = "Me.Fieldname" where fieldname is the name of your field.

 

[holden_1]

so stdocname isn't the name of the report? Then problem solved!

 

[maxmangion]

stdocname is a variable name. Therefore you can either insert the filepath directly instead of the stdocname or else use the method explained in my previous post.

Good luck

 

[holden_1]

cool, thanks for your help, that's filled a gap in my knowledge!

Rob

 

[maxmangion]

you're welcome

 

[holden_1]

ah, i think i've not explained myself very well...

I wanted to change the filename of the report depending on who it's going to... i.e. i want to rename the report when it is sent without modifying the name of the report stored in the database.

 

[holden_1]

it appears caption property controls the filename.. but i can't put any code in there...

 

[stopher]

Yes, caption would be the obvious place to set the name. However, you can't easily edit this in code without first opening the report (messy).

However, if you leave the caption blank and then copy your report to a new report name in code, the new file created from this report will pick up the new filename.

DoCmd.CopyObject , "Jims Report", acReport, "MasterReport"

Obviously then you need to email "Jims Report".

Also, you need to delete afterwards

DoCmd.DeleteObject acReport, "Jims Report"

There may be a better way but this is the only easy way I can think of.
hth
Chris

 

[holden_1]

I see, thats the sort of route i was going to ask about as my next line of thought! It may sound a little long winded, but i think it's the only meathod to customise the report file name... I'll give this a go and let you know the outcome

 

[holden_1]

ok, slight problem...

DoCmd.CopyObject , stdocname, acReport, origdocname
DoCmd.SendObject acReport, stdocname, "Rich Text Format (*.rtf)", to_list, cc_list, , subject, msgText
DoCmd.DeleteObject acReport, stdocname

this copies the report, changes report name and sends to outlook. However, the file name for the output to outlook is "caption".rft and the report isn't deleted afterwards. bummer.
any ideas? i don't suppose i can code to set the caption... this seems either a silly limitation of access or i'm missing the point someplace! Have searched for this several times, i can't beleive i'm the only person who wants to set the filename of the output report!
Rob

 

[stopher]

Very strange. It works fine for me. I get an email with the new report name (using Access2003 SP2). Then the new report gets deleted as expected.

Are you sure there is nothing is the caption field for your original report? Or is there any code that may be setting this attribute. If the caption field is blank then the file name should used should default to the report name.

Can you post your d/b?
Chris

 

[holden_1]

Oh, how daft! Had something input to caption. rename now works a treat, only problem left is if the sendobject is cancelled the report remains... but i think i know how to tackle that!

I'd post the project, but it has content of a confidential nature and i'm not sure i'd be allowed by company policy.

Thanks for your help tho! I've always been impressed by the helpfulness of people like youorself on this forum, it's very much appriciated from someone like myself who has some knowledge gaps from being more or less self taught!

 

[holden_1]

...or not...

 

[holden_1]

oh wait, we're good!

 

[holden_1]

just a quick one, what code would i use to check if that report exists?

thanks