Sending emails from access (1 Viewer)

[bob fitz]

Hi guys,

I made a db for a client some years ago in A2000 who wants me to add some new features.
In the old db I had a report that created statements of account for each of his customers which he printed out and sent by post. He would now like to send these by email.
Can anyone give some advice on how to go about this please.

 

[burrina]

Bob, are you talking about sending out multiple statements at a time? If so, I would suggest saving them to a folder and then attaching them to a email.

 

[bob fitz]

Hi Burrina.
Thanks for your interest.

I haven't had time yet to delve into the db that I did for the client but as I recall the db gave the user the facility to choose which customers to print a statement for and for which week ending date. Obviously, the detail of each printed statement is different for each customer depending on the transactions that happens in that week.
The user would then have a statement for each of his customers which would need to be placed in an envelope, addressed, stamped and posted.
The requirement now is to still choose which customers and for which week ending date but instead of printing the report, to create an email to each customer and attach the appropriate report, perhaps as a PDF file, to the appropriate email and then send the emails.
I wouldn't expect there to be more than 50 or 60 of these at any one time. Indeed there may only be half that number.

 

[bob fitz]

BTW

I should have stated that although the original db was done using A2000 I expect to be using A2013 for this now.

 

[Ranman256]

I have a form. In it is a list box of people to email. ClientName, EmailAddr.
Also is a combo box of reports, cboRpt.
and a Send button.

The report uses a query, this query looks at the list box as the Client in the criteria.
select * from table where [Client] = forms!frmRpts!lstClients

So when the client is selected, the report pulls ONLY that managers data.
then sends it via
DoCmd.SendObject acSendReport, cboRpt, acFormatPDF, lstClients, , , cboRpt.Column(1), cboRpt.Column(2), False

The btnSend_click will then scan the list of people and send them the report.

  'scan items in listbox
For i = 0 To lstClients.ListCount - 1
   vTo = lstClients.ItemData(i)
   lstClients= vTo
        
   DoCmd.SendObject acSendReport, cboRpt, acFormatPDF, lstClients, , , cboRpt.Column(1), cboRpt.Column(2), False     
next

 

[burrina]

Bob, I have no experience with 2013 but just as an added suggestion, you might look at a db I wrote which is in the sample section named Group Emailer which you may be able to adapt to your needs. Other than that, it's above my pay grade.

 

[Minty]

We send invoices out automatically from Access, which I think is probably slightly more complicated than a statement.

As Burrina has stated the easy route would be to create the statements as PDF's per account possibly stored in an account folder with a account number/date related file name.

You can then quite easily using the same criteria, create a list of those accounts that have a statement and loop round them creating an email to each one with the PDF attached. If you have outlook installed on the client machine it's pretty straight forward.