Results 1 to 4 of 4

Send email through access using data from record set

  1. #1
    snsmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8

    Send email through access using data from record set

    Iíve gotten help previously regarding filtering my code to a specific input from a form and it has mostly worked. https://www.accessforums.net/showthr...ge=2&styleid=6



    my code is above in the original string, but the formatting didnít carry over so itís harder to read. Iím trying to do this post without my computer so I apologize for not being able to post the code directly. My new issue is that the emails will generate and the subject line appears to be tying appropriately to the record set. However, the first email has ALL record sets added to the email and each subsequent one has one less. Of course this works perfectly when there is only one line of data. Can someone help guide me to how to limit the data in the emails to a single record set until it moved through all the records?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,339
    Please post code between CODE tags to retain indentation and readability.

    Using another variable really wasn't needed.

    Do need to set the variable back to empty string at beginning of loop.

    emailText = ""

    Or else the first line should not concatenate the variable:

    emailText = "Hello, " & vbCrLf

    However, without either, I would expect each iteration to add a record to the string, not be a reducing dataset.

    Not seeing a recordset opened in the last code version.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    snsmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8
    Here is the code. I do have an open recordset, but perhaps it wasn't showing on the original post. After your comment I did go back and analyze the results and it was increasing. The first record only had it's data in the email while each subsequent line had it's data and every data for the lines above it. I'm trying to limit the email to just one line of data and then move to the next.

    Private Sub Dept_Deposit_Notification_Click()

    Dim db As Dao.Database
    Dim rs As Dao.Recordset
    Dim strSQL As String

    Dim emailTo As String
    Dim emailCC As String
    Dim emailSubject As String
    Dim emailText As String
    Dim smsg As String

    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outStarted As Boolean
    Dim qdf As Dao.QueryDef
    Dim prm As Parameter
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.application")
    On Error GoTo 0

    If outApp Is Nothing Then
    Set outApp = CreateObject("Outlook.application")
    outStarted = True
    End If
    'get data
    Set db = CurrentDb
    Debug.Print strSQL

    Set qdf = db.QueryDefs("OCRChecksReceivedDeptNotification")
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm

    Set rs = qdf.OpenRecordset


    'send email
    Do Until rs.EOF

    emailText = ""
    emailTo = rs.Fields("[Check Notification Contact Email]").Value
    emailCC = "OCR-Financials@ahc.ufl.edu"

    emailSubject = "Funds received for " & rs.Fields("[PI Name]").Value & "'s project " & rs.Fields("[myUFL Master Project]").Value

    smsg = smsg & "Hello, " & vbCrLf
    smsg = smsg & "OCR has recently completed a deposit for one of your projects. Additional information can be found below or on the deposit log which will be updated in the next few business days. " & vbCrLf
    smsg = smsg & " myUFL Project Number: " & rs.Fields("myUFL Master Project").Value & vbCrLf
    smsg = smsg & " Check Number: " & rs.Fields("Check Number").Value & vbCrLf
    smsg = smsg & " Date of Check: " & rs.Fields("Date of Check").Value & vbCrLf
    smsg = smsg & " Total Amount of check: " & Format(rs.Fields("Total Amt of Check").Value, "Currency") & vbCrLf
    smsg = smsg & " IDC Charged to Project: " & Format(rs.Fields("IDC Charged to Project").Value, "Currency") & vbCrLf
    smsg = smsg & " Department ID: " & rs.Fields("Department ID").Value & vbCrLf
    smsg = smsg & " Deposit ID: " & rs.Fields("Deposit ID").Value & vbCrLf
    smsg = smsg & " Date of Deposit: " & rs.Fields("Date of Deposit").Value & vbCrLf
    smsg = smsg & " Payment is split: " & rs.Fields("Payment is split").Value & vbCrLf
    smsg = smsg & " Is Backup Available: " & rs.Fields("Is Backup Available").Value & vbCrLf
    smsg = smsg & " Payee: " & rs.Fields("Payee").Value & vbCrLf


    smsg = smsg & vbCrLf

    smsg = smsg & " Thank you," & vbCrLf
    smsg = smsg & " OCR"

    emailText = smsg

    Set outMail = outApp.CreateItem(olMailItem)
    outMail.To = emailTo
    outMail.CC = emailCC
    outMail.Subject = emailSubject
    outMail.Body = emailText
    outMail.Display
    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    If outStarted Then
    outApp.Quit
    End If
    Set outMail = Nothing
    Set outApp = Nothing

    End Sub

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,339
    Please, please, please post code between CODE tags to retain indentation and readability, especially lengthy code.

    As already advised, reset variable to empty string

    smsg = ""

    or don't concatenate the first line.

    smsg = "Hello, " & vbCrLf
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-12-2018, 05:38 AM
  2. Replies: 3
    Last Post: 09-30-2016, 04:49 PM
  3. New Record - send email HELP! :(
    By ThorstenG in forum Database Design
    Replies: 4
    Last Post: 04-14-2015, 08:38 AM
  4. Replies: 1
    Last Post: 09-07-2014, 10:15 PM
  5. Replies: 9
    Last Post: 10-09-2009, 08:15 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums