Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Unanswered: Need help with loop to send multi emails

    Hi - I have a script that I got off the internet to send pdf emails. It uses pdf995 program. All works fine there. However, I now have a need to send multiple emails automatically. Can someone take this code and add a loop sequence to send out the RMA report based on the RMA table. One record for each report. The email address is in the RMA table. I have put together an example in the attached database. I am not a programmer but have tried unsuccessfully.

    Any help is greatly appreciated!!
    B&R
    Attached Files Attached Files

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by B&R
    Can someone take this code and add a loop sequence to send out the RMA report based on the RMA table. One record for each report. The email address is in the RMA table.
    FAQ
    Quote Originally Posted by Teddy's FAQ
    DBForums may be helpful in a variety of situations, but it is not the one-stop shop for every situation. Here are a few things the MS Access fourm is not

    * Paid Tech Support:
    We are not a paid tech support forum. Please keep this in mind when evaluationg responses to your questions. It is unrealistic to hold an expectation of service above and beyond that of a forum comprised of volunteer members who share their knowledge, experience and solutions just because they like to help. If you expect or demand solutions from our members, you will be unlikely to receive any useful information. If your expectations are flagarantly obnoxious/unrealistic, expect your post to be moderated.
    We can help you do this though. If you want to do this as a loop, check out the For...Next loop in vb help, and then look at the dcount function to get the number of loops. That shoould get you started in the right direction.
    Me.Geek = True

  3. #3
    Join Date
    Feb 2004
    Posts
    139
    Thanks for your response but I had already searched on the access forum and could not find anything to help me. I went to the vb forum and searched on next loop but I did not find the dcount function. Where exactly can I find this example you are speaking of?

    Appreciate your help!
    B&R

  4. #4
    Join Date
    Sep 2006
    Posts
    265
    This is bit tricky but well worth the ask. I use html and embed it into the body and a selection form as a frontend, hence the "With CodeContextObject". I have never had to use the attachment so it may need testing. I hope this is useful.

    A truncated section looks like this.

    With CodeContextObject

    Set rs = db.OpenRecordset("SELECT …”)

    Do

    objMFrom = .Reply
    objMTo = rs![Client Email]
    objMSubj = .Subject

    ' Start collecting the mail components and test components before inclusion

    Set objCDOMail = CreateObject("CDO.Message")
    objCDOMail.FROM = objMFrom
    objCDOMail.To = objMTo
    objCDOMail.Subject = objMSubj
    objCDOMail.TextBody = objMText
    objCDOMail.HTMLBody = objMText & objMBody

    If Len(.Attachment) > 0 Then
    objCDOMail.AttachFile = objMAtt
    End If

    objCDOMail.Send
    rs.MoveNext

    Loop Until rs.EOF

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set objCDOMail = Nothing

    End With

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by B&R
    Thanks for your response but I had already searched on the access forum and could not find anything to help me. I went to the vb forum and searched on next loop but I did not find the dcount function. Where exactly can I find this example you are speaking of?
    I wasn't referring to an example on these forums, I was saying to refer to the vb help. Go to your module go Help > Microsoft Visual Basic Help, or alternatively, type the function you wish to use (either For or Dcount), and with your cursor in the word, press F1. It'll give you a pretty good summary of what each does.
    Me.Geek = True

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SimonMT's solution should work perfectly for you -

    SET rs = db.OpenRecordset("SELECT

    DO

    <send e-mail>

    LOOP Until rs.EOF
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Posts
    139
    Thanks for all the help but I do not know vb so this all looks so difficult to me. I was thinking that I could use a do while loop to achieve but I have not been successful yet. In the past, I reference a form field in the email section of this code and it sent the report to the email address of a form I referenced. What Simon suggested, I did not understand.

    I am trying to use the table RMA and send the report RMA. If someone could insert the appropriate lines of code into the existing code, I would appreciate it and promise I will learn from their assistance.

    Any help is appreciated!
    B&R


    Option Compare Database
    Option Explicit

    'Read INI settings
    Declare Function GetPrivateProfileString Lib "kernel32" Alias _
    "GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
    ByVal lpKeyName As Any, ByVal lpDefault As String, _
    ByVal lpReturnedString As String, ByVal nSize As Long, _
    ByVal lpFileName As String) As Long

    'Write settings
    Declare Function WritePrivateProfileString Lib "kernel32" Alias _
    "WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
    ByVal lpKeyName As Any, ByVal lpString As Any, _
    ByVal lpFileName As String) As Long

    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Sub pdfwritetest(reportname As String, destpath As String, Optional strcriteria As String)

    ' Runs an Access report to PDF995 to create a pdf file from the report.
    ' Input parameters are the name of the report within the current database,
    ' the path for the output file, and an optional criteria for the report

    ' Be sure to check that the "Generating PDF CS" setting in pdfsync.ini is set to 0
    ' when pdf995 is idle. This codes uses that as a completion flag as it seems to be
    ' the most reliable indication that PDF995 is done writing the pdf file.


    ' Note: The application.printer object is not valid in Access 2000
    ' and earlier. In that case, set the printer in the report to pdf995
    ' and comment out the references herein to the application.printer


    Dim syncfile As String, maxwaittime As Long
    Dim iniFileName As String, tmpPrinter As Printer
    Dim outputfile As String, x As Long
    Dim tmpoutputfile As String, tmpAutoLaunch As String

    ' set the location of the PDF995.ini and the pdfsync files
    iniFileName = "c:\Program Files\pdf995\res\pdf995.ini"
    syncfile = "c:\documents and settings\all users\application data\pdf995\pdfsync.ini"

    ' build the output file name from the path parameter and the report name
    If Mid(destpath, Len(destpath), 1) <> "\" Then destpath = destpath & "\"
    outputfile = destpath & reportname & ".pdf"

    ' PDF995 operates asynchronously. We need to determine when it is done so we can
    ' continue. This is done by creating a file and having PDF995 delete it using the
    ' ProcessPDF parameter in its ini file which runs a command when it is complete.

    ' save current settings from the PDF995.ini file
    tmpoutputfile = ReadINIfile("PARAMETERS", "Output File", iniFileName)
    tmpAutoLaunch = ReadINIfile("PARAMETERS", "Autolaunch", iniFileName)

    ' remove previous pdf if it exists
    On Error Resume Next
    Kill outputfile
    On Error GoTo Cleanup

    ' setup new values in PDF995.ini
    x = WritePrivateProfileString("PARAMETERS", "Output File", outputfile, iniFileName)
    x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", "0", iniFileName)

    ' change the default printer to PDF995
    ' if running on Access 2000 or earlier, comment out the next two lines
    Set tmpPrinter = Application.Printer
    Application.Printer = Application.Printers("PDF995")

    'print the report
    DoCmd.OpenReport reportname, acViewNormal, , strcriteria

    ' cleanup delay to allow PDF995 to finish up. When flagfile is nolonger present, PDF995 is done.
    Sleep (10000)
    maxwaittime = 300000 'If pdf995 isn't done in 5 min, quit anyway
    Do While ReadINIfile("PARAMETERS", "Generating PDF CS", syncfile) = "1" And maxwaittime > 0
    Sleep (10000)
    maxwaittime = maxwaittime - 10000
    Loop

    ' restore the original default printer and the PDF995.ini settings
    Cleanup:
    Sleep (10000)
    x = WritePrivateProfileString("PARAMETERS", "Output File", tmpoutputfile, iniFileName)
    x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", tmpAutoLaunch, iniFileName)
    x = WritePrivateProfileString("PARAMETERS", "Launch", "", iniFileName)
    On Error Resume Next

    ' if running on Access 2000 or earlier, comment out the next line
    Application.Printer = tmpPrinter

    Shell """" & "C:\Documents and Settings\TOBIE\My Documents" & "\sendEmail.exe"" -s " & "mail.earthlink.net" & " -f " & "TOBIE@earthlink.net" & " -t " & "TOBIE@EARTHLINK.net" & " -u """ & "None" & """ -m """ & "None" & """ -a """ & "C:\Documents and Settings\TOBIE\My Documents" & "\" & "RMA.pdf" & """", vbHide

    End Sub

    Function ReadINIfile(sSection As String, sEntry As String, sFilename As String) As String
    Dim x As Long
    Dim sDefault As String
    Dim sRetBuf As String, iLenBuf As Integer
    Dim sValue As String

    'Six arguments
    'Explanation of arguments:
    'sSection: ini file section (always between brackets)
    'sEntry : word on left side of "=" sign
    'sDefault$: value returned if function is unsuccessful
    'sRetBuf$ : the value you're looking for will be copied to this buffer string
    'iLenBuf% : Length in characters of the buffer string
    'sFileName: Path to the ini file

    sDefault$ = ""
    sRetBuf$ = String$(256, 0) '256 null characters
    iLenBuf% = Len(sRetBuf$)
    x = GetPrivateProfileString(sSection, sEntry, _
    sDefault$, sRetBuf$, iLenBuf%, sFilename)
    ReadINIfile = Left$(sRetBuf$, x)

    End Function

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Posting a rediculously big block of code is not going to help your situation.

    Step 1) write some SQL to pick out everyone you want to e-mail.
    Step 2) insert the SQL into this line
    SET rs = db.OpenRecordset("<your SQl statement in quotes>")
    Step 3) DO '(this is the start of your loop)
    Step 4) Add the code that sends your e-mail
    Step 5) LOOP Until rs.EOF (EOF = End Of File (loop until you hit the end of your SELECT statement))

    Just give it a try
    George
    Home | Blog

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •