Email ONLY to current subform records

ande8150

New member
Local time
Today, 08:12
Joined
Jun 9, 2006
Messages
5
I have been reading quite a bit on this and other forums about email automation and looping queries to gather email addresses. However, I am unable to restrict my list of email addresses to only that are currently being viewed in a subform.

An image of my form is attached. I have 3 tables being used:
  • Events
  • Contacts
  • EventAttendance

The EventAttendance Table is a join table to determine which Contacts attended which Events.

When I try to loop a query to capture email addresses, I am only able to return email addresses for the entire query (all people attending all events) not just those that are listed in the subform for the current event.

How do I adjust the code below to only use the emails that match the EventID I am viewing?

Code:
Private Sub Command19_Click()

Dim rs As New ADODB.Recordset
Dim strEmail As String

rs.Open "EventAttendance Query", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

strEmail = ""
Do While Not rs.EOF
strEmail = strEmail & rs!Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject , , , strEmail, , , "test", "Test", True

rs.Close
Set rs = Nothing
        

End Sub
 

Attachments

  • screencap.jpg
    screencap.jpg
    88.6 KB · Views: 189
Since you have already produced the list, using the query and linked fields, you can work on the sub form recordset clone directly: -

Code:
Option Explicit
Option Compare Text


Private Sub cmdSendEmails_Click()
    Dim strEmail As String
    
    With Me.ctlEventAttendanceQuerySubform.Form.RecordsetClone
        If (.RecordCount) Then
            .MoveFirst
            Do Until .EOF
                If Len(!Email) Then
                    strEmail = strEmail & !Email & ";"
                End If
            .MoveNext
            Loop
            If Len(strEmail) Then
                DoCmd.SendObject , , , strEmail, , , "test", "Test", True
            Else
                MsgBox "No Emails to send.", vbInformation
            End If
        Else
            MsgBox "No Records in Sub Form.", vbInformation
        End If
    End With
    
End Sub
That also requires no references set so should be OK in any version of Access.
(Only tested in A97 and A2K using Outlook Express. Please change control names as required.)

Hope that helps.

Regards,
Chris.
 
Thanks Chris, your code works perfectly!! You have ended many hours of trial & error!!
 
You’re welcome but was just wondering if you were using Outlook and does it work OK with both Outlook Express and Outlook.
 
I am using Outlook 2002. Unfortunately I am unable to test it with Outlook Express.
 
Thanks for the reply.

I have tested it with Outlook Express so it is OK with both.

Regards,
Chris.
 
Chris,

The code works fine in both Outlook and Express.

When I make Outlook (2003)my default and change,
Code:
DoCmd.SendObject , , , strEmail, , , "test", "Test", [COLOR="Blue"]True[/COLOR]
to:
Code:
DoCmd.SendObject , , , strEmail, , , "test", "Test",[COLOR="Blue"] False[/COLOR]
you will have the Outlook security warning appear.

If you use Express with "False" and change the security settings to remove the warning then the e-mail will be sent with only a flash of the "send" dialog box.

I would also change the strEMail to the Bcc field and put the senders e-mail address in the To. This way you will receive an email confirming that the email was sent, and each recipient will not see who else received the email.
 
G’day John.

Good points and I’ll rework it along those lines. I particularly like the idea of not showing all recipients the full list.

Thanks and regards,
Chris.
 
A small rewrite: -

Behind the form…
Code:
Option Explicit
Option Compare Text


Private Sub cmdSendEmails_Click()
    Dim strSubject  As String
    Dim strMessage  As String
    Dim blnEditMode As Boolean
    
    [color=green]'   Optional arguments.[/color]
    strSubject = "The quick brown fox jumps over the lazy dog."
    strMessage = "Who's Afraid of Virginia Woolf?"
    blnEditMode = True
    
    SendEmails Me.ctlEventAttendanceQuerySubform.Form, strSubject, strMessage, blnEditMode
    
End Sub

In a standard module…
Code:
Option Explicit
Option Compare Text


[color=green]'   If unwanted messages arise due to sending emails
'   try a product called ClickYes...not tested by me.
'   Google for -> ClickYes.[/color]
Public Sub SendEmails(ByRef frmForm As Form, _
                   Optional strSubject As String = "Test Subject", _
                   Optional strMessage As String = "Test Message", _
                   Optional blnEditMode As Boolean = False)
                      
    Dim strBCC As String
      
    With frmForm.RecordsetClone
        If (.RecordCount) Then
            .MoveFirst
            Do Until .EOF
                If Len(!Email) Then
                    strBCC = strBCC & !Email & ";"
                End If
            .MoveNext
            Loop
            
            If Len(strBCC) Then
                [color=green]'                                 Set to your email address.[/color]
                DoCmd.SendObject , , acFormatRTF, "who@where.something", , strBCC, strSubject, strMessage, blnEditMode
            Else
                MsgBox "No Emails to send.", vbInformation
            End If
        Else
            MsgBox "No Records in Sub Form.", vbInformation
        End If
    End With
    
End Sub

There seems to be too many permutations to cover all bases so I’ll leave it at that but contributions would certainly be welcome.

Thanks again John and regards,
Chris.
 
Alternative email format for HTML

Hil all,

Here is what I have worked up as an alternative which uses HTML format. This allows a formatted email to be sent. I have also included some fields from my form to automatically fill in details. Not sure if it works with Outlook Express.

Code:
Private Sub Command19_Click()

    Dim strEmail As String
    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    Dim strToWhom As String

    With Me.[I]YourSubformNameGoesHere[/I].Form.RecordsetClone
        If (.RecordCount) Then
            .MoveFirst
            Do Until .EOF
                If Len(!Email) Then
                    strEmail = strEmail & ![I]YourEmailField [/I]& ";"
                End If
            .MoveNext
            Loop
            If Len(strEmail) Then
                  'Create e-mail item
                Set objMail = olApp.CreateItem(olMailItem)
                With objMail
                'Set body format to HTML
                .To = "[I]youremail@yourdomain.com"[/I]
                .BCC = strEmail
                .Subject = [I]"Event Reminder: " & Me.EventName[/I]
                .BodyFormat = olFormatHTML
                .HTMLBody = [I]"<HTML><BODY>This email is a reminder of your registration for the event listed below.  If you have any questions about the event, or are unable to attend, please advise us by responding to this email.<br><br> <b>Event: </b>" & Me.EventName & "<br><b>Event Description: </b>" & Me.EventDescription & "<br><b>Start Time:</b> " & Me.EventStartTime & " <b>End Time: </b>" & Me.EventEndTime & "<br><b>Event Location: </b>" & EventLocation & "<br><br>We look forward to seeing you at the event.<br><br>Your Company Name<br>youremail@yourdomain.com</BODY></HTML>"[/I]
                .Display
                End With
                End If
            Else
                MsgBox "No Emails to send.", vbInformation
            End If
    End With
    
End Sub
 
I haven’t tested it but don’t think it would work with Outlook Express.

The reason for using DoCmd.SendObject, as in your original post was to, in effect, late bind the email software whichever was the default. That much works so far.

Now if we go to defining an object as an Outlook.Application then we are early binding to Outlook software which should exclude Outlook Express. That, to me, is what SendObject avoids. It late binds to the machine default email software.

The Ritz will hit the fan with the SendObject method when trying to add an attachment.

But the original question was to produce a recipient list and that, with the help of John’s advice, has been done (I think.). The original question used SendObject not Outlook specific software.

So, it seems to me, that the question is being refined from the very general SendObject method of your first post to your now specific need while using Outlook.

So, is there a requirement, specific to your needs?

Regards,
Chris.
 
But the original question was to produce a recipient list and that, with the help of John’s advice, has been done (I think.).

Absolutely. My original post was to find out how to create the recipient list, and that works great. I just added the additional post to show it working with an HTML format for future visitors. I don't have any further issues at this point.

Just trying to give back to those who helped me out!!

Thanks again everyone.
 

Users who are viewing this thread

Back
Top Bottom