Copy query results into email w/ bullet points (1 Viewer)

jmt90404

Registered User.
Local time
Today, 11:28
Joined
Mar 24, 2018
Messages
29
Novice here. Via VBA I am trying to copy the results of a query into an email, with each result it’s one bullet point, broken down into catagories. I basically have 2 relevant fields in the query. A category field and a notes field. The email should look like this:

Category 1:
  • Note 1
  • Note 2

Catagory 2:
  • Note 3
  • Note 4

I hope this is clear. Appreciate any help on the issue!
 

bastanu

AWF VIP
Local time
Today, 08:28
Joined
Apr 13, 2010
Messages
1,401
Create a report grouping by category and email that.
Cheers,
Vlad
 

jmt90404

Registered User.
Local time
Today, 11:28
Joined
Mar 24, 2018
Messages
29
Needs to be in the body of the email, not an attachment.
 

Mark_

Longboard on the internet
Local time
Today, 08:28
Joined
Sep 12, 2017
Messages
2,111
How are you currently sending the Email? What format are you sending the Email in?
 

jmt90404

Registered User.
Local time
Today, 11:28
Joined
Mar 24, 2018
Messages
29
I’m not sure I fully understand your question. I’m sending it using outlook via VBA from access. I basically just want to push a button in access and an email is generated without having to make any additions or changes. Please excuse my ignorance. I’m still really new at this.
 

Mark_

Longboard on the internet
Local time
Today, 08:28
Joined
Sep 12, 2017
Messages
2,111
In general, you will want to create the body of your Email by looping through the records in your query and appending to a string variable each formatted line.

If you are doing this in HTML, you would start by adding the header information to your string FIRST, then doing the loop, then adding the ending information LAST.

Please post the current code you are using to send the Email so we can give more specific advice.
 

jmt90404

Registered User.
Local time
Today, 11:28
Joined
Mar 24, 2018
Messages
29
The bones for my code are below. Of course I'm missing just about everything needed for it to work. Some of the info I am using in the email (address & name) are in the main form (which is also in a navigation form), where as the info that I need to put in the category sections and bullet points are in a query and also in the sub form that this code is being activated from. Again, sorry if I'm not being clear.

Code:
Dim objOutlook As Outlook.Application
Dim objEmailItem As MailItem
Dim strAddress As String
Dim strClient As String

strAddress = Me.Address & ", " & Me.City
strClient = Me.sfrmClients.Form.FirstName & " " & Me.sfrmClients.Form.LastName


' Prevent 429 Error if outlook is not open
On Error Resume Next
Err.Clear
Set objOutlook = GetObject(, "Outlook.application")
If Err.Number <> 0 Then
Set objOutlook = New Outlook.Application
End If

Set objEmailItem = Outlook.CreateItem(olMailItem)
With objEmailItem
.Subject = "Pending Conditions/Requirements: " & strAddress & " - " & strClient
.To = "email address to be added"
.HTMLBody = "<body style='font-size:11.0pt'><b>Pending Conditions on " & strAddress_ & ":</b><br><br>" _
    & "category 1:</br><ul><li>" & 1st bullet point"</li><li>" & 2nd bullet point & "  _
    & "</li><li> " & 3rd bullet point "</li></ul></body>"
.Display
End With

Set objEmailItem = Nothing
Set objOutlook = Nothing
 
Last edited:

Mark_

Longboard on the internet
Local time
Today, 08:28
Joined
Sep 12, 2017
Messages
2,111
Very quick rundown of what you want to do;
Add two variables, one to hold your "Current Category" and one to hold your HTML.
PRIOR to assigning .HTMLBody, you will have something that does
1) Opens your query of Notes,
2) Begins looping through your notes.
If your saved category (starts blank) doesn't match you current records category
a) Add to your HTML variable your category information
b) Save the current category as your save category
3) Add a formatted line for the current record to your HTML variable
WHEN you hit the last record in your query, you are done.
4) Once done looping through your query, set your .HTMLBody = your save HTML

If you have issues with coding any of that, please let us know which piece(s) you will need help with. This is fairly old school coding for reporting but it does work very well for situation like what you are doing.
 

jmt90404

Registered User.
Local time
Today, 11:28
Joined
Mar 24, 2018
Messages
29
I'm not sure I understood the directions above but I did my best. This is what I have. Its an quite the mess. I'm sure there is a much simpler and better way to do what I have tried to do. It seemed to work in my test database, but when I put it in action it no longer worked and would only display the last 2 categories in the email, whether they had entries or not.

Code:
Private Sub btnConditions_Click()

Dim objOutlook As Outlook.Application
Dim objEmailItem As MailItem
Dim strAddress As String
Dim strClient As String
Dim strHTML As String
Dim rst As Recordset
Dim strQuery As String
Dim val As String

val = [Forms]![frmNavigation]![NavigationSubform]![DealID]

strQuery = "SELECT tblConditions.Stage, tblConditions.Condition " _
    & "FROM tblClients INNER JOIN (tblConditions RIGHT JOIN tblDeals ON tblConditions.DealIDFK = tblDeals.DealID) ON tblClients.ClientID = tblDeals.ClientIDFK " _
    & "WHERE tblDeals.DealID = " & val & " AND tblConditions.Status ='Pending';"

Set rst = CurrentDb.OpenRecordset(strQuery)

On Error Resume Next

If rst.Fields("Stage") = "LOI" Then
    strHTML = "<u>LOI:</u><br><ul>"
    rst.MoveLast
    rst.MoveFirst
    
    Do Until rst.EOF
        If rst.Fields("Stage") = "LOI" Then
            strHTML = strHTML & "<li>" & rst.Fields("Condition") & "</li>"
        End If

        rst.MoveNext
    Loop

    strHTML = strHTML & "</ul>"
End If

If rst.Fields("Stage") = "Rate Lock" Then
    strHTML = strHTML & "<u>Rate Lock:</u><br><ul>"
    rst.MoveLast
    rst.MoveFirst

    Do Until rst.EOF
        If rst.Fields("Stage") = "Rate Lock" Then
            strHTML = strHTML & "<li>" & rst.Fields("Condition") & "</li>"
        End If

        rst.MoveNext
    Loop

    strHTML = strHTML & "</ul>"
End If

If rst.Fields("Stage") = "Appraisal" Then
    strHTML = strHTML & "<u>Appraisal:</u><br><ul>"
    rst.MoveLast
    rst.MoveFirst

    Do Until rst.EOF
        If rst.Fields("Stage") = "Appraisal" Then
            strHTML = strHTML & "<li>" & rst.Fields("Condition") & "</li>"
        End If

        rst.MoveNext
    Loop

    strHTML = strHTML & "</ul>"
End If
      
If rst.Fields("Stage") = "Appraisal Requirement" Then
    strHTML = strHTML & "<u>Appraisal Requirement:</u><br><ul>"
    rst.MoveLast
    rst.MoveFirst

    Do Until rst.EOF
        If rst.Fields("Stage") = "Appraisal Requirement" Then
            strHTML = strHTML & "<li>" & rst.Fields("Condition") & "</li>"
        End If

        rst.MoveNext
    Loop

    strHTML = strHTML & "</ul>"
End If
      
If rst.Fields("Stage") = "Final UW" Then
    strHTML = strHTML & "<u>Final UW:</u><br><ul>"
    rst.MoveLast
    rst.MoveFirst

    Do Until rst.EOF
        If rst.Fields("Stage") = "Final UW" Then
            strHTML = strHTML & "<li>" & rst.Fields("Condition") & "</li>"
        End If

        rst.MoveNext
    Loop

    strHTML = strHTML & "</ul>"
End If

If rst.Fields("Stage") = "Loan Doc" Then
    strHTML = strHTML & "<u>Loan Doc:</u><br><ul>"
    rst.MoveLast
    rst.MoveFirst

    Do Until rst.EOF
        If rst.Fields("Stage") = "Loan Coc" Then
            strHTML = strHTML & "<li>" & rst.Fields("Condition") & "</li>"
        End If

        rst.MoveNext
    Loop

    strHTML = strHTML & "</ul>"
End If

If rst.Fields("Stage") = "Close" Then
    strHTML = strHTML & "<u>Close:</u><br><ul>"
    rst.MoveLast
    rst.MoveFirst

    Do Until rst.EOF
        If rst.Fields("Stage") = "Close" Then
            strHTML = strHTML & "<li>" & rst.Fields("Condition") & "</li>"
        End If

        rst.MoveNext
    Loop

    strHTML = strHTML & "</ul>"
End If
      
strAddress = DFirst("Address", "qryConditions")
strClient = DFirst("Name", "qryConditions")


' Prevent 429 Error if outlook is not open
Err.Clear
Set objOutlook = GetObject(, "Outlook.application")
If Err.Number <> 0 Then
Set objOutlook = New Outlook.Application
End If

Set objEmailItem = Outlook.CreateItem(olMailItem)
With objEmailItem
    .Subject = "Pending Conditions/Requirements: " & strAddress & " - " & strClient
    .To = "email address"
    .HTMLBody = "<body style='font-size:11.0pt'><i>Pending Conditions on " & strAddress & ":</i><br><br>" _
        & strHTML
    .Display
End With

Set objEmailItem = Nothing
Set objOutlook = Nothing

End Sub
 

Cronk

Registered User.
Local time
Tomorrow, 02:28
Joined
Jul 4, 2013
Messages
2,770
When you open your recordset, it is not in any particular order.


Assume the value of the field Stage for the first record is "Closed", the first If statement tests for the value "LOI" and will be false. Same for all them until the last one. So only conditions for "Closed" will be completed.


Yes, your code could be made more efficient but the easy fix is to order the recordset ie include ORDER BY Stage in strQuery


Then
Code:
rst.findfirst "Stage='LOI'"
if not rst.noMatch then   

   ' there are some records
   strHTML = "<u>LOI:</u><br><ul>"

   do while rst!Stage ="LOI"
       strHTML = strHTML & "<li>" & rst.Fields("Condition") & "</li>"
       rst.MoveNext
   loop
endif
Repeat above for the rest of your Stage values
 

Mark_

Longboard on the internet
Local time
Today, 08:28
Joined
Sep 12, 2017
Messages
2,111
Honestly looks like too much work to me.

Code:
Dim strLast as STRING 'Add in for the next part.
    
Do Until rst.EOF
   If rst.Fields("Stage") <> strLast Then
      strLast = rst.Fields("State")
      strHTML = strHTML & "<u>" & strLast & ":</u><br><ul>"
   End If
   strHTML = strHTML & "<li>" & rst.Fields("Condition") & "</li>"
    rst.MoveNext
Loop
strHTML = strHTML & "</ul>"
That makes your entire body.

You do ONE loop through the query, but you make sure it is ordered by your Stage.
 

Cronk

Registered User.
Local time
Tomorrow, 02:28
Joined
Jul 4, 2013
Messages
2,770
That's fine unless the OP wants the output in the order in his code.
 

Mark_

Longboard on the internet
Local time
Today, 08:28
Joined
Sep 12, 2017
Messages
2,111
If he wants custom orders, then he could have join in a "Sequence" table that would have both the "Category" and an "Sequence", so he can match category to category and then order by the Sequence. Having one small table that returns which category is first, which is second and such is almost always better than hard coded values for reporting. Avoids problems when a new value is entered.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:28
Joined
Sep 21, 2011
Messages
14,044
Mark_

Typo?
Just mentioning, as new users appear to copy as is.?

Code:
Dim strLast as STRING 'Add in for the next part.
    
Do Until rst.EOF
   If rst.Fields("Stage") <> strLast Then
      strLast = rst.Fields("Sta[COLOR="Red"]g[/COLOR]e")
      strHTML = strHTML & "<u>" & strLast & ":</u><br><ul>"
   End If
   strHTML = strHTML & "<li>" & rst.Fields("Condition") & "</li>"
    rst.MoveNext
Loop
strHTML = strHTML & "</ul>"
 

jmt90404

Registered User.
Local time
Today, 11:28
Joined
Mar 24, 2018
Messages
29
Thanks very much for the help. Code works perfectly and is so much simpler. Instead of joining a sequence table I created a sort column in the existing query, mostly because I had already done it, but in the future it sounds like the sequence table may be the better the option and I'll certainly implement that in the future. But for now I just wanted to get the code working. There was one issue with the code where each subsequent category after the first was indented and the bullet point was set to a lower level then the previous category, but I simply added a </ul> to the code and it seemed to fix everything. I've posted the code below. Thanks again everyone for the help.

Code:
Dim objOutlook As Outlook.Application
Dim objEmailItem As MailItem
Dim strAddress As String
Dim strClient As String
Dim strHTML As String
Dim rst As Recordset
Dim strQuery As String
Dim val As String
Dim strLast As String

val = [Forms]![frmNavigation]![NavigationSubform]![DealID]

strQuery = "SELECT tblConditions.Stage, tblConditions.Condition, IIf([tblConditions.Stage]='LOI',1," _
    & "IIf([tblConditions.Stage]='Rate Lock',2,IIf([tblConditions.Stage]='Appraisal',3,IIf([tblConditions.Stage]='Appraisal Requirement',4," _
    & "IIf([tblConditions.Stage]='Final UW',5,IIf([tblConditions.Stage]='Loan Doc',6,IIf([tblConditions.Stage]='Close',7))))))) AS Sort " _
    & "FROM tblConditions RIGHT JOIN tblDeals ON tblConditions.DealIDFK = tblDeals.DealID " _
    & "WHERE (((tblDeals.DealID) = " & val & ") And ((tblConditions.Status) = 'Pending'))" _
    & "ORDER BY IIf([tblConditions.Stage]='LOI',1,IIf([tblConditions.Stage]='Rate Lock',2,IIf([tblConditions.Stage]='Appraisal',3," _
    & "IIf([tblConditions.Stage]='Appraisal Requirement',4,IIf([tblConditions.Stage]='Final UW',5,IIf([tblConditions.Stage]='Loan Doc',6,IIf([tblConditions.Stage]='Close',7)))))));"

Set rst = CurrentDb.OpenRecordset(strQuery)

On Error Resume Next

Do Until rst.EOF
   If rst.Fields("Stage") <> strLast Then
      strLast = rst.Fields("Stage")
      strHTML = strHTML & "[COLOR=Red]</ul>[/COLOR]<u>" & strLast & ":</u><br><ul>"
   End If
   strHTML = strHTML & "<li>" & rst.Fields("Condition") & "</li>"
rst.MoveNext
Loop
strHTML = strHTML & "</ul>"

strAddress = DFirst("Address", "qryConditions")
strClient = DFirst("Name", "qryConditions")


' Prevent 429 Error if outlook is not open
Err.Clear
Set objOutlook = GetObject(, "Outlook.application")
If Err.Number <> 0 Then
Set objOutlook = New Outlook.Application
End If

Set objEmailItem = Outlook.CreateItem(olMailItem)
With objEmailItem
    .Subject = "Pending Conditions/Requirements: " & strAddress & " - " & strClient
    .To = "email address"
    .HTMLBody = "<body style='font-size:11.0pt'><i>Pending Conditions on " & strAddress & ":</i><br><br>" _
        & strHTML
    .Display
End With

Set objEmailItem = Nothing
Set objOutlook = Nothing
 

Users who are viewing this thread

Top Bottom