Send multiple custom e-mails from Access

[rdemyan]

My app creates a building report.

My users have requested that I provide functionality to e-mail these
"building reports" to building managers once a month.

So assuming that I have the following table that lists a building manager's
name, e-mail address and their building, is there a way that I can automate
this to send these monthly e-mails from Outlook. Ideally this would be a
click one button type of action:

Example table:
Name E-Mail Building Nbr

John Doe jo******@www.co m 443
Mary Smit ma*******@www.c om 321
Abe Adam ab******@www.co m 69

So, I want to send the Building 443 report to only John Doe. I want to send
the Building 321 report to only Mary Smit and so on. Is there any way to
automate this? Also, I can't install any executables, so I can't install
Redemption to get around potential issues with Outlook.

Any ideas are greatly appreciated.

Thanks.

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[pietlinden]

See "e-mail senate" on this page:
http://www.granite.ab.ca/access/email.htm

[rdemyan via AccessMonster.com]

Thanks, I'm familiar with this example, but as far as I can tell it doesn't
allow for the kind of customization (i.e. send a specific report to a
specific individual as I mentioned in my first post).

I want to send the Building 443 report to only John Doe. I want to send the
Building 321 report to only Mary
Smit and so on.

So it would no doubt require some kind of loop. So I guess the method would
be

1) Retrieve information from table provided in first post.

2) Loop through each record in that table.

3) Within each loop, run the report and create an e-mail with the name of the
appropriate Facility Manager and include a standard message and subject

4) Then Send the E-mail.

I had heard that there can be a problem with sending automated e-mails from
Outlook because of the security patches. If there are 100 facility managers,
I'd like to avoid the user having to intervene within Outlook for each of the
e-mails (i.e. 100 times).

I'm not allowed to install any executables, dlls such as Redemption to help
with this. Everything has to be within Access.

pi********@hotm ail.com wrote:

>See "e-mail senate" on this page:
http://www.granite.ab.ca/access/email.htm

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[pietlinden]

..rdemyan via AccessMonster.c om wrote:

Thanks, I'm familiar with this example, but as far as I can tell it doesn't
allow for the kind of customization (i.e. send a specific report to a
specific individual as I mentioned in my first post).

I want to send the Building 443 report to only John Doe. I want to send the
Building 321 report to only Mary
Smit and so on.

So it would no doubt require some kind of loop. So I guess the method would
be

1) Retrieve information from table provided in first post.

2) Loop through each record in that table.

3) Within each loop, run the report and create an e-mail with the name of the
appropriate Facility Manager and include a standard message and subject>

4) Then Send the E-mail.

The logic looks good.

If you can create a query that returns the buidling# and the
recipient's name and e-mail address, you should be in business.

<SNIP>
However you can not pass a filter or where clause as you would in a
standard OpenReport command in VBA when outputting the report to a
file.

Me.Filter = "ShipmentsI D=" & Forms![Select Load List]![LoadID]
Me.FilterOn = True

Therefore you must change the filter or where clause yourself in the
reports OnOpen event.

Me.Filter = "ShipmentsI D=" & Forms![Select Load List]![LoadID]
Me.FilterOn = True
</SNIP>
Dim rs as DAO.Recordset '---recordset of recipient info and filter
info together
'--filter the recordset for just the buldings this person is interested
in
'==syntax is something like this...

set rs=DBEngine(0)( 0).QueryDefs("q ryBuildingMaili ng").OpenRecord set
do until rs.eof

me.Filter="Buil dingID=" & rs.Fields("Buil dingID")
me.FilterOn=Tru e
....
then you can output to snapshot and e-mail the snapshot of the report
(also outlined on the page).

If you read the page, you'll see there are examples of all the pieces.
So then all you have to do is put them together. Nice job of Legos,
Tony!

Pieter

[rdemyan via AccessMonster.com]

Thanks, Pieter:

I'm not 100% sure, but I think the problem will occur within Outlook. When
the e-mail is to be sent, Outlook will force the user to intervene to send
the e-mail. This ruins some of the "automation " I was hoping for. We want
to hit one button and have around 100 e-mails sent. Each e-mail will have a
specific person and specific building attachment.

Since the problem is most likely with Outlook, is there a way to send a
primitive e-mail from Access directly and circumvent Outlook altogether? As
I said earlier, the e-mail will have a standard short title and short message
that also will be standard.

I wonder how people handle e-mailing from Access when they don't have Outlook?
?

Thanks for any thoughts on that.

pi********@hotm ail.com wrote:

>Thanks, I'm familiar with this example, but as far as I can tell it doesn't
allow for the kind of customization (i.e. send a specific report to a

[quoted text clipped - 10 lines]

>>
2) Loop through each record in that table.

>3) Within each loop, run the report and create an e-mail with the name of the
appropriate Facility Manager and include a standard message and subject>

>4) Then Send the E-mail.

The logic looks good.

If you can create a query that returns the buidling# and the
recipient's name and e-mail address, you should be in business.

<SNIP>
However you can not pass a filter or where clause as you would in a
standard OpenReport command in VBA when outputting the report to a
file.

Me.Filter = "ShipmentsI D=" & Forms![Select Load List]![LoadID]
Me.FilterOn = True

Therefore you must change the filter or where clause yourself in the
reports OnOpen event.

Me.Filter = "ShipmentsI D=" & Forms![Select Load List]![LoadID]
Me.FilterOn = True
</SNIP>

Dim rs as DAO.Recordset '---recordset of recipient info and filter
info together
'--filter the recordset for just the buldings this person is interested
in
'==syntax is something like this...

set rs=DBEngine(0)( 0).QueryDefs("q ryBuildingMaili ng").OpenRecord set
do until rs.eof

me.Filter="Bui ldingID=" & rs.Fields("Buil dingID")
me.FilterOn=Tr ue
...
then you can output to snapshot and e-mail the snapshot of the report
(also outlined on the page).

If you read the page, you'll see there are examples of all the pieces.
So then all you have to do is put them together. Nice job of Legos,
Tony!

Pieter

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[rdemyan via AccessMonster.com]

I may have found a promising answer. The solution appears to be in the
following post:

http://www.accessmonster.com/Uwe/For...lowing-message
The code is in the 9th post down and uses Outlook Automation. The key,
however, appears to be the following line of code:

'****** Send the email without prompts *******
SendKeys "%{s}", True
'************** *************** *************** ********

I have added "DoEvents" before the SendKeys line to allow Outlook to open
and then everything seems to be working without my intervention (well at
least for one e-mail). I'll have to try this out in a loop.

Comments from more experienced programmers are welcome esp. with regards to
gotchas, etc.

Thanks.

rdemyan wrote:

>Thanks, Pieter:

I'm not 100% sure, but I think the problem will occur within Outlook. When
the e-mail is to be sent, Outlook will force the user to intervene to send
the e-mail. This ruins some of the "automation " I was hoping for. We want
to hit one button and have around 100 e-mails sent. Each e-mail will have a
specific person and specific building attachment.

Since the problem is most likely with Outlook, is there a way to send a
primitive e-mail from Access directly and circumvent Outlook altogether? As
I said earlier, the e-mail will have a standard short title and short message
that also will be standard.

I wonder how people handle e-mailing from Access when they don't have Outlook?
?

Thanks for any thoughts on that.

>>Thanks, I'm familiar with this example, but as far as I can tell it doesn't
allow for the kind of customization (i.e. send a specific report to a

[quoted text clipped - 47 lines]

>>
Pieter

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[Smartin]

I'd be interested to know what more you find out.

IME, working in an Exchange environment, the problem (read: security
issue) is not so much Outlook as it is Exchange. The server won't let me
send unattended emails so the best I can do is create the message, but I
still have to click "Send".

Are you working with Exchange?

rdemyan via AccessMonster.c om wrote:

I may have found a promising answer. The solution appears to be in the
following post:

http://www.accessmonster.com/Uwe/For...lowing-message
The code is in the 9th post down and uses Outlook Automation. The key,
however, appears to be the following line of code:

'****** Send the email without prompts *******
SendKeys "%{s}", True
'************** *************** *************** ********

I have added "DoEvents" before the SendKeys line to allow Outlook to open
and then everything seems to be working without my intervention (well at
least for one e-mail). I'll have to try this out in a loop.

Comments from more experienced programmers are welcome esp. with regards to
gotchas, etc.

Thanks.

rdemyan wrote:

>Thanks, Pieter:

I'm not 100% sure, but I think the problem will occur within Outlook. When
the e-mail is to be sent, Outlook will force the user to intervene to send
the e-mail. This ruins some of the "automation " I was hoping for. We want
to hit one button and have around 100 e-mails sent. Each e-mail will have a
specific person and specific building attachment.

Since the problem is most likely with Outlook, is there a way to send a
primitive e-mail from Access directly and circumvent Outlook altogether? As
I said earlier, the e-mail will have a standard short title and short message
that also will be standard.

I wonder how people handle e-mailing from Access when they don't have Outlook?
?

Thanks for any thoughts on that.

>>>Thanks, I'm familiar with this example, but as far as I can tell it doesn't
allow for the kind of customization (i.e. send a specific report to a

[quoted text clipped - 47 lines]

>>Pieter

--
Smartin

[rdemyan via AccessMonster.com]

I'm using MS Outlook so I guess the answer to your question is yes.

I just had some locking up problems which forced me to three finger salute
out of both Outlook and my app. My app does have a hidden form that checks
if a "Forced Logoff" has been set by an administrator. It checks every minute.
I'm not sure if this caused some kind of conflict. I've temporarily turned
that "feature" off and now everything seems to be working fine. Although I
haven't really checked, it looks like the SendKeys code is telling Outlook to
press the "Send" key. I think the locking up can occur if MS Access keeps
executing code after the SendKeys line and if Outlook wasn't ready to accept
the SendKeys when it was sent (just a guess). I thought DoEvents would take
care of that, but it may not work every time.

Smartin wrote:

>I'd be interested to know what more you find out.

IME, working in an Exchange environment, the problem (read: security
issue) is not so much Outlook as it is Exchange. The server won't let me
send unattended emails so the best I can do is create the message, but I
still have to click "Send".

Are you working with Exchange?

>I may have found a promising answer. The solution appears to be in the
following post:

[quoted text clipped - 39 lines]

>>[quoted text clipped - 47 lines]
Pieter

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[rdemyan via AccessMonster.com]

Well, now it locked up again and I was able to just Ctrl-Alt-Del to close
Outlook. My app stayed open. I tried it again and now the Outlook message
appears, but SendKeys doesn't seem to do anything. Not really sure what is
going on. Could my firewall be trying to stop it? Or perhaps I need to add
additional code. I don't even know what the .NoAging line of code is for
under the Outlook automation.

rdemyan wrote:

>I'm using MS Outlook so I guess the answer to your question is yes.

I just had some locking up problems which forced me to three finger salute
out of both Outlook and my app. My app does have a hidden form that checks
if a "Forced Logoff" has been set by an administrator. It checks every minute.
I'm not sure if this caused some kind of conflict. I've temporarily turned
that "feature" off and now everything seems to be working fine. Although I
haven't really checked, it looks like the SendKeys code is telling Outlook to
press the "Send" key. I think the locking up can occur if MS Access keeps
executing code after the SendKeys line and if Outlook wasn't ready to accept
the SendKeys when it was sent (just a guess). I thought DoEvents would take
care of that, but it may not work every time.

>>I'd be interested to know what more you find out.

[quoted text clipped - 10 lines]

>>>[quoted text clipped - 47 lines]
Pieter

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[pietlinden]

Just wondering, but what would happen if you used CDOSys to send the
messages? Is it affected by the Redemption fun? Or you could use
Blat. Never used it, but IIRC Steve Jorgensen uses it. (Where'd he
go?!)