E-Mail query results

[NISMOJim]

I hope this question has an easy answer, though I haven't had many lately that do.
Using VBA, how do I add the results of a query to the body of my e-mail. I already have a sentence as part of the body in the ".HTMLBody = " section of the code, but also want to add the list of names from the query below this sentence. The query has 5 fields, but I only want 3 of them to show up on the E-Mail if possible.

Thanks for the assistance.

[rpeare]

I would build the query you want to attach to the email and not rely on filtering a query before you send it.

here's a link to how to do it.

http://www.fmsinc.com/microsoftacces...endobject.html

[NISMOJim]

Sorry, I'm still lost. I'm using the following code;

Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim ExpDt As Date

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
ExpDt = Now()
ExpDt = Format(ExpDt, "mmmm yyyy")
Expire = ExpDt

With MailOutLook
.BodyFormat = olFormatRichText
.To = ""
.Subject = "Permit Expiration Notice"
.HTMLBody = " The following employee's driving permits have expired, or will expire on the first of " & Expire & ". Please contact Operations to schedule a renewal date." 'qryExpire
.Display
End With

Exit Sub
email_error:
MsgBox "An error has occurred. " & vbCrLf & "The error is: " & Err.Description
Resume Error_out
Error_out:

End Sub

The query (qryExpire) already comes up with the list of names I want in the body of the e-mail, I'm just not sure how to add it after the sentences that are already going into the body.
One other thing I haven't figured out yet... the date format shows the current month & year, but I need it to show the following month. I've tried adding "+1" in a few different ways, but it isn't working.

Thanks for your help.

[rpeare]

Ok, let's say your query has two fields PersonFN and PersonLN (first land last name)

.Subject = "Permit Expiration Notice"
.HTMLBody = " The following employee's driving permits have expired, or will expire on the first of " & Expire & ". Please contact Operations to schedule a renewal date." 'qryExpire
.Display

This is your existing information

I would add a variable to your procedure

Dim sBody as string
Dim db as Database
Dim rst as recordset

then when you get to this section of code:

.Subject = "Permit Expiration Notice"
set db = currentdb
set rst = db.openrecordset("qryExpire")
rst.movefirst
do while rst.eof <> true
      sbody = sbody & rst.fields("PersonLN") & ", " & vbcrlf rst.fields("PersonFN")
      rst.movenext
loop
rst.close
set db= nothing
.HTMLBody = " The following employee's driving permits have expired, or will expire on the first of " & Expire & ". Please contact Operations to schedule a renewal date." & vbcrlf & sbody
.Display

[NISMOJim]

Thanks for the reply.
I entered the code that you suggested, and I'm getting a syntax error on the looping line that starts with sBody = sBody & rst.fields... Here's what I entered...

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim sBody As String
Dim db As Database
Dim rst As Recordset

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

ExpirationDate = Date
ExpirationDate = ExpirationDate + 29
Expire = Format(ExpirationDate, "mmmm yyyy")

With MailOutLook
.BodyFormat = olFormatRichText
.To = ""
.Subject = "Permit Expiration Notice"
Set db = CurrentDb
Set rst = db.OpenRecordset("qryExpire")
rst.MoveFirst
Do While rst.EOF <> True
sBody = sBody & rst.Fields("LName") & ", " & vbcrlf rst.Fields ("FName")
rst.MoveNext
Loop
rst.Close
Set db = Nothing

.HTMLBody = " The following employee's driving permits have expired, or will expire on the first of " & Expire & ". Please contact Operations to schedule a renewal date." & vbCrLf & sBody
.Display
End With

Exit Sub
email_error:
MsgBox "An error has occurred. " & vbCrLf & "The error is: " & Err.Description
Resume Error_out
Error_out:

End Sub

Also, if possible, I would like to add a third field to that line (Cmpny).
Lastly, I think I have the date part working now. I know it looks funny, but this message won't be e-mailed out until a week or so into the month, so the following month should always show up correctly.

Thanks again for your help.

[NISMOJim]

OK, I got part of this figured out. I changed that line of code to look like this...

sBody = sBody & rst.Fields(1) & ", " & rst.Fields(2) & " " & rst.Fields(3) & vbCrLf
Also changed the body line to this...

.HTMLBody = " The following employee's driving permits have expired, or will expire on the first of " & Expire & ". Please contact Operations to schedule a renewal date." & vbCrLf & sBody


Now the whole body looks like one big sentence. Isn't the vbCrLf supposed to force a new line? I'll keep playing with it, but any guidance is appreciated.

[rpeare]

try chr(13)chr(10) instead of vbcrlf

[NISMOJim]

That line of code now looks like this...

sBody = sBody & rst.Fields(1) & ", " & rst.Fields(2) & " " & rst.Fields(3) & chr(13)chr(10)

Still getting a syntax error on that line. Did I do this wrong?

[rpeare]

sBody = sBody & rst.Fields(1) & ", " & rst.Fields(2) & " " & rst.Fields(3) & chr(13) & chr(10)

[NISMOJim]

Not getting any errors this time, but the e-mail is still all clumped together.

[rpeare]

Can you post a sample database, with some fake data in it. All my tests correctly generate the sbody.

[NISMOJim]

Attachment 4352

I hope this worked. I checked it again after deleting & changing alot of the data, & it's still doing the same thing. I hope you have better luck with it than I have.

Thanks again!

[rpeare]

I missed it on your original post but you're using an HTML format so instead of chr(10)chr(13) or vbcrlf just add the text

& "<BR>" wherever you want a new line to start

[NISMOJim]

That did the trick, thanks so much. Just too much code out there that I don't understand. I hate to keep bothering you guys with my problems, but you really are the best source of information I have found. Thank you for the work you do.

PS...
I did find one more thing. I added a few spaces between the name & the company in the code, but only one space shows up in the e-mail. There must be another trick out there that baffles me.
Thanks for the help!

[rpeare]

HTML code for a forced non compression of a blank space is

&nbsp;