Need help VBA Email Loop

[leviathanxx]

Hello everyone. As I am still learning vba please bare with me. I currently have to send out an email to individual parties with a listing of all their current items. So for example;

Customer - Item

Joe - Car
Joe - Ipad
Joe - Phone
Jake - Chainsaw
John - Ipad
John - Phone


What I would like to have is an email sent to each party listing their items. Aka their name is the distinct field and the item will be listed by row per each distinct filtered. For the above example their should be 3 emails generated in total for Joe, Jake, and John listing their items.

I managed to get most of the coding, however, I cannot seem to figure out why the code will cumulatively add items and not separate them by person. So when i run this report it exports the query to an .html (need the items listed in the body not as an attachment), then opens the export and write html to the body of the email.

It baffles me because it will generate three exported files with the persons name and the html matches the query if it were filtered by the person. However, when outlook generates the emails, it adds each filter on top of eachother. Any help would greatly be appreciated as I am so close to finishing this.

Private Sub Command0_Click()

Dim MyDB As DAO.Database, RS As DAO.Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long
DoCmd.RunCommand acCmdSaveRecord
Set MyDB = DBEngine.Workspaces(0).Databases(0)

Set RS = MyDB.OpenRecordset _
("SELECT DISTINCT Field1 FROM table1")

lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF

'Changes the forms unbounded box, the query uses this to filter
Forms![Form1]![cmbfilter] = RS!Field1
Set subjecttest = RS!Field1

'Beginning of email code
Dim strline, strHTML

Dim OL As Outlook.Application
Dim MyItem As Outlook.MailItem

Set OL = New Outlook.Application
Set MyItem = Outlook.Application.CreateItem(olMailItem)

DoCmd.OutputTo acOutputQuery, "table1 Query", acFormatHTML, "C:\temp\" & RS!Field1 & ".html"

Open "C:\temp\" & RS!Field1 & ".html" For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If
If IsNull(RS!Field1) Then
Else
MyItem.Subject = subjecttest
End If
MyItem.HTMLBody = "TEST" & strHTML
MyItem.Save


RS.MoveNext
Loop
End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close
MsgBox "Done sending Promo email. ", vbInformation, "Done"
Exit Sub
End Sub

 

[Gasman]

I'm not sure if the DIM statement clears the variable on each run.

I tend to Dim my variables at the top of the code

I would walk though the code in the debugger.

You do not clear strHTML in the loop, so I would expect it to grow in size on each email?

Edit:
Just did a quick test and Dim does not clear the variable, so you will need to set strHTML to "" for each pass in the loop

Also it really helps if you post code in the code snippet and indent the code.

 

[zpy2]

probably, some thing wrong with your query.
can you show the html which the query generate and the sql statement as well？

 

[pbaldy]

I think Gasman has already identified the problem, but I'm curious about the whole bit exporting to a file and back. Is any HTML formatting added, or is it just a list? You could just add the items to a string variable, either separated by a comma or on different lines, and avoid the external file. In other words either

Car, Ipad, Phone

or

Car
Ipad
Phone