G'day
I did not write this code it was written by a friend of mine, we have
been working together on the database.
It is a sailing club membership annual invoice. Each member can have 2
email addresses which is the bit in the middle.
It works by printing a report based on a query to html. We did not
spend much time with the report but one thing to remember is that html
does not like lines - if you want a line in your report make it an
underline in a text box.
have fun!
Gus
Sub EmailInvoiceRoutine()
'-----------------------------------------------------------------------
-----------
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim vEmail1 As String
Dim vEmail2 As String
Dim vCode As String
Dim vMailMess As String
Dim vDocName As String
Dim vCheck As String
On Error GoTo Err_EmailInvoiceRoutine
Set db = CurrentDb
Set rst = db.OpenRecordset("tblmemberunits")
Set rst2 = db.OpenRecordset("tblInvoiceDataSourceHTML")
vDocName = "rptInvoiceFirstHTML" ' name of the report to print out
or emial
DoCmd.SetWarnings False
DoCmd.Echo False
rst.MoveFirst
rst2.MoveFirst
'-----------------------------------------------------------------------
-----------
vCheck = MsgBox("This will email invoices to all members who have an
email address on the database" & Chr$(13) & _
"If you do not want to send these immediately please ensure you are
NOT connected to the Interent at this time." & Chr$(13) & Chr$(13) & _
"For those members without Email Addresses, a printout of their
Invoice will be printed to your printer" & Chr$(13) & Chr$(13) & _
"Do you wish to continue ?", vbYesNo, "NSC Invoice Send Routine")
If vCheck = vbNo Then Exit Sub
'-----------------------------------------------------------------------
-----------
' Get Email Message else use default
vMailMess = InputBox("What Subject do you want to appear on your
Email?" & Chr$(13) & _
"Default will be NSC Invoice", "Mail Message", "NSC
Invoice")
'MsgBox vMailMess
'-----------------------------------------------------------------------
-----------
Do Until rst.EOF
' Assign vcode to variable
vCode = rst!code
'MsgBox vCode
' Update temp table for HTML query for code
rst2.Edit
rst2!code = vCode
rst2.Update
'MsgBox rst2!code
' Check Email Address. If null then do not send out.
If IsNull(rst!Email) Then vEmail1 = "None" Else vEmail1 =
rst!Email
If IsNull(rst!Email2) Then vEmail2 = "None" Else vEmail2 =
rst!Email2
'MsgBox vEmail1 & vEmail2
If vEmail1 <> "None" And vEmail2 <> "None" Then
'MsgBox "Two email address"
DoCmd.SendObject acSendReport, "rptInvoiceFirstHTML",
acFormatHTML, _
vEmail1 & ";" & vEmail2, , , vMailMess, , False
ElseIf vEmail2 = "None" And vEmail1 <> "None" Then
'MsgBox "Primary email address only"
DoCmd.SendObject acSendReport, "rptInvoiceFirstHTML",
acFormatHTML, _
vEmail1, , , vMailMess, , False
ElseIf vEmail1 = "None" And vEmail2 <> "None" Then
'MsgBox "Secondary email address only"
DoCmd.SendObject acSendReport, "rptInvoiceFirstHTML",
acFormatHTML, _
vEmail2, , , vMailMess, , False
Else
'MsgBox "No email address"
DoCmd.OpenReport "rptInvoiceFirstHTML", acPreview
End If
rst.MoveNext
Loop
DoCmd.SetWarnings True
DoCmd.Echo True
MsgBox "All Invoices have been processed!"
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!