Hello Everybody,
Using Access 2003, I have a database that on_click, generates a report and opens up outlook, from which I manually populate the message address in outlook.
I have found some useful code that allows me to automatically email on_click, all senders whose address exists within a table from http://www.jephens.com/2007/05/13/ho...using-outlook/. which works perfectly. so now........I am trying to combine the two.
Code for sending reports as per command button wizard;
Code:
Private Sub ReportSend_Click()Dim stDocName As String
stDocName = "Breach"
DoCmd.SendObject acReport, stDocName
Exit_ReportSend_Click:
Exit Sub
The code for the second database resides in a module and is instigated as a macro running off a command button
Code:
Public Function SendEMail()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Set fso = New FileSystemObject
Subjectline$ = "Breach"
MyBodyText = "Please find report attached"
If 1 = 2 Then
BodyFile$ = "InputBox"
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
MyBodyText = MyBody.ReadAll
MyBody.Close
End If
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset("MyEmailAddresses")
Dim MyRecip As Outlook.Recipient
Do Until MailList.EOF
Set MyMail = MyOutlook.CreateItem(olMailItem)
Set MyRecip = MyMail.Recipients.Add(MailList("email"))
MyRecip.Type = olBCC
MyMail.Subject = Subjectline$
MyMail.Body = MyBodyText
'MyMail.Attachments.Add "c:myfile.txt", olByValue, 1, "My Displayname"
Dim MyQuery As QueryDef
Set MyQuery = CurrentDb.QueryDefs("ReportSource")
MyQuery.SQL = "select * from emails "
MyQuery.Close
MyMail.Send
MailList.MoveNext
Loop
Set MyMail = Nothing
MyOutlook.Quit
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function
Does anybody know if it is possible to incorporate the former code in the second passage to cause the email to contain the report? The original code shows where (as stated in green text', but how to I redirect this to the report? Any pointers very gratefully received.
Mattbro