Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    59

    Unanswered: How to add 2 objects to a button

    Hi,
    I have a mail function which I want to run two times, so that I end up with 2 new e-mails open in my e-mail client. The functions will use 2 different queries but apart from that will be exactly the same. How can I run both of these from the same button?

    Here is the code below. Do I just need to copy and paste it, and if so, do I then just need to change the name of the variables. If so, which variables do I need to change? dbs, rst, strSubject, etc.?

    Code:
    Public Sub SendMail()
    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 "<HTML>"
            theFile.WriteLine "<BODY style='font-family:Century Gothic;'>"
            theFile.WriteLine "<p></p>"
            theFile.WriteLine [Message]
            theFile.WriteLine "<br />"
    
          ' theFile.WriteLine fso.OpenTextFile("K:\Admin & office\logo & letterhead\email-signature.html").ReadAll
            theFile.WriteLine "</BODY>"
            theFile.WriteLine "</HTML>"
            theFile.Close
            
            Set f = fso.OpenTextFile("C:\Email.htm", 1)
            MyHTML = f.ReadAll
            f.Close
    
    strSubject = [Subject]
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryEmailOut")
    
    rst.MoveFirst
    Do Until rst.EOF
        
        strEmailAddress = rst![email address]
        strEMailMsg = MyHTML
    
        DoCmd.SendObject acSendNoObject, stDocName, acFormatRTF, , _
        , strEmailAddress, strSubject, strEMailMsg, , True
        
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
        
        DoCmd.SetWarnings False
        
        DoCmd.SetWarnings True
    
    End Sub
    Thanks!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You need to rewrite the sub so that the the variables that you are going to change become arguments. Then you can write another sub, called by the command button, that in turn calls SendMail twice, with different values for the arguments each time.

    For example, if the name of the source query is what is going to change, make the first line of the sub:
    Code:
    Public Sub SendMail(strQuery As String)
    Then amend the line that populates the recordset to be:
    Code:
    Set rst = dbs.OpenRecordset(strQuery)
    Finally, the sub that is called from your command button could be:
    Code:
    Private Sub cmdSendMail_Click()
    
        Call SendMail("QueryName1")
        Call SendMail("QueryName2")
    
    End Sub
    Hope this helps!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •