I have a table that holds my reports list tReports: [rptName],[eMailQry]
I use a form that has my reports list (listbox), once I select the report, it also shows the list of emails for that report. The query in [eMailQry].
The code below then scans each email and sends that report to them. (tho my 'reports' are queries, you can set yours to send snapshots, or pdfs)
Code:
'------------
Public Sub ScanAndEmail()
'------------
Dim vTo, vSubj, vBody, vRpt
Dim vFilePath
dim i as integer
For i = 0 To lstEAddrs.ListCount - 1
vRpt = lstEAddrs.ItemData(i)
lstEAddrs = vRpt
vTo = lstEAddrs.Column(2)
vBody = "body of email"
vSubj = vRpt
vFilePath = "" 'path of the snapshot or XL or PdF
Call Email1(vTo, vSubj, vBody, vFilePath)
Next
End Sub
'-------
'YOU MUST ADD THE OUTLOOK APP IN REFERENCES!!! checkmark OUTLOOK in the vbE menu, Tools, References
'-------
Public Function Email1(ByVal pvTo, ByVal pvSubj, ByVal pvBody,optional ByVal pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem
On Error GoTo ErrMail
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)
With oMail
.To = pvTo
.Subject = pvSubj
.Body = pvBody
If Not Ismissing(pvFile) Then .Attachments.Add pvFile, olByValue, 1
.Send
End With
EmailO = True
Set oMail = Nothing
Set oApp = Nothing
Exit Function
ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume Next
End Function