Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trying to send two distinct emails from one button click. 1

Status
Not open for further replies.

TheresAlwaysAWay

Programmer
Mar 15, 2016
135
US
I have an Access 2007 system with 20 users, SQL Server 2012, and I have a routine currently working which does many different functions and ultimately sends an email. The email is generated and displayed on screen, and it is set up to manually click send.

I want to open and populate a second email, also set up to manually send. Basically I want to see two pending emails open. I would click send on each individually.

I have the code set up to populate one, but when the code for the second email runs it simply repopulates the open mail with the new information instead of actually opening the second mail.

It wouldn't matter to me if email 1 opened, then I'd click send, and then email 2 would open and again click send. I am struggling with trying to get two independent mails to run and I'm hopeful someone can help.

As always, thanks in advance.
 
can you post your code? that may help to figure out if you are using the same resource, which would call the same instance of the same new email. Just want to see what you need to adjust.

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Here is greatly abbreviated code. I took out lots of things that are irrelevant and just add confusion. I even edited out the actual email body text. I'm trying to keep what I send to a bare minimum.

As I said, ultimately only one email is generated but the body and to: field of the first is overwritten by the second. Interestingly, I have the same document attached twice to the single mail as well.

I appreciate you reviewing this for me.

Private Sub EMailQuote_Click()
Dim wobj As Word.Application
Dim strFN As String, strEmail As String, strDealerEmail As String, strFullEmail As String
Dim strSubject As String, strBody As String
Dim I As Long, X As String, strReturn As String
Dim strFaxage As String
Dim strFaxageEmail As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strAttach1 As String
Dim clipboard As MSForms.DataObject
Set clipboard = New MSForms.DataObject
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)

On Error GoTo ErrHandler:
Me.Refresh


strFullEmail = ""
strEmail = ""
strDealerEmail = ""
strReturn = ""

If Len(Me.EMail) > 2 Then
strEmail = Nz(Me.EMail, "") & ";"
End If

If Me.DealerEMail Like "*@*" Then
strDealerEmail = Nz(Me.DealerEMail, "") & ";"
End If

If Screen.ActiveForm![DealerFax] Like "(*" Then
For I = 1 To Len(DealerFax)
X = Mid(DealerFax, I, 1)
If X Like "[0-9]" Then
strReturn = strReturn & X
End If
Next
If Len(strReturn) > 2 Then
strReturn = strReturn & "@faxage.com;"
End If
End If

strDealerEmail = strDealerEmail & strReturn 'Sets Dlr Email
strFullEmail = strDealerEmail & strEmail 'sets email for dlr and cust
'End new email address routine

strSubject = IIf(DLookup("[Ignore]", "[Dealers]", "[DealerID]=Screen.ActiveForm!DealerID") = False, Trim(Replace(Screen.ActiveForm![Dlr], "*", "")) & _
" is providing " & First & " " & Last & " a", First & " " & Last & "'s") & " complimentary insurance quotation" & _
IIf(DLookup("[Ignore]", "[Dealers]", "[DealerID]=Screen.ActiveForm!DealerID") = False, Null, " from American " & _
"Adventure Insurance")
Me.Refresh
Me.Refresh

If MsgBox("Do you want to edit the Word document before sending?", vbYesNo, "Confirm Document Edit") = vbYes Then

Set wobj = CreateMDoc()

strFN = CreatePDF(wobj)
Me.Refresh
Exit Sub

Else


Set wobj = CreateMDoc()

strFN = CreatePDF(wobj)

With objEmail
'.To = [TempVars]![EMailAdd].Value
If DLookup("[DSExists]", "[DSExists]") = True And Len(Me.EMail) > 2 Then 'send dealer text only to dealer if ds and No cust email
.To = strDealerEmail
End If
If DLookup("[DSExists]", "[DSExists]") = False Then 'send same text to all if no DS
.To = strFullEmail
End If
.Subject = strSubject
.HTMLBody = "<head>" & "<style> .indented { padding-left: 50pt; padding-right: 10pt; } </style>" & "</head>" & "<p style='font-family:verdana;font-size:12'>" & _
"Hello Mail 1"

.Display

.Attachments.Add strFN
End With
Kill strFN
Me.Refresh
wobj.Application.Quit False
Set wobj = Nothing
Set objOutlook = Nothing

End If

If DLookup("[DSExists]", "[DSExists]") = True And Len(Me.EMail) > 2 Then 'DS veh and sends only to cust email address
Set wobj = CreateMDoc()

strFN = CreatePDF(wobj)

With objEmail
.To = strEmail
.Subject = strSubject
.HTMLBody = "<head>" & "<style> .indented { padding-left: 50pt; padding-right: 10pt; } </style>" & "</head>" & "<p style='font-family:verdana;font-size:12'>" & _
"Hello Mail2"

.Display

.Attachments.Add strFN
End With
Kill strFN
Me.Refresh
wobj.Application.Quit False
Set wobj = Nothing
End If
Set objOutlook = Nothing


ErrHandler:
DoCmd.SetWarnings True
If TempVars!ErrorHandler = "Error" Then
clipboard.SetText TempVars!ShortAppName
clipboard.PutInClipboard
Me.Refresh
DoCmd.RunMacro "Control2.Timer"
TempVars!AllowOpen.Value = Null
Else
If Err.Number = 2501 Then
Application.Echo True
TempVars!AllowOpen.Value = Null
Exit Sub
End If
End If
End Sub
 
This is a lot to pour through, but at first glance, you only have one objEmail As Outlook.MailItem, so you will only have 1 email. You like need an objEmail2 As Outlook.MailItem for the 2nd email, to create a 2nd item. make sense?

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Yessss! Of course! I'm sure that's the answer.

One other question. Is it possible to force the code to break? Ideally, I'd like the code to run to the point where first email is generated, then stay on hold until it's sent and then continue to run to generate the second, then stay on hold again until it gets sent, and finally finish the code after all is done.
 
BTW, the second object was the problem. I am now generating two distinct emails.
 
Excellent! The break thing would be more difficult. I would maybe suggest you have a msgbox pop up, with a "Please click ok, when you are done with first email". That should pause the code, until the message is responded to. Not the most elegant, but I would think that would create the pause.

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Actually, everything is just fine as it is. Once I started running and testing it it turns out that I don't even need to break it as I thought I might.

Thanks again for your help. Sometimes the most obvious things can be the most vexing.
 
seriously! I'm stuck on my form freezing, and it unfreezes when I right-click the title bar. How bizarre is that?

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top