Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 22

Thread: Generating an email from VBA which pulls results from a query

  1. #1
    VBAX Regular
    Joined
    Jan 2021
    Posts
    11
    Location

    Question Generating an email from VBA which pulls results from a query

    Hello,

    I am trying to generate an email from an Access database when the user clicks a button on a form. I need it to pull records from a table where the records equal the value chosen in a combo box. Right now, this code below will pop just an email with the information after the Set objoutlook line. Nothing from the query is pulled.

    I had to attach my code as a .docx because the forum wouldn't let me post it for some reason.

    I would greatly appreciate some help. Thanks in advance.

    vbacode.docx

  2. #2
    VBAX Regular
    Joined
    Jan 2021
    Posts
    11
    Location
    Just wanted to bump this to see if anyone could help?

  3. #3
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Do you still need help with this?

  4. #4
    VBAX Regular
    Joined
    Jan 2021
    Posts
    11
    Location
    Quote Originally Posted by OBP View Post
    Do you still need help with this?

    Yes, please. I would really appreciate it.

  5. #5
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I am not sure what is wrong with your code, but it doesn't have any Error Trapping to tell you if it has crashed.

    The code that I use does not use a Query def as it is not necesarry, you only need to use a Recordset.
    I am not sure if you want to mail out one email or multiple emails from the query?

  6. #6
    VBAX Regular
    Joined
    Jan 2021
    Posts
    11
    Location
    Quote Originally Posted by OBP View Post
    I am not sure what is wrong with your code, but it doesn't have any Error Trapping to tell you if it has crashed.

    The code that I use does not use a Query def as it is not necesarry, you only need to use a Recordset.
    I am not sure if you want to mail out one email or multiple emails from the query?
    This code was something I tried to use after looking around on the internet. If there is a better way I am all for it.

    This code needs to generate one email based on the value selected in a combo box. That information needs to be populated in the email you see in the code at the bottom of what I submitted.

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, give me a few minutes to find some of my code.

  8. #8
    VBAX Regular
    Joined
    Jan 2021
    Posts
    11
    Location
    Quote Originally Posted by OBP View Post
    OK, give me a few minutes to find some of my code.
    No problem! I appreciate it.

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, I am going to try and go with a modified version of your code as you have doen a lot of work on the formatting etc

    Dim rec As Object, ahead(1 To 4) As String, aRow(1 To 4) As String, aBody() As String, lCnt As Long
    On errer goto errorcatch

    strQry = "PARAMETERS cboParam TEXT(255);" _
    & " SELECT [LoanID], [Prior LoanID], [SRP Rate], [SRP Amount] " _
    & " FROM emailtable " _
    & " WHERE [Seller Name:Refer to As] ='" & [cboParam] & "'"

    ' BIND PARAMETER
    'qdef!cboParam = Me.Combo296

    ' OPEN RECORDSET
    Set rec = CurrentDb.OpenRecordset(SQL)
    If rec.recordcount<>0 then

    msgbox rec.[Seller Name:Refer to As]
    End if

    rec.close

    Exit sub


    'Create the header row
    ahead(1) = "Loan ID"
    ahead(2) = "Prior Loan ID"
    ahead(3) = "SRP Rate"
    ahead(4) = "SRP Amount"

    lCnt = 1
    ReDim aBody(1 To lCnt)
    aBody(lCnt) = "<HTML><body><table border='2'><tr><th>" & Join(ahead, "</th><th>") & "</th></tr>"

    If Not (rec.BOF And rec.EOF) Then
    Do While Not rec.EOF
    lCnt = lCnt + 1
    ReDim Preserve aBody(1 To lCnt)
    aRow(1) = rec("[LoanID]")
    aRow(2) = rec("[Prior LoanID]")
    aRow(3) = rec("[SRP Rate]")
    aRow(4) = rec("[SRP Amount]")
    aBody(lCnt) = "<tr><td>" & Join(aRow, "</td><td>") & "</td></tr>"
    rec.MoveNext
    Loop
    End If

    aBody(lCnt) = aBody(lCnt) & "</table></body></html>"

    Set objOutlook = CreateObject("Outlook.Application.16")
    Set objMail = objOutlook.CreateItem(0)
    With objMail
    .Display 'To display message
    .to = Me.Combo88
    .cc = Me.Combo282
    .Subject = "*SECURE* " & Me.Combo296 & " EPO Refund Request (" & Me.Combo212 & " " & Me.Combo284 & ")"
    .HTMLBody = "<p><font face=""calibri"" font-size:11pt;"">Greetings,</p>" _
    & "<p>We recently acquired loans from " & Me.Combo296 & ", some of which have paid in full and meet the criteria for early prepayment defined in the governing documents. We are requesting a refund of the SRP amount detailed on the attached list.</p>" _
    & "<p>Please wire funds to the following instructions:</p>" _
    & "<ul>Bank Name: My Bank</ul>" _
    & "<ul>ABA: 123456</ul>" _
    & "<ul>Credit To: My Mortgage</ul>" _
    & "<ul>Acct: 54321</ul>" _
    & "<ul>Description: " & Combo296 & " EPO SRP Refund</ul>" _
    & "<p>Thank you for the opportunity to service loans from " & Me.Combo296 & "! We appreciate your partnership.</p>" _
    & "<p>If you have any questions, please contact your Relationship Manager, " & Me.Combo336 & " (Cc'd).</p>" _
    & "<p><br>Sincerely,</br>" _
    & "<br>Acquisitions</br>" _
    & "<br>email@me.com</br></p>"

    End With

    rec.Close
    Set rec = Nothing: Set qdef = Nothing: Set db = Nothing

    Exit Sub
    errorcatch:
    MsgBox Err.Description



    OK, try the code above, if it crashes let me know what the error description is.
    If it works it should just give you a message box with the name that you chose in the Combo.

  10. #10
    VBAX Regular
    Joined
    Jan 2021
    Posts
    11
    Location
    Ok. I am getting an error message.

    2021-01-25_13-20-17.jpg

    When I click OK, this pops up.

    2021-01-25_13-20-34.jpg

  11. #11
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Are you running this as a macro?
    I always run code as a Procedure.

  12. #12
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, I think it is this line
    Set rec = CurrentDb.OpenRecordset(SQL)
    Change it to

    Set rec = CurrentDb.OpenRecordset(strQry)

  13. #13
    VBAX Regular
    Joined
    Jan 2021
    Posts
    11
    Location
    This database was created by someone else and I was ask to see if I could make this email piece work. In the dropdown of a form the user selects a value in Combo249 that populates the fields in the rest of the form. Once those are populated, we click the "email" button and it pops an email with the information from that search. With the original code I posted, the email would pop with everything but the information from the strQry.

    Here is the embedded macro:

    2021-01-25_13-42-01.jpg

  14. #14
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, try the code that I changed, I was using my SQL instead of your strSQL

  15. #15
    VBAX Regular
    Joined
    Jan 2021
    Posts
    11
    Location
    I changed it to Set rec = CurrentDb.OpenRecordset(strQry) I get the same message.

  16. #16
    VBAX Regular
    Joined
    Jan 2021
    Posts
    11
    Location
    Does it have anything to do with this line in the query:

    & " WHERE [Seller Name:Refer to As] ='" & [cboParam] & "'"

    Since this line is commented out?

    ' BIND PARAMETER
    'qdef!cboParam = Me.Combo296

  17. #17
    VBAX Regular
    Joined
    Jan 2021
    Posts
    11
    Location
    I tried un-commenting that line and still the same error.

  18. #18
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I also have a spelling mistake here
    On errer goto errorcatch

    It should be
    On error goto errorcatch

    That is the problem with doing this on the fly and in a hurry.

  19. #19
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    The line you point to should if anything give a missing parameter or Syntax error.

    I don't suppose that you can post a copy of the database with a few Dummy records in it?

  20. #20
    VBAX Regular
    Joined
    Jan 2021
    Posts
    11
    Location
    Quote Originally Posted by OBP View Post
    The line you point to should if anything give a missing parameter or Syntax error.

    I don't suppose that you can post a copy of the database with a few Dummy records in it?
    The spelling error didn't change anything.

    Let me work on getting a dummy db like this one put together and I can attach it. I appreciate the help and will hopefully have something by tomorrow.

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
  •