Email Reports

  • Thread starter Thread starter Derek Brown
  • Start date Start date
D

Derek Brown

Hi all

I would like to email letters to clients using information from access
tables with names address and of course email details. In a similar way that
you would produce a mail merge. Is it possible and can anyone point me to
information on how to do it. I have used usual recourses but find al I get
is how to send an email snapshot, in fact I need a way of automating the
process from within Access.
 
Derek:

The practical steps to do this are a bit involved but here you go as to the
steps.

1.) Create a mail merge template in Word that includes your basic
information. Save this as a .doc template delinking it from the underlying
source query.

2.) Use automation to open the Word document and set the recordsource to
your db and merge in one record at a time, saving the resulting document to
a distinct Word file (which you can delete later,) or printing it to a PDF
document to attach to a mail item. I've added some example code below that
would use ODBC to connect to your db and merge and save the document. This
code doesn't have all the objects declared (its a quick cut and paste job,)
but it should get you started. This code assumes you are looping through
an Access recordset and feeding the target recipient ids to the code to
merge a specific letter.

3.) Once you have either a merged Word document or a distinct PDF file, you
can then use automation to send an e-mail through Outlook, attaching the PDF
file to that mail item (or the naked Word document).

4.) If you want to save time automating the creation of the PDF files from a
Word document (from within Access) and to mail the items, you might look at
our PDF and Mail Library for Access, (PDF Pro Edition) which you'll find on
our web in the Developer tools section.

--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

---------------begin merge code----------------------
Const wdSendToNewDocument = 0
Const wdSaveChanges As Integer = -1
Const wdDoNotSaveChanges As Byte = 0
Set objWordDoc = objWordApp.Documents.Open(strTargetMergeTemplateDocName)
DoEvents
objWordDoc.Application.Visible = False
With objWordDoc.MailMerge
.MainDocumentType = wdFormLetters
strConnection = "DSN=MS Access Databases;" _
& "DBQ=C:\somedir\yourdb.mdb;" _
& "FIL=RedISAM;"
.OpenDataSource Name:="C:\somedir\yourdb.mdb", _
Connection:=strConnection, _
SQLStatement:="SELECT * FROM Customers WHERE CustID =" &
TargetCustID
.Destination = wdSendToNewDocument
.Execute
End With
'Set focus to the resultant merged document
Set objWordDoc = objWordApp.ActiveDocument
With objWordDoc
.SaveAs strOutputMergedDocTargetFileName

' OR Simply Print it to a PDF file
'objWordApp.ActivePrinter = "Name of Your PDF Printer Here"
'.PrintOut Background:=False

.Close wdDoNotSaveChanges
End With
'Re-set focus to the merge template
Set objWordDoc = objWordApp.Documents(strTargetMergeTemplateDocName)
With objWordDoc
.Close wdDoNotSaveChanges
End With
ObjWordApp.Quit
-----------------------end code------------------------
 
Thank you.

what I am trying to do is email a report automatically.
If I have a report that has produced 20 personalised letters how can I email
them to the available email addresses instead of printing and posting them?
Is it possible?
 
Derek:

Yes, its possible as I noted in my first reply to your post. Originally
you mentioned e-mailing a Word document, now you mention e-mailing a report.
In principal achieving both requires the same logic (but vastly different
operations), namely that you have to filter the target (word doc or report)
to produce a single output for the single target recipient (i.e. per my
original post, you filter the mail merge to be for one output document, or
if you are working with a report, you filter it to produce only one page
using the SQL WHERE option of docmd.OpenReport) and print either output to a
file like a PDF file. Because Access doesn't directly support attaching a
document (Word doc, PDF File etc.) to a mail item, you have to then program
outlook to do that using automation. Our PDF and Mail Library can assist
you in doing this if you are using an Access report as the basis for your
mail items. It has lots of code samples in the accompanying help file
related to the mail class. You'll find it on our web.
 
Back
Top