Yes, there is! I can't recall now where I got this from (could have been this
forum or a text book!) but below is some code that I use to set up the
recipient, subject and then the text of e-mails and before sending them. (It
is not the complete code as this would be a bit long but I hope that this
helps.)
I am not sure whether the on click event is what you really want to use -
that's OK if you want to do each person individually, but if not, why don't
you create a recordset that contains all of the people who have not paid
their dues, then you can loop through the records and send an e-mail
automatically to each one. Just in case it is of any use, the code below does
something similar - but as I said, it isn't all there as I have omitted some
of the code that is very specific to my application (in this case it is to
remond people to send in their expenses). Hope I haven't cut out any
important bits!!!!!
Private Sub SubmitMonthlyExpenseReminder_Click()
Dim mySQL As String
Dim appOutlook As Outlook.Application
Dim appOutlookMsg As Outlook.MailItem
Dim appOutlookRecip As Outlook.Recipient
Dim strMsgBody As String
Dim strRecipient As String
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRS As New ADODB.Recordset
mySQL = "SELECT statement to get your data"
myRS.Open mySQL, cnn1, adOpenForwardOnly, adLockReadOnly
If myRS.EOF = True And myRS.BOF = True Then
MsgBox "There are no jobs waiting for expense details"
GoTo Error:
End If
Do While myRS.EOF = False
' put code in here to set any other variables etc. you may need. You can
pick up fields from the SELECT statement by using myRS.Fields(0) thru
myRS.Fields(n) based upon sequence is statement
Set appOutlook = CreateObject("Outlook.Application")
Set appOutlookMsg = appOutlook.CreateItem(olMailItem)
With appOutlookMsg
Set appOutlookRecip = .Recipients.Add(strRecipient)
appOutlookRecip.Type = olTo
.BCC = "email if you want to bcc anyone - perhaps yourself"
.Subject = "Subject text is here"
.Importance = olImportanceHigh
' Above is set to imortance high - you may want this if you are chasing money!
strMsgBody = "Dear " & myRS.Fields(8) & "," & vbCrLf & "Please
find listed below details of jobs you have undertaken and for which we
require your expenses." & vbCrLf
' Above code you would change for your particular circumstances
appOutlookMsg.Body = strMsgBody & vbCrLf & vbCrLf & "Please
provide the following information for each job within the next 4 working
days;" & vbCrLf & vbCrLf & "Many thanks"
.Send
Set appOutlook = Nothing
Set appOutlookMsg = Nothing
Set appOutlookRecip = Nothing
myRS.MoveNext
Loop
Finish:
myRS.Close
cnn1.Close
Error:
End Sub
