Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access VBA to generate email from table

Access VBA to generate email from table

Access VBA to generate email from table


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!


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

strQry = "PARAMETERS cboParam TEXT(255);" _ & " SELECT [LoanID], [Prior LoanID], [SRP Rate], [SRP Amount] " _ & " FROM emailtable " _ & " WHERE [Seller Name:Refer to As] = [cboParam]"
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close