Generating an email from VBA which pulls results from a query

**skailey** · 01-21-2021, 01:05 PM

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

**skailey** · 01-22-2021, 08:30 AM

Just wanted to bump this to see if anyone could help?

**OBP** · 01-25-2021, 06:23 AM

Do you still need help with this?

**skailey** · 01-25-2021, 07:06 AM

Originally Posted by OBP

Do you still need help with this?

Yes, please. I would really appreciate it.

**OBP** · 01-25-2021, 10:31 AM

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?

**skailey** · 01-25-2021, 10:43 AM

Originally Posted by OBP

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.

**OBP** · 01-25-2021, 11:08 AM

OK, give me a few minutes to find some of my code.

**skailey** · 01-25-2021, 11:15 AM

Originally Posted by OBP

OK, give me a few minutes to find some of my code.

No problem! I appreciate it.

**OBP** · 01-25-2021, 11:35 AM

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.

**skailey** · 01-25-2021, 12:25 PM

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

**OBP** · 01-25-2021, 12:36 PM

Are you running this as a macro?
I always run code as a Procedure.

**OBP** · 01-25-2021, 12:46 PM

OK, I think it is this line

Set rec = CurrentDb.OpenRecordset(SQL)

Change it to

Set rec = CurrentDb.OpenRecordset(strQry)

**skailey** · 01-25-2021, 12:47 PM

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

**OBP** · 01-25-2021, 01:00 PM

OK, try the code that I changed, I was using my SQL instead of your strSQL

**skailey** · 01-25-2021, 01:03 PM

I changed it to

Set rec = CurrentDb.OpenRecordset(strQry)

I get the same message.

**skailey** · 01-25-2021, 01:05 PM

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

**skailey** · 01-25-2021, 01:06 PM

I tried un-commenting that line and still the same error.

**OBP** · 01-25-2021, 01:14 PM

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.

**OBP** · 01-25-2021, 01:16 PM

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?

**skailey** · 01-25-2021, 02:27 PM

Originally Posted by OBP

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.

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

Thread Tools

Display

Generating an email from VBA which pulls results from a query

Tags for this Thread

Posting Permissions