emailing from a query from a report button

[shakey]

Hi All

I am Stuck!!
I want to email a report using a button on the report to all the email addresses in a names query
The email field is set to hyperlink so that it recognises email addresses but the code stops at the send object command
Could anyone look at my code to see where i am wrong

I got the code from a previous post dated 2008 so wonder if my code is obsolete for access 2016
Regards James

 

[isladogs]

You can't send a report in an email.
See this site for more details on the DoCmd.SendObject code you used http://www.fmsinc.com/microsoftaccess/email/sendobject.html

There are other ways of sending emails from Access though NONE will allow you to send a report
- its simply NOT possible due to formatting issues.
Various companies such as FMS do offer addins (at a price) which use some trickery to achieve some clever things but I doubt its going to be worthwhile for your needs

However, you could do one of the following:
a) specify a format e.g. acFormatTXT but I doubt you will be happy with the results. You will also get various warning messages
b) save the report as a PDF file and send it as an attachment
c) save the report as an image and send it as an attachment
d) create an HTML email, save the report as an image and include in the body of your email

Other points:
1. Reports are not normally intended to be interactive.
Although you can add buttons in report view, these aren't visible in Print Preview
Normally you would run the print command from a button on a form
2. In your code, the email header is something "Invoice #: ".
Where will it get the invoice number from?

 

[shakey]

Hi Colin

Thanks again
The problem isnt that I cant send the report A dialogue box pops up asking me what format i would like the report to go as, my dilemma is the code is not picking up the email addresses from the email field. Am i pointing to the wrong table?
James

 

[arnelgp]

Syntax of SendObject:




DoCmd.SendObject ObjectType, ObjectName, OutputFormat, To, Cc, Bcc,
Subject, MessageText, EditMessage, TemplateFile
ObjectType – acSendTable( to send table), acSendquery(to send
query),acsendreport(to send report),ascendform(to send form),
acSendModule(to send module), acSendNoObject( do not send any object)


ObjectName – Name of table, query, Report etc.
OutputFormat – acFormatHTML ,acFormatRTF ,acFormatSNP,acFormatTXT
,acFormatXLS,acFormatXLSB, acFormatXLSX ,acFormatXPS,acFormatPDF




To – email address to be added in to field of outlook
Cc– email address to be added in cc field of outlook
Bcc– email address to be added in bcc field of outlook
Subject – subject of email
Message text – “content of email”
Edit message – If you want to display or make any changes in email before
sending then set it True, else false
Templatefile – The full name, including the path, of the file to use as a template
for an HTML file


.. i think your what your missing is the output Format.

 

[isladogs]

I've made 2 changes to make your code work:

1. Changed the email field from hyperlink to text (and removed the unwanted hyperlink part)
2. Modified the code in your recordset section to get the email

sToName = !email

3. I also specified the output as PDF as follows:

DoCmd.SendObject acSendReport, "rptData", acReportPDF, _
                    sToName, , , sSubject, sMessageBody, False, False

but it still asks you for the format on sending!

As you can see, it now works ... but will drive you mad with all the various warning messages.
There are better ways of sending email from Access - for example I use CDO to send email without any warnings. Suggest you Google it

Is that 2 pints now?

EDIT: you may be interested in this similar thread where another user wanted to put a PDF in the body of an email.
Also impossible but various workrounds suggested including placing an image in the body of an HTML email
https://www.access-programmers.co.uk/forums/showthread.php?t=293923&highlight=HTML+email

 

[ashleedawg]

Access isn't very co-operative when it comes to producing emails with HTML body, especially with it's lame excuse for support of HTML Templates so I put together my own email-template-interpreter a while back. I've attached an adaptation of it using your data example. (Both files in the zip need to be placed in the same folder.)

Table #1 must contain a field called [/B]of type Hyperlink or String. All other fields can be changed to 'whatever'. To have them appear in the email body, just add the field name to the template surrounded by [square brackets].

Any [field names] from[B] Table #1[/B] found in the "main body" of the template will be replaced with the field's value. (Also [B][CurrentDate][/B] & [B][CurrentTime][/B] will work as you would expect.)

Any fields from [B]Table #2[/B] which you specify with [square brackets] will appear in a [B]formatted HTML Table[/B] where you specify with HTML representing a *single table row* [I]surrounded by special tags[/I].

[U]The example in the picture might make more sense[/U]:
[img]https://image.ibb.co/ep8XH5/Email_HTMLfrom_Table_pic.jpg[/img]

It's not "finished" (no error handling, limited commenting, currently only supports Outlook) but it does the trick, and I'll update this post when I add to it.

 

[shakey]

Cheers Colin
I believe this warrants more than 2
Cheers
Thanks Frank
I await your alternative with anticipation