Sending a email in a continous form.

L

lilbit27

Can someone look at my code and tell me why my looping is not working?

Dim rst As DAO.Recordset
Dim strSQL As String
Dim db As Database
Dim rec As Database
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Set olApp = Outlook.Application
Dim strToWhom As String
strSql = "TARA.Keep='true'"
Set rst = CurrentDB.OpenRecordset(strSQL)
Do Until rst.EOF
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
..To = " "
..Subject = "Past Due Item"
..BodyFormat = olFormatHTML
..HTMLBody = "<HTML><BODY>" & "Insured: " & Me.InsName & "<br> " &
"Policy: " & Me.Policy & "<br> " & "SP Policy: " & Me.SpcPol & "<br> "
& "Trans Type: " & Me.TranType & "<br> " & "Effective Date: " &
Me.BillEffDte & "<br> " & "Gross: " & Format(Me.Gross, "currency") &
"<br>" & "Commission: " & Format(Me.Comm, "currency") & "<br>" & "Net:
" & Format(Me.Net, "currency") & " </BODY></HTML>"
..Display

rst.MoveNext
Loop
rst.Close
Set rst = Nothing

Its a continous form and I want them click on the check box called
"Keep" for every line that they want to pass to the body of the email.
So where keep is true open email and pass the identified fields for
each line
 
D

Douglas J. Steele

I'm surprised it's not raising an error (unless you've set On Error Resume
Next).

strSQL = "TARA.Keep='true'"

is not valid SQL.
 
L

lilbit27

The only error I get is "Loop without do" why is my strsql wrong. Its
saying bring me all the fields from the tARA table where "keep" equals
true.
 
D

Douglas J. Steele

No, that's not what your SQL is saying.

To get that, you'd need

strSQL = "SELECT * FROM TARA WHERE Keep = True"

assuming Keep is a Boolean, or Yes/No, field, or

strSQL = "SELECT * FROM TARA WHERE Keep = 'True'"

if Keep is a text field.

The "Loop with do" error is likely due to the fact that you don't have an
End With. I'd put it before the rst.MoveNext line.
 
L

lilbit27

Okay here is what I have:
Dim rst As DAO.Recordset
Dim strSQL As String
Dim db As Database
Dim rec As Database
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Set olApp = Outlook.Application
Dim strToWhom As String
strSQL = "SELECT * FROM TARA WHERE Keep = True"
Set rst = CurrentDb.OpenRecordset(strSQL)
Do Until rst.EOF
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
..To = " "
..Subject = "Past Due Item"
..BodyFormat = olFormatHTML
..HTMLBody = "<HTML><BODY>" & "Insured: " & Me.InsName & "<br> " &
"Policy: " & Me.Policy & "<br> " & "SP Policy: " & Me.SpcPol & "<br> "
& "Trans Type: " & Me.TranType & "<br> " & "Effective Date: " &
Me.BillEffDte & "<br> " & "Gross: " & Format(Me.Gross, "currency") &
"<br>" & "Commission: " & Format(Me.Comm, "currency") & "<br>" & "Net:
" & Format(Me.Net, "currency") & " </BODY></HTML>"
..Display
End With
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Not it opens up outlook and passes the info but now it opens up 5 times
because the table shows 5 items where keep is true and it's only
putting one item on the email. I wanted all 5 items to appear in the
body of one email.
 
D

Douglas J. Steele

If you want only one e-mail, then it's up to you to concatenate the
information, and only create one Mail Item object once you're done.

You've got a loop, and you're explicitly creating a Mail Item object each
time the loop iterates.

Try:

Dim rst As DAO.Recordset
Dim strSQL As String
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strMessage As String

strSQL = "SELECT * FROM TARA WHERE Keep = True"
Set rst = CurrentDb.OpenRecordset(strSQL)
Do Until rst.EOF
strMessage = strMessage & "Insured: " & Me.InsName & "<br> " & _
"Policy: " & Me.Policy & "<br> " & "SP Policy: " & Me.SpcPol & _
"<br> " & "Trans Type: " & Me.TranType & "<br> " & _
"Effective Date: " & Me.BillEffDte & "<br> " & "Gross: " & _
Format(Me.Gross, "currency") & "<br>" & "Commission: " & _
Format(Me.Comm, "currency") & "<br>" & "Net: " & _
Format(Me.Net, "currency") & " <br>"
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

'Create e-mail item
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.To = " "
.Subject = "Past Due Item"
.BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>" & strMessage & " </BODY></HTML>"
.Display
End With

End Sub
 
L

lilbit27

Thanks,

I now have one email but now the same record is populating in the email
twice.

Say I click keep on records 1 and record 2. so now keep is true for
record 1 and 2. what I would like to do is populate both of those
record into one email. I have the command in the detail of the
continous form.
 
D

Douglas J. Steele

Didn't even notice that you were getting values from the form, not from the
recordset

Do Until rst.EOF
strMessage = strMessage & "Insured: " & rst.InsName & "<br> " & _
"Policy: " & rst.Policy & "<br> " & "SP Policy: " & rst.SpcPol & _
"<br> " & "Trans Type: " & rst.TranType & "<br> " & _
"Effective Date: " & rst.BillEffDte & "<br> " & "Gross: " & _
Format(rst.Gross, "currency") & "<br>" & "Commission: " & _
Format(rst.Comm, "currency") & "<br>" & "Net: " & _
Format(rst.Net, "currency") & " <br>"
rst.MoveNext
 
L

lilbit27

I get an error message that says method or data memeber not found. and
it hightligs the rst.insName
 
D

Douglas J. Steele

Sorry, all the rst.xxx references should have been rst!xxx.

This assumes that InsName, Policy, SpcPol, TranType, BillEffDte, Gross, Comm
and Net are all fields in table TARA. If not, you'll have to adjust the
references appropriately.
 
L

lilbit27

No need for aplogizes I figured it out after I sent the email. Thank
you for all of your help.

Okay I got it to work by doing this:

Dim rst As DAO.Recordset
Dim strSQL As String
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strMessage As String


strSQL = "SELECT * FROM TARA WHERE Keep = True"
Set rst = CurrentDb.OpenRecordset(strSQL)
Do Until rst.EOF
strMessage = strMessage & "Insured: " & rst!InsName & "<br> " & _
"Policy: " & rst!Policy & "<br> " & "SP Policy: " & rst!SpcPol & _
"<br> " & "Trans Type: " & rst!TranType & "<br> " & _
"Effective Date: " & rst!BillEffDte & "<br> " & "Gross: " & _
Format(rst!Gross, "currency") & "<br>" & "Commission: " & _
Format(rst!Comm, "currency") & "<br>" & "Net: " & _
Format(rst!Net, "currency") & " <br>"
rst.MoveNext
Loop
rst.Close
Set rst = Nothing


'Create e-mail item
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
..To = " "
..Subject = "Past Due Item"
..BodyFormat = olFormatHTML
..HTMLBody = "<HTML><BODY>" & strMessage & " </BODY></HTML>"
..Display
End With
End Sub

How do I get the information to go across the body of the email instead
of down.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 462 shows every other round 2
new email form in front of MsgBox 1
Currenct issue with a text box 1
sending email from Access 1
Specify SenderEmailAccount in VBA 1
License information for this component not found.. 2
Outlook vba errror message says Access can't find my userform 5
Automated EMails 7

Top