By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,741 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Email automation

P: n/a
I've written several SubRoutines for sending reports via embedded A97
VBA to OUtlook, but was wondering if there's a way to send from
Access/Outlook 2000 a daily email letting certain users know that my
backups of their databases ran. I'd like to do this from the push of a
button.

In Access97 I was using the trusty:

DoCmd.SendObject acSendReport, "MyReport",_ acFormatRTF,
"Jo*****@abcde.com", , "Here is your report", , False

But now I'm in A2000 and just want to send a message, without an
attachment. Just need "To" "Recipients" "Subject" "Body of Message"

So I need some syntax help with my button if anyone has ever done this??

Apr 18 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
instead of "acSendReport use acSendMoObject, and delete the "MyReport"
and acFormatRTF

Apr 18 '06 #2

P: n/a
instead of "acSendReport use acSendNoObject, and delete the "MyReport"
and acFormatRTF

Apr 18 '06 #3

P: n/a
Thanks! That works fine.... one more thing: What to do about handling
carriage returns when the body of the email is long like:

"mcrClients ran at 6:30am 4/18 (for SA)
mcrQM_Pressmen ran at 7:15pm 4/17 (for QM)
mcrIncoming ran at 7:15pm 4/17 (for FG)
mcrQMgmt ran at 7:15pm 4/17 (for QM)"

Apr 18 '06 #4

P: n/a
"mcrClients ran at 6:30am 4/18 (for SA) " & char(13) & _
"mcrQM_Pressmen ran at 7:15pm 4/17 (for QM) " & char(13) & _
"mcrIncoming ran at 7:15pm 4/17 (for FG)" & char(13) & _
"mcrQMgmt ran at 7:15pm 4/17 (for QM)" & char(13)

or the whole thing as a single string without the _

Apr 18 '06 #5

P: n/a
Our new Total Access Emailer program will do the trick. It can now
send filtered reports (or tables or queries) to each recipient.

We have a free beta going right now, so you should try it and let us
know how it works. Rather than Outlook, it uses SMTP to avoid the
security issues with Outlook/MAPI. Visit
http://www.fmsinc.com/products/emailer/index.asp

If you are concerned about system adminstrative tasks like compacts and
backups, try our Total Visual Agent program. That automates the whole
process and lets you schedule events at night with a complete audit
trail of what was done. It can manage an unlimited number of databases
across your network and notify you via email if a problem occurs. It
can also run as an NT service so you can perform the tasks securely and
handle computer crashes.

Visit http://www.fmsinc.com/products/agent/index.html where you can get
a fully functional 30 day demo version of Total Visual Agent.

Luke Chung
President
FMS, Inc.
http://www.fmsinc.com

Apr 18 '06 #6

P: n/a
Since your on a role... the above worked (THANKS!),

How can I put a variable date in the above?

I tried this (but got a compile error):

"mcrClients ran at 6:30am" &Format(Date(), "mm/dd"& "(for SA)" &
Chr(13) & _

Apr 18 '06 #7

P: n/a
I think you left out a parenthesis (and some spaces, which you would
have seen as soon as it worked).

"mcrClients ran at 6:30am " & Format(Date(), "mm/dd") & " (for SA)" &

Chr(13) & _

I am assuming that the format is proper syntax

Ron

Apr 19 '06 #8

P: n/a
Thanks! All is working properly now per below: (except0
.... one problem with syntax is lingering... I get a good carriage
return after the first two lines and the last two lines in this set,
but lines 3, 4, 5, 6 fail to get a carriage return and I get a
continuous string... see any errors?

"Dept. successfully did backups on all databases and executed macros
for feeds to MainData." & Chr(13) & _
"" & Chr(13) & _
"mcrClients ran at 6:30am " & Format(Date, "mm/dd") & " (for SA)" &
Chr(13) & _
"mcrQM_Pressmen ran at 7:15pm " & Format(Date, "mm/dd") & " (for QM)" &
Chr(13) & _
"mcrIncoming ran at 7:15pm " & Format(Date, "mm/dd") & " (for FG)" &
Chr(13) & _
"mcrQMgmt ran at 7:15pm " & Format(Date, "mm/dd") & " (for QM)" &
Chr(13) & _
"" & Chr(13) & _
"Thanks!" & Chr(13) & _

Here's what gets sent:

Dept. successfully did backups on all databases and executed macros for
feeds to MainData.

mcrClients ran at 6:30am 04/19 (for SA)
mcrQM_Pressmen ran at 7:15pm 04/19 (for QM) mcrIncoming ran at 7:15pm
04/19 (for FG) mcrQMgmt ran at 7:15pm 04/19 (for QM)

Thanks!

Apr 19 '06 #9

P: n/a
Boy, you have an interesting situation.
I can't see any logical co-ordination between those that worked and
those that did not.
Since it is NOT acting logically (or we can't see the logic) try some
illogical solutions.

1)I always have my underscore "_" directly after the "&" and you have a
space there. It hardly seems that like that should do it, but it is
worth a try.
2) try adding an extra Chr(13) to the end of the offending line.
3) Maybe, create strings for line1, line2, line3 and line4, load then
before you get there and use the try

"Dept. successfully did backups on all databases and executed macros
for feeds to MainData." & Chr(13) & _
"" & Chr(13) & _
line1 & Chr(13) & line2 & Chr(13) & line3 & Chr(13) & line4 & Chr(13)
&_
"" & Chr(13) & _
"Thanks!" & Chr(13) & _

or
"Dept. successfully did backups on all databases and executed macros
for feeds to MainData." & Chr(13) & _
"" & Chr(13) &_
line1 & Chr(13) &_
line2 & Chr(13) &_
line3 & Chr(13) &_
line4 & Chr(13) &_
"" & Chr(13) &_
"Thanks!" & Chr(13) & _

Ron

Apr 20 '06 #10

P: n/a
Thanks! I found a work-around (see bottom of post)

The "1)I always have my underscore "_" directly after the "&" and you
have a
space there. It hardly seems that like that should do it, but it is
worth a try." ...... I tried this and it gave compile errors (I'm
using A2000 which may be the culprit, I don't recal seeing anything
this weird in A97.

Anyway I put a "" & Chr(13) & _ between each line like this:

"mcrClients ran at 6:30am " & Format(Date, "mm/dd") & " (for SA)" &
Chr(13) & _
"" & Chr(13) & _
"mcrQM_Pressmen ran at 7:15pm " & Format(Date, "mm/dd") & " (for QM)" &
Chr(13) & _
"" & Chr(13) & _
"mcrIncoming ran at 7:15pm " & Format(Date, "mm/dd") & " (for FG)" &
Chr(13) & _
"" & Chr(13) & _
"mcrQMgmt ran at 7:15pm " & Format(Date, "mm/dd") & " (for QM)" &

.... and this work good enough for me ... with the results being:

mcrClients ran at 6:30am 4/18 (for SA)

mcrQM_Pressmen ran at 7:15pm 4/17 (for QM)

mcrIncoming ran at 7:15pm 4/17 (for FG)

mcrQMgmt ran at 7:15pm 4/17 (for QM)

Apr 20 '06 #11

P: n/a
Glad you found a workaround. Very unusual problem. Will have to
watch for it.

Ron

Apr 20 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.