Generating an email from VBA which pulls results from a query

Printable View

01-21-2021, 01:05 PM
skailey

1 Attachment(s)

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.

Attachment 27781
01-22-2021, 08:30 AM
skailey

Just wanted to bump this to see if anyone could help?
01-25-2021, 06:23 AM
OBP

Do you still need help with this?
01-25-2021, 07:06 AM
skailey

Quote:

Originally Posted by OBP

Do you still need help with this?

Yes, please. I would really appreciate it.
01-25-2021, 10:31 AM
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?
01-25-2021, 10:43 AM
skailey

Quote:

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.
01-25-2021, 11:08 AM
OBP

OK, give me a few minutes to find some of my code.
01-25-2021, 11:15 AM
skailey

Quote:

Originally Posted by OBP

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

No problem! I appreciate it.
01-25-2021, 11:35 AM
OBP

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

Code:

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 = "Greetings," _ & "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." _ & "Please wire funds to the following instructions:" _ & "<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>" _ & "Thank you for the opportunity to service loans from " & Me.Combo296 & "! We appreciate your partnership." _ & "If you have any questions, please contact your Relationship Manager, " & Me.Combo336 & " (Cc'd)." _ & " Sincerely," _ & " Acquisitions" _ & " email@me.com" 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.
01-25-2021, 12:25 PM
skailey

2 Attachment(s)

Ok. I am getting an error message.

Attachment 27800

When I click OK, this pops up.

Attachment 27801
01-25-2021, 12:36 PM
OBP

Are you running this as a macro?
I always run code as a Procedure.
01-25-2021, 12:46 PM
OBP

OK, I think it is this line

Code:

Set rec = CurrentDb.OpenRecordset(SQL)

Change it to

Code:

Set rec = CurrentDb.OpenRecordset(strQry)
01-25-2021, 12:47 PM
skailey

1 Attachment(s)

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:

Attachment 27802
01-25-2021, 01:00 PM
OBP

OK, try the code that I changed, I was using my SQL instead of your strSQL
01-25-2021, 01:03 PM
skailey

I changed it to

Code:

Set rec = CurrentDb.OpenRecordset(strQry)

I get the same message.
01-25-2021, 01:05 PM
skailey

Does it have anything to do with this line in the query:

Code:

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

Since this line is commented out?

Code:

' BIND PARAMETER 'qdef!cboParam = Me.Combo296
01-25-2021, 01:06 PM
skailey

I tried un-commenting that line and still the same error.
01-25-2021, 01:14 PM
OBP

I also have a spelling mistake here

Code:

On errer goto errorcatch

It should be

Code:

On error goto errorcatch

That is the problem with doing this on the fly and in a hurry.
01-25-2021, 01:16 PM
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?
01-25-2021, 02:27 PM
skailey

Quote:

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.
01-25-2021, 02:36 PM
OBP

OK, talk to you tomorrow.
01-27-2021, 06:37 AM
OBP

If you are havng problems with recreating the database I would suggest.
1. Create a new Database.
2. Import only those tables & queries required to operate the form.
3. import the form.
4. Delete all but 2 or 3 records from the table(s).
5. change any sensitive data.
6. Compact & repair the new database.
7. Put it in a Zip file.