How to send two different emails in access

I have a database that when the user is finished entering data the system is supposed to send two different emails. The problem is that access doesnt recognize the second call to send the second email. What I am doing to opening a report based on the users entered data, converting it to html then sending that html to outlook which in turn sends the email. Works great like I said for one but not the other.
I have attached the  code for the two email routines as well as the command button code.

I need to send two emails with different data at the same time.

Any help is greatly appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
Your problem is in your CreateSQRMail Sub.
Set olApps = CreateObject("Outlook.Application")
Set olMails = olApp.CreateItem(olMailItem) ‘this is where the error occurs

Open in new window

You define olApps but try to create the mailitem using olApp.  Change it to:
Set olApps = CreateObject("Outlook.Application")
Set olMails = olApps.CreateItem(olMailItem) ‘this is where the error occurs

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Ideally, you'd create a single Function that would send your email, and then pass in arguments to define the items which may be different. In this case, the only difference seems to be the name of the Report and the Subject, so you should be able to modify CreateHTMLMail and then just call it twice.

Below, I changed lines 1 and 35. After doing that, you would then call the routine like this:

CreateHTMLMail "SQ", "Safety Quick React"
CreateHTMLMail "SQ", ""Full Disclosure Safety Quick React" & AAA

You might also consider including that "AAA" value in the call. I assume that's a global variable you've setup somewhere, and globals can be troublesome in VBA.
Public Sub CreateHTMLMail(strRptName As String, Subject As String)

Dim olApp As Object, olMail As Object
Dim oFilesys, oTxtStream As Object
Dim txtHTML As String
Dim fso As Object

Const olMailItem = 0
Const olFormatHTML = 2
Const FLDR_NAME As String = "C:\temp\"

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFilesys = CreateObject("Scripting.FileSystemObject")

If Not fso.FolderExists(FLDR_NAME) Then
    fso.CreateFolder (FLDR_NAME)
End If

DoCmd.OpenReport strRptName, acViewReport, , "tbl_SQR.SQRUID = " & AAA, acHidden
DoCmd.OutputTo acOutputReport, strRptName, acFormatHTML, "C:\temp\" & strRptName & ".html", False

Set oTxtStream = oFilesys.OpenTextFile("C:\temp\" & strRptName & ".HTML", 1)
txtHTML = oTxtStream.ReadAll

Set oTxtStream = Nothing
Set oFilesys = Nothing

With olMail
.BodyFormat = olFormatHTML
.HTMLBody = txtHTML
.Recipients.Add ""
.Subject = Subject '"Safety Quick React"
End With

Set olApp = Nothing
Set olMail = Nothing
DoCmd.Close acReport, strRptName, acSaveYes
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
As Scott already pointed out, it's better to use a single function than having to create one for each email you send that is slightly different.  I noticed that you modified the names slightly in your second function.  This was unnecessary; you could use the same variable names because their scope is local to the function they're defined in.  Imagine if you had to send 10 different emails and you had to come up with slightly different variable names for each one.

Another problem I saw was that you neglected to use the correct variable in your second function here:
If Not fso.FolderExists(FLDR_NAME) Then
    fso.CreateFolder (FLDR_NAME)
End If

This is a consequence of using the variable names the way you did.

One of the reasons a single function is better is that if you needed to make a change in your emailing process, you wouldn't have to go to more than one process to make this change.

Finally, I would even go further than Scott and separate the process for creating the html file and add more arguments to your email procedure.  This way, you can use the email function throughout your database.  Your parameters would look something like this:
Public Sub SendEmail(
                      sTo As String, _
                      sFrom As String, _
                      sSubject As String, _
                      vbody As Variant, _
                      Optional SAttach As String, _
                      Optional sendHtml As Boolean, _
                      Optional sCC As String, _
                      Optional sBCC As String)

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.