Mass emailing

[petern]

The client is using Office 2007. I have to set up a mass email facility for a client. I have done this in the past so that is not a particular problem. The situation is the following: Their systems person suggests that there be no more than 15 addresses per email in order for them not to have problems with ISP spam filters for bulk emails so I was planning just to loop through the set of addresses and put each set in a separate email. In another database, I just use docmd.sendobject which allows them to have all the addresses they filtered and then compose the message in Outlook with whatever formatting they want.

If there are 90 emails and I limit the addresses to 15 per email, then I will need to send 6 messages which means that the above method isn't practical. Finally I get to my question:

Is there a way that they can have a formatted message from somewhere, maybe Word, as the message that gets sent six times?

The alternative, as I see it is that the message is in an unbound textbox on an Access form and then used as the MessageText argument. I need to have the composition of the mail message, which will always be a new message, be no more complicated for the end user than opening Outlook and composing a message.

[JohnH]

I use the looping through the recordset method, but concatenate the addresses in the BCC field, until the loop counter reaches 15 (or whatever the relevant number is).
When I reach that number the message is sent, BCC is cleared, then keep working my way through the recordset.
Then send off whatever is left at the end.

So the message is in an Unbound text box, and only needs to be input once.

Some email systems object to the To address being blank, so I typically put my own address there. That way I get a copy of each message, which has advantages.

[petern]

Thanks John. That will be my fallback method. What I'm hoping for is some flexibility in the format of the message such as you get within Word or Outlook. An unbound textbox doesn't really allow for that. Or does it in 2007? And would that translate into VBA when the message is grabbed as a variable? I use 2003 for development.

[HansV]

Outlook VBA has two ways of populating the message text of an e-mail: the Body property of the MailItem object and the HTMLBody property.

The Body property is a plain string - no formatting.
The HTML body can be set to a string containing HTML tags such as <b>...</b> for bold etc.

Access 2007 has a richtext text box, but that is not much help - it works with RTF, so you'd have to convert the RTF tags to HTML tags - not an attractive proposition.

[petern]

Thanks Hans. That confirms my suspicions. They will have to go with plain text. If they want to go the route of using an attachment, is there a simple way of doing this from within a form/dialogue box in Access where the user would navigate to the attachment like they do in Outlook and the proper path would fill itself into the field which the VBA for the email could then pick up? I've never done this in Access.

[HansV]

You can use code like this:

Code: Select all

  Dim strFile As String
  With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Clear
    .Filters.Add "All files", "*.*"
    If .Show Then
      strFile = .SelectedItems(1)
    End If
  End With
  Me.txtAttachment = strFile

Here, a text box txtAttachment is filled, but you could as well use the variable strFile itself.

Note: the code requires a reference to the Microsoft Office n.0 Object Library, where n is the internal version number of your Office installation (10 = Office XP, 11 = Office 2003, 12 = Office 2007 and 14 = Office 2010)

[petern]

Just to confirm, would this code be the on click event of a button (cmdAttach for example) on my form?

[HansV]

I assumed that you already had code for creating and sending an e-mail to which you could add the fragment that I posted.

Does this mean you don't have any code at all?

[petern]

Yes I do have code for creating the email. Does this mean if I insert this code it will automatically open up the dialogue box? I'm not familiar with the .FileDialog object so that is where the confusion is coming from.

[HansV]

The FileDialog will display the standard Open dialog to let the user select a file.
You can insert the code more or less anywhere in the existing procedure before the point where you want to attach a file.
Do you create an object of type Outlook.MailItem in your code?

[JohnH]

Access 2007 has a richtext text box, but that is not much help - it works with RTF, so you'd have to convert the RTF tags to HTML tags - not an attractive proposition.

Access 2007 Rich Text is actually just HTML, so you can include it directly in an html formatted email.

[HansV]

Didn't know that - good to hear (although I have Access 2007 myself now, I don't use these new bells and whistles because my users have Access 2003)

[petern]

Thanks John for that info. As I have 2003 for development, I think I will stay away from that. Hans: I suppose I will have to use the Outlook.MailItem which I have used before. I looked at the object model for docmd.SendObject and it doesn't seem to allow for attachments. Would I need to install ClickYes to deal with annoying messages from Outlook, then?

[HansV]

Docmd.SendObject only lets you attach an object from the database, not an external file.

If you automate Outlook, you will run into prompts to allow access to the address book; ClickYes is the easiest way to suppress those.

[Mark L]

If they are planning on sending alot of e-mails (not necessarily all at once, but also frequently), perhaps your client should consider one of the web-based e-mail services (ConstantContact, etc.)? You can export an email list from your application and easily upload it to the service, compose the message on the website (with a little practice, you can really make them look good), and then send it without worrying about your ISP's spam filters.

[Carol W.]

I do a lot of work for a volunteer organization that also does a considerable amount of mass mailing. A few months ago, we ran into a lot of issues with our email provider's spam filters (or whatever the restrictions were). As a result, we purchased this program. The Business version interfaces with Access which is good because our membership list is in an Access app I wrote many years ago. The "broadcast messages" sent to our members now go through GroupMail. The program is a little buggy but I was able to work around the issues.

We also used it to send mass mailings to our Thrift Shop customers (a list we maintain within GroupMail) announcing the opening of our Holiday Shop and also several sale announcements.

So far, it has paid for itself based on increased sales on the days after the mass mailings were sent.

[petern]

Thanks Mark and Carol. The client is a social service agency and will be mainly using the email facility to target one-off groups (new immigrants in the last 6 months, women who speak arabic, etc) to advertise workshops and the like. They will be filtered through a filter form and each email list will be compiled on the fly. This is helpful info to tuck away, however.

[petern]

I have this working now. It is working fine except it is throwing off certain errors when the loop counter gets to 15 and starts the compile the next group. Specifically errors 13, 20, 3021 and 0. With the error trapping they don't seem to cause any problems in the final result, but I would be interested to know if it has something to do with my code (I can never figure out where to put rs.movenext ) or if this is actually normal behaviour. Also is is a good or bad idea to use .Quit at the end for Outlook. I have a suspicion that it might close previously open instances of the program and would like to confirm that.

Code: Select all

Private Sub cmdSend_Click()
Dim strfilter
Dim strsql As String
Dim I As Integer
Dim strEmail As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strFile As String
Dim strSubject As String
Dim strBody As String
Dim objOutlook  As New Outlook.Application  ' outlook object
Dim objMessage  As MailItem                 ' outlook mail message
Dim objOutlookAttach As Outlook.Attachment


On Error GoTo Err

strFile = ""
strSubject = Me.txtTitle
strBody = Me.TxtMessage


If MsgBox("Add an Attachment?", vbYesNoCancel, "Attachment") = vbYes Then

  With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Clear
    .Filters.Add "All files", "*.*"
    If .Show Then
      strFile = .SelectedItems(1)
    End If
  End With
  Me.txtAttach = strFile
End If


strfilter = [Forms]![frmFilterForm].Filter
Debug.Print strfilter
DoCmd.SetWarnings False
strsql = "DELETE * FROM tblFilterEmail"
DoCmd.RunSQL strsql

strsql = "INSERT INTO tblFilterEmail ( Email ) " & _
"SELECT DISTINCT tblFilter.Email FROM tblFilter WHERE (((tblFilter.Email) Is Not Null)) And "
strsql = strsql & strfilter
Debug.Print strsql
DoCmd.RunSQL strsql

DoCmd.SetWarnings True

I = 1
strEmail = ""
Set db = CurrentDb
Set rs = db.OpenRecordset("tblFilterEmail", dbOpenDynaset)
With rs
    rs.MoveLast
    rs.MoveFirst
    Debug.Print rs.RecordCount
    Do While Not rs.EOF
        Do Until I = 15
            strEmail = strEmail & rs![Email] & ";"
            I = I + 1
            rs.MoveNext

        Loop
         Set objMessage = objOutlook.CreateItem(olMailItem)
            With objMessage
                .BCC = strEmail
                .Subject = strSubject
                .Body = strBody
                .Save
        If Not strFile Or strFile <> "" Then
                Set objOutlookAttach = .Attachments.Add(strFile)
        End If
                .Send
            End With
        
        I = 1
        strEmail = ""
        
    Loop
    
End With

rs.Close
db.Close
objOutlook.Quit
set rs = Nothing
set db = Nothing
set objOutlook = Nothing

Err:
If Err.Number = 2051 Then
Exit Sub
Else
'Debug.Print Err.Number
'Debug.Print strEmail
Resume Next
End If

End Sub

[HansV]

Error 0 is because you always pass the error handler, I think, and error 3021 because you don't check for the end of the recordset in the I = 1 to 15 loop.

The version below should correct that, and it also checks whether Outlook was already active, and only quits it if not.

Code: Select all

Private Sub cmdSend_Click()
  Dim strfilter
  Dim strsql As String
  Dim I As Integer
  Dim strEmail As String
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim strFile As String
  Dim strSubject As String
  Dim strBody As String
  Dim objOutlook  As Outlook.Application  ' outlook object
  Dim objMessage  As MailItem             ' outlook mail message
  Dim objOutlookAttach As Outlook.Attachment
  Dim blnStartOutlook As Boolean

  On Error Resume Next

  Set objOutlook = GetObject(, "Outlook.Application")
  If objOutlook Is Nothing Then
      Set objOutlook = CreateObject("Outlook.Application")
      If objOutlook Is Nothing Then
          MsgBox "Could not start Outlook. Exiting...", vbExclamation
          Exit Sub
      End If
      blnStartOutlook = True
  End If

  On Error GoTo ErrHandler

  strFile = ""
  strSubject = Me.txtTitle
  strBody = Me.TxtMessage

  If MsgBox("Add an Attachment?", vbYesNoCancel, "Attachment") = vbYes Then
    With Application.FileDialog(msoFileDialogFilePicker)
      .Filters.Clear
      .Filters.Add "All files", "*.*"
      If .Show Then
        strFile = .SelectedItems(1)
      End If
    End With
    Me.txtAttach = strFile
  End If

  Set db = CurrentDb
  strfilter = [Forms]![frmFilterForm].Filter
  Debug.Print strfilter
  strsql = "DELETE * FROM tblFilterEmail"
  db.Execute strsql

  strsql = "INSERT INTO tblFilterEmail ( Email ) " & _
  "SELECT DISTINCT tblFilter.Email FROM tblFilter WHERE (((tblFilter.Email) Is Not Null)) And "
  strsql = strsql & strfilter
  Debug.Print strsql
  db.Execute strsql

  I = 1
  strEmail = ""
  Set rs = db.OpenRecordset("tblFilterEmail", dbOpenDynaset)
  With rs
    rs.MoveLast
    rs.MoveFirst
    Debug.Print rs.RecordCount
    Do While Not rs.EOF
        For I = 1 To 15
            strEmail = strEmail & rs![Email] & ";"
            I = I + 1
            rs.MoveNext
            If rs.EOF Then Exit For
        Next I
        Set objMessage = objOutlook.CreateItem(olMailItem)
        With objMessage
            .BCC = strEmail
            .Subject = strSubject
            .Body = strBody
            .Save
            If Not strFile Or strFile <> "" Then
                Set objOutlookAttach = .Attachments.Add(strFile)
            End If
            .Send
        End With
        strEmail = ""
    Loop
  End With

ExitHandler:
  On Error Resume Next
  If blnStartOutlook Then
      objOutlook.Quit
  End If
  Set objOutlook = Nothing
  rs.Close
  Set rs = Nothing
  Set db = Nothing

ErrHandler:
  If Err.Number = 2051 Then
    Resume ExitHandler
  Else
    'Debug.Print Err.Number
    'Debug.Print strEmail
    Resume Next
  End If
End Sub

[petern]

Hi Hans:

That works fine now. I had to remove the line I = I+1 leftover from my original code as your For Next loop dealt with that automatically. It still throws off error 13 at the end of each loop and error 0 at the very end, but the error handler deals with that so I'm not going to worry about it. More beer: