Forms to email (1 Viewer)

[widemonk]

Hi,
I have a subform in Continuous Records format (records displayed are determined by controls on the parent). I would like to create a button that prepares an email and copies the contents of that subform in to the body of an email message.

The email
I have a method to create a new email, set To, CC, Subject and even Message Text.

strTo = txtName
strCC = txtManager
strSubject = "Something"
strMessage = "Hi, please find below list of details from X Y and Z" & vbnewline & vbnewline

That all works. Thats fine


The Records
I have found ways to have them added as an attachment, either the the SendObject and OutputTo method but I would like the list of records to be converted to a text string so I can include it within my strMessage variable, not include it as an attachment.

strMessage = strMessage & "Subform Records to go here"

Anyone please?

 

[pr2-eugin]

You need to play with recordset object. You need to set the recordset to the Subform's recordsource and then loop through them to get the data and then use them in the string.

 

[AOB]

Another, more complex option, would be to export the data to Excel and then paste the worksheet range as HTML into the body of the e-mail. There's a lot more work involved than a loop iteration but it does allow more enhanced formatting to make it 'prettier'. If you don't mind just getting the raw data, then Paul's option is far easier.

 

[pr2-eugin]

Well you can still make it look pretty using the method I have mentioned. All you have to do is while looping through the recordset just simply add HTML tags, nothing very hard. Simple Table tags would do.

 

[AOB]

That's true alright. I find it easier doing the formatting in Excel and letting Excel write the HTML for me using the .PublishObjects method. But if you're familiar and comfortable with HTML tags then that would be pretty straightforward!

 

[pr2-eugin]

I agree to certain extent, but I find it rather unnecessary to invoke Excel objects when it would be easily available through simple HTML. Nevertheless, many roads lead to Rome.

 

[AOB]

Indeed! I couldn't argue with you there!

I guess another reason I tend to do it this way is that, for most of my reporting requirements, a lot of my end-users like / need to include an attachment of the records in Excel so that the end-recipients (usually management) have something that can be easily sorted and filtered, as well as the main body which really just acts as a summary. Hence, I have to create the workbook anyway.

Management : they love a spreadsheet they do!

 

[widemonk]

Thanks for the input guys - appreciated.

I hadnt considered the export to Excel idea because frankly I have a genuine need to make this absolutely as simplistic as possible, to be used by technophobes to put it politely... click, done!!

Will keep you posted how it goes.