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

    Unanswered: How to add 2 objects to a button

    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.?

    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>"
            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
        DoCmd.SetWarnings False
        DoCmd.SetWarnings True
    End Sub

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    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:
    Public Sub SendMail(strQuery As String)
    Then amend the line that populates the recordset to be:
    Set rst = dbs.OpenRecordset(strQuery)
    Finally, the sub that is called from your command button could be:
    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