• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

I need help emailing letters to individual email addresses

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
nomi1
Asked:
nomi1
  • 2
  • 2
  • 2
  • +2
4 Solutions
 
Patrick MatthewsCommented:
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
 
t_hungateCommented:
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
 
Jeffrey CoachmanCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
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
 
Helen FeddemaCommented:
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
 
Helen FeddemaCommented:
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
 
Luke ChungPresidentCommented:
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
 
Patrick MatthewsCommented:
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now