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 "<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
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()