Mass emailing
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Mass emailing
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.
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.
Peter N
-
- 3StarLounger
- Posts: 287
- Joined: 09 Mar 2010, 23:16
- Location: Canberra Australia
Re: Mass emailing
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.
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.
Regards
John
John
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Mass emailing
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.
Peter N
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Mass emailing
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.
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Mass emailing
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.
Peter N
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Mass emailing
You can use code like this:
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)
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
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)
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Mass emailing
Just to confirm, would this code be the on click event of a button (cmdAttach for example) on my form?
Peter N
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Mass emailing
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?
Does this mean you don't have any code at all?
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Mass emailing
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.
Peter N
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Mass emailing
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?
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?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 287
- Joined: 09 Mar 2010, 23:16
- Location: Canberra Australia
Re: Mass emailing
HansV wrote: 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.
Regards
John
John
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Mass emailing
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)
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Mass emailing
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?
Peter N
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Mass emailing
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.
If you automate Outlook, you will run into prompts to allow access to the address book; ClickYes is the easiest way to suppress those.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 331
- Joined: 11 Feb 2010, 03:55
- Location: Land O Lakes, FL
Re: Mass emailing
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.
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
-
- 3StarLounger
- Posts: 390
- Joined: 26 Jan 2010, 16:02
- Location: Las Vegas, NV
Re: Mass emailing
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.
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.
Carol W.
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Mass emailing
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.
Peter N
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Mass emailing
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
Peter N
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Mass emailing
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.
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
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Mass emailing
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:
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:
Peter N