Unanswered: Trying to modify send mail function for a server workaround
I have created a send mail function in my database that works perfectly except for one problem which is due to the server. The e-mail is created within access, subjects, body, e-mail addresses and so on, and then the created e-mail is opened with the e-mail client. This allows for any modifications to the design of the e-mail, which we think is really useful.
Anyway, so we have one problem. That is that our server restricts any e-mail to a maximum of 100 recipients. On the database there are around 670 so this is a problem. How can the code I have published below be changed so that it would create a new e-mail for each 100 recipients? So in our case it would create 7 e-mails. 6 of which would have 100 recipients and 1 would have 70 recipients. Here is the code below. If anyone knows how this can be done I would be so grateful. I'm still learning access coding and I think this one is a little over my head!
Public Sub SendMail()
'Provides the Send Mail automation
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailAddress As String
Dim strEMailMsg As String
Dim ingCounter As Integer
Dim intCount As Integer
Set fso = CreateObject("Scripting.FileSystemObject")
Set theFile = fso.CreateTextFile("C:\Email.htm", vbTrue)
theFile.WriteLine "<BODY style='font-family:Century Gothic;'>"
theFile.WriteLine "<br />"
' theFile.WriteLine fso.OpenTextFile("K:\Admin & office\logo & letterhead\email-signature.html").ReadAll
Set f = fso.OpenTextFile("C:\Email.htm", 1)
MyHTML = f.ReadAll
strSubject = [Subject]
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryEmailOut")
Do Until rst.EOF
strEmailAddress = rst![email address]
strEMailMsg = MyHTML
DoCmd.SendObject acSendNoObject, stDocName, acFormatRTF, , _
, strEmailAddress, strSubject, strEMailMsg, , True
Set rst = Nothing
Set dbs = Nothing
I would be on the back of the IT department hourly to get them to lift that dumb restriction.
Having said that, if you can't/they won't then the way I would go about it would be to create groups and assign recipents to those groups. Then your e-mailer can simply loop through the groups and spam erm... e-mail them group by group.
No, you'd have to build it as part of your design. Each group (record in the group table) would have to manually have no more than 100 recipients given to it... by way of the related subform of recipients. Send your bill to the IT department!!