elwayfan446
Programmer
Hello,
I am having a couple of problems trying to generate an email based on results from a query.
1. I am trying to include the results from the query in a table in the body of an email. I have researched this on the internet and still can't quite get it to work.
2. I have Outlook 365 installed on my PC and the email doesn't open unless outlook is closed on my PC.
I would really appreciate some help to get this to work. Thanks in advance!
I am having a couple of problems trying to generate an email based on results from a query.
1. I am trying to include the results from the query in a table in the body of an email. I have researched this on the internet and still can't quite get it to work.
2. I have Outlook 365 installed on my PC and the email doesn't open unless outlook is closed on my PC.
I would really appreciate some help to get this to work. Thanks in advance!
Code:
Option Compare Database
Private Sub Command0_Click()
Dim db As DAO.Database, qdef As DAO.QueryDef, rec As DAO.Recordset, ahead(1 To 4) As String, aRow(1 To 4) As String, aBody() As String, lCnt As Long
[indent]strQry = "PARAMETERS cboParam TEXT(255);" _
& " SELECT [LoanID], [Prior LoanID], [SRP Rate], [SRP Amount] " _
& " FROM emailtable " _
& " WHERE [Seller Name:Refer to As] = [cboParam]"[/indent]
Set db = CurrentDb
Set qdef = db.CreateQueryDef("", strQry)
' BIND PARAMETER
qdef!cboParam = Me.Combo296
' OPEN RECORDSET
Set rec = qdef.OpenRecordset()
'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"" style=""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
End Sub
