Emailing from a form

  • Thread starter Thread starter Kevin Bruce
  • Start date Start date
K

Kevin Bruce

Each month, I send out reminders to between thirty and fifty performing
artists advising them of their schedule for the month following. Currently,
these reminders are sent as .rtf attachments. I would rather the content of
the .rtf report appear in the body of the email message. My code at present
is as follows:


Dim stDocName As String
txtEmailReportTo.SetFocus

stDocName = "rptBookingArtistMonthlyReminder"
DoCmd.SendObject acReport, stDocName, , Me!txtEmailReportTo.Text, , ,
"Bookings Reminder"


I assume that in the DoCmd.SendObject line I would have to add something
like:

DoCmd.SendObject acReport, stDocName, PUT SOMETHING HERE?,
Me!txtEmailReportTo.Text, , , "Bookings Reminder", PUT SOMETHING HERE?

Am I on the right track?

Thanks in advance for any help.

_Kevin
 
try using the outlook object model directly;

(reference the outlook 9.0 object library)

and try:

Dim objOutlook As New Outlook.Application
Dim objMessage As MailItem
Dim strBody As String
Dim recArt As Recordset

Set db = CurrentDb()

Set recArt = db.OpenRecordset("qryListOfArtists")
'qryListOfArtists contains the artists info and their
email address as Email


While Not recArt.EOF

If Not IsNull(recArt("Email")) Then
Set objMessage = objOutlook.CreateItem(olMailItem)
With objMessage
.To = "artist name"
.Subject = "next months schedule"
.Body = "all your report info"
.Send
End With
End If

recArt.MoveNext
Wend

recArt.Close
Set recArt = Nothing
Set objOutlook = Nothing
Set objMessage = Nothing

enjoy!!
 
This certainly seems promising, but when the code runs, it gets to the line:

Set recArt = db.Open.Recordset("qryListOfArtists")

at which point the following message appears:

'object doesn't support this property or method'.

What now?

Thanks.

_Kevin
 
Kevin,

Start off the code with this and you will be fine ---

Dim objOutlook As New Outlook.Application
Dim objMessage As MailItem
Dim strBody As String
Dim recArt As DAO.Recordset
Dim Db As DAO.Database

Set db = CurrentDb()


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
 
Still not quite. The code stops at:

Dim recArt As dao.Recordset

with the message: "User defined type not defined."

What next?
 
While this will work, once you get your DAO references sorted out, it's a
bit of overkill for what you can do with just the SendObject command.
Leave your first two arguments empty, and use the Message argument to hold a
string with the information you want in the body of the message.

HTH
- Turtle
 
Okay, now I'm getting a little confused. Let me put it this way... Here is
my code:

Dim stDocName As String
txtEmailReportTo.SetFocus

stDocName = "rptSchedule"
DoCmd.SendObject acReport, stDocName, , Me!txtEmailReportTo.Text, , ,
"Bookings Reminder", <SCHEDULE TO APPEAR AS A TABLE>

The above code attaches a copy of the schedule to the outgoing email just
fine. In addition, I want the schedule to appear in the message of the
email.

The report, when it prints from Access is a nicely formatted, easy to read
schedule that can have up to sixty events listed on it. What appears in the
message of the email should be as easy to read as the hard copy report,
which is not always the case were I to simply cut-and-paste from an .rtf .
Moreover, cutting and pasting from an .rtf is time-consuming and inefficient
and so I'm trying to automate this procedure.

My assumption is that I must take the report and convert it to an html doc
which then appears in the message of the email, or take the data for the
report as SQL statement and create a new doc which then goes into the
message. This is where I'm getting stuck.

Thanks again.

_Kevin
 
Hello, Kevin -

E-mail readers vary.
Some do word-wraps differently from others.
Some read and render HTML -
some don't.
Some with RTF.

So the goal of producing a uniform, nicely formatted message in the body of
the e-mail is rather elusive.
Most folks who send information in the body of an e-mail end up using just
plain text - not even RTF.

I don't know much of what to tell you about formatting your data into the
body of an e-mail message except to keep trying until you like the result.
Here are a couple of small details:
If you use a fixed-width font like Courrier, it will be easier to make
columns line up.
You may want to collect your message up into a string variable:
Dim Mess as string
Mess="This is the first line"
Mess=Mess & vbcrlf & "This is the second line"

HTH
- Turtle
 
Back
Top