Changing the way my Sendmail function sends e-mails

**slaterino** · 04-28-2009, 03:31 PM

Hi,
I have created a Sendmail function on one of my pages but am having some problems getting it to work the way I want. At the moment it opens a seperate e-mail for every e-mail address in my query. How can I change the script below so that it opens just one e-mail with all the e-mail addresses listed under BCC rather than several e-mails, each with just one recipient?

Code:

Public Sub SendMail()
'Provides the Send Mail automation
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSubject As String
Dim strEmailAddress As String
Dim strEmailAddress2 As String
Dim strEMailMsg As String
Dim ingCounter As Integer
Dim intCount As Integer

' Write messagebox content to a file
    
Set fso = CreateObject("Scripting.FileSystemObject")
Set theFile = fso.CreateTextFile("C:\Email.htm", vbTrue)


        theFile.WriteLine "<HTML>"
        theFile.WriteLine "<BODY style='font-family:Century Gothic;'>"
        theFile.WriteLine "<p></p>"
        theFile.WriteLine [Message]
        theFile.WriteLine "<br />"

' Append the user's signature file

      ' theFile.WriteLine fso.OpenTextFile("K:\Admin & office\logo & letterhead\email-signature.html").ReadAll
        theFile.WriteLine "</BODY>"
        theFile.WriteLine "</HTML>"
        theFile.Close
        
      
' Close the file which now contains the email body and signature and read the content into myHTML
        
        Set f = fso.OpenTextFile("C:\Email.htm", 1)
        MyHTML = f.ReadAll
        f.Close

strSubject = [Subject]
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryEmailOut")
Set rst2 = dbs.OpenRecordset("qryEmailOut2")

rst.MoveFirst
Do Until rst.EOF
    
    strEmailAddress = rst![email address]
    strEMailMsg = MyHTML

    'EMAIL USER DETAILS & ATT REPORT
    DoCmd.SendObject acSendNoObject, stDocName, acFormatRTF, , _
    , strEmailAddress, strSubject, strEMailMsg, , True
    
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

rst2.MoveFirst
Do Until rst2.EOF
    
    strEmailAddress2 = rst2![email address]
    strEMailMsg = MyHTML

    'EMAIL USER DETAILS & ATT REPORT
    DoCmd.SendObject acSendNoObject, stDocName, acFormatRTF, , _
    , strEmailAddress2, strSubject, strEMailMsg, , True
    
    rst2.MoveNext
Loop
rst2.Close
Set rst2 = Nothing

dbs.Close
Set dbs = Nothing
    
'Run update to update the sent mail check box
    DoCmd.SetWarnings False
    
    DoCmd.SetWarnings True
    
End Sub

**RuralGuy** · 04-29-2009, 12:40 PM

You need to create a function that will take a table or query name as input and return a concantenated string of all of the email addresses separated by a ";". So then your BCC argument would be:
GetEmailAddys("qryEmailOut") instead of what you have. I just made up the name of the function.

Changing the way my Sendmail function sends e-mails

Thread Tools

Changing the way my Sendmail function sends e-mails

Similar Threads

Changing A Report Recordsource from a form

Problems changing report design

Changing a date to the current year

Changing where clause in a query to run in a report

Changing the value of filed depending other field value

Posting Permissions