Solved

I need help emailing letters to individual email addresses

Posted on 2010-08-12
9
553 Views
Last Modified: 2013-11-28
I need to preface this message by saying that while I have a lot of experience creating databases and working with macros, I have next to no experience with VB coding so any help I receive I really need specific help doing what I need to.Basically, I have a database where I run a report that is basically an hours status letter that runs for customers based on some criteria of number of hours remaining and activity in a month.  The result is a report that contains several letters, one for each customer and I am currently printing the entire report, parsing out each customer's report and mailing it.What I would like is an automated way to generate each customer's letter and email is a pdf attachment.  I have gone through various other threads and have seen references to recordsets and looping but due to my novice status, I am unable to figure out how to apply this information to my database and integrate it in. I understand this is a novice question and that anybody who responds will need to give me some handholding to walk me through how to get this code in and how to customize it with my database.  Thanks very much, any help is greatly appreciated.
0
Comment
Question by:nomi1
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 125 total points
ID: 33424663
Without going into a ton of detail, this sounds like the sort of thing Word's Mail Merge was made for.  Word is content to use Access as the data source and Outlook as the delivery mode for a Mail Merge.
0
 
LVL 10

Expert Comment

by:t_hungate
ID: 33425326
nomi1

When you get a chance checkout this article, you can probably skip the first portion of it since you said you have been creating DBs for  while now.  But i may be a good starter for you see how and where the VBA portions start to come into it.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3560-Beginners-How-to-utilize-an-Experts-solution-in-the-VBA-editor-with-MS-Access.html?sfQueryTermInfo=1+30+beginn

TLH
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 33425635
The thing here is that you cannot simply Email a report, you have to specify a format
(RTF, XLS, PDF, ...ect)

You did not specify your version of Access.
The is important because, in order to create a PDF, you will need an External utility for Access 2003 and older.
You will need to PDF Addin installed for Access 2007 and newer.

Here is a sample of both:
Access 2003 an older, PDF and RTF
Access 2007 PDF and rtf

I am sure with your experience you will be able to read through the code and tweak/troubleshoot it to work in your database.

;-)

JeffCoachman


Access-EmailBulkIndividualCustom.mdb
Access--EmailIndividualCustomerR.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33425676
Oh,

I just read the expert post.

Yes, if you have not the Skills to use VBA, then the Word route might be an easier option.
So give that a try first.

If you plan on posting more questions here requiring code solutions, then you should pick up a good reference book.
Here are a few:
http://www.amazon.com/Microsoft-Access-Programming-Absolute-Beginner/dp/1598633937/ref=sr_1_1?ie=UTF8&s=books&qid=1281655548&sr=8-1
http://www.amazon.com/Access-2007-VBA-Bible-Data-Centric/dp/047004702X/ref=sr_1_27?ie=UTF8&s=books&qid=1281655606&sr=8-27

You can goole for more...

;-)

JeffCoachman
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 125 total points
ID: 33438219
Here is some code to save filtered reports as PDFs and email each to the appropriate address:
Option Compare Database

Option Explicit





Private dbs As DAO.Database

Private qdf As DAO.QueryDef

Private rst As DAO.Recordset





Public Sub SendInterventionEmails()

'Created by Helen Feddema 10-Jan-2010

'Last modified by Helen Feddema 10-Jan-2010



On Error GoTo ErrorHandler



   Dim appOutlook As New Outlook.Application

   Dim itm As Outlook.MailItem

   Dim rstIntervention As DAO.Recordset

   Dim lngCount As Long

   Dim lngID As Long

   Dim rpt As Access.Report

   Dim strFileName As String

   Dim strPrompt As String

   Dim strQuery As String

   Dim strRecordSource As String

   Dim strReport As String

   Dim strSQL As String

   Dim strTitle As String

   Dim strCurrentPath As String

   Dim strFileNameAndPath As String

   Dim strEmailSource As String

   

   strEmailSource = "qryInterventionEmail"

   strRecordSource = "qryMissingAssignments"

   strQuery = "qryMissingAssignmentsSingleStudent"

   Set dbs = CurrentDb

   Set rstIntervention = dbs.OpenRecordset(strEmailSource)

   strCurrentPath = Application.CurrentProject.Path & "\"

   

   'Use path selected with SelectFolder procedure

   'strCurrentPath = SelectFolder()



   With rstIntervention

      Do While Not .EOF

         lngID = ![StID]

         Debug.Print "Processing Student ID " & lngID

         strFileName = "Intervention Report for " & ![StFirst] _

            & " " & ![StLast] & ".pdf"

         strFileNameAndPath = strCurrentPath & strFileName

         

         'Create filtered query

         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _

            & "[StID] = " & Chr(39) & lngID & Chr(39) & ";"

         Debug.Print "SQL for " & strQuery & ": " & strSQL

         lngCount = CreateAndTestQuery(strQuery, strSQL)

         Debug.Print "No. of items found: " & lngCount

         If lngCount = 0 Then

            GoTo NextStudent

         End If

      

         'Open report with filtered query record source

         strReport = "rptMissingAssignmentsNew"

         DoCmd.OpenReport ReportName:=strReport, _

            View:=acViewPreview, _

            windowmode:=acWindowNormal

         Set rpt = Reports(strReport)

         DoCmd.OutputTo objecttype:=acOutputReport, _

            objectname:=strReport, _

            outputformat:=acFormatPDF, _

            outputfile:=strFileNameAndPath

         

         'Create email

         Set itm = appOutlook.CreateItem(olMailItem)

         itm.Subject = "MISSING WORK"

         itm.Body = "The attached file lists your missing assignments"

         itm.To = ![Email]

         itm.Attachments.Add Source:=strFileNameAndPath, _

            Type:=olByValue

         

         'For editing before sending

         itm.Display

         

         'For sending automatically

         'itm.Send

         DoCmd.Close objecttype:=acReport, _

            objectname:=strReport, _

            Save:=acSaveNo



NextStudent:

         .MoveNext

      Loop

   End With

   

ErrorHandlerExit:

   Exit Sub



ErrorHandler:

   MsgBox "Error No: " & Err.Number _

      & " in SendInterventionEmails procedure; " _

      & "Description: " & Err.Description

   Resume ErrorHandlerExit



End Sub



Public Function CreateAndTestQuery(strTestQuery As String, _

   strTestSQL As String) As Long

'Created by Helen Feddema 28-Jul-2002

'Last modified by Helen Feddema 10-Jan-2010



On Error Resume Next

   

   'Delete old query

   Set dbs = CurrentDb

   dbs.QueryDefs.Delete strTestQuery



On Error GoTo ErrorHandler

   

   'Create new query

   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)

   

   'Test whether there are any records

   Set rst = dbs.OpenRecordset(strTestQuery)

   With rst

      .MoveFirst

      .MoveLast

      CreateAndTestQuery = .RecordCount

   End With

   

ErrorHandlerExit:

   Exit Function



ErrorHandler:

   If Err.Number = 3021 Then

      CreateAndTestQuery = 0

      Resume ErrorHandlerExit

   Else

   MsgBox "Error No: " & Err.Number _

      & " in CreateAndTestQuery procedure; " _

      & "Description: " & Err.Description

   End If

   

End Function

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 33438229
My ebook Working with Word may also be helpful.  It updates some of the examples in my Access 2007 VBA Bible book, and adds some new examples.
0
 
LVL 10

Accepted Solution

by:
LukeChung-FMS earned 125 total points
ID: 33582946
If you're not a coder and want to use a solution that runs as an Access add-in, our Total Access Emailer program, http://www.fmsinc.com/MicrosoftAccess/Email.asp, will do what you seek.

Total Access Emailer will take a table with a list of email addresses and let you filter reports based on values in your table to send emails with PDF reports specifically for each contact.

Your messages can be in text or HTML. The HTML messages can include embedded graphics, and the messages can automatically use fields from your table so each message and subject can be personalized.

Best of all, you don't need to do any programming for all of these features.

A free demo is available for you to try it out.  Good luck.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36157638
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now