Code:
Dim appOutLook As Outlook.Application, mailOutLook As Outlook.MailItem, rs As DAO.Recordset, objApp As Object, strZip As String
Set appOutLook = CreateObject("Outlook.Application")
Set mailOutLook = appOutLook.CreateItem(olMailItem)
Set rs = CurrentDb.OpenRecordset ("SELECT SubNo FROM tablename WHERE OrderDate = Date();")
Set objApp = CreateObject("Shell.Application")
'create empty zip folder
'found this on web, no idea what the Print line does but if it isn't there, this won't work
strZip = "folderpath\filename.zip"
Open strZip For Output As #1
Print #1, "PK" & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
With mailOutLook
Do While Not rs.EOF
'double parens not in original example code but won't work without
objApp.NameSpace((strZip)).CopyHere "file folder path\" & rs!SubNo & ".filename extension here" 'copy file into zip folder
rs.MoveNext
Wend
.Attachments.Add strZip
.To = "email address here"
.Subject = "subject text here"
.HTMLBody = "message body here"
.Display
End With