473,898 Members | 4,888 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Send multiple custom e-mails from Access

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

Sep 25 '06 #1
17 7662
See "e-mail senate" on this page:
http://www.granite.ab.ca/access/email.htm

Sep 25 '06 #2
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

Sep 25 '06 #3
..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

Sep 25 '06 #4
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

Sep 25 '06 #5
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

Sep 26 '06 #6
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
Sep 26 '06 #7
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

Sep 26 '06 #8
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

Sep 26 '06 #9
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?!)

Sep 26 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
12059
by: jing_li | last post by:
Hi, you all, I am a newbee for php and I need your help. One of my coworker and I are both developing a webpage for our project using php. We have a copy of the same files in different location on the server (in our own accounts on the same machine). When I am testing both versions of our program using the same browser (IE on Windows or Konqueror on Linux) the session variables will mix up and only the latest selection or options will...
66
5057
by: Darren Dale | last post by:
Hello, def test(data): i = ? This is the line I have trouble with if i==1: return data else: return data a,b,c,d = test()
0
1645
by: | last post by:
Hi, I have an app that uses a typed dataset to persist user data. The dataset has a multiple tables with multiple table relations. The problem I am running into is that the user data is dynamic and is not predictable at design time. What I mean by this is the forms that user inputs data is not static - it changes dynamically at runtime based on user input. This makes it difficult to persist the data in a dataset with multiple...
2
2208
by: Greg W. | last post by:
In our setup project, we have the standard web application folder, but we also have 4 web custom folders (virtual directories) that are created at the same level as the web application folder (i.e. not subdirectories). The setup works fine as long as the user leaves the default setting of port 80. If they choose something different, the install program loads the web application folder correctly at the non-standard port, but it installs...
3
2795
by: Shikari Shambu | last post by:
Hi All, I have a situation where multiple applications are sharing some pages/ controls. So, I have a separate common project that has the common pages/ controls. My question is how do I reference these pages/ controls from my ASP.NET web projects WEbApp1 url http://localhost/app1 C:\Apps\App1
11
2232
by: xenophon | last post by:
I have a web site with forms authentication and a single logon page. I have 4 subdirectories, each that should be protected by a different username/password combination. For testing purposes, the username/password are hardcoded into the code-behind C# code. How can I write my web.config to make this happen? Thanks.
3
6514
by: Matt D | last post by:
I've got two web services that use the same data types and that clients will have to consume. I read the msdn article on sharing types (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnservice/html/service07162002.asp) but I don't want clients to have to add two web references and then manually have to edit the proxy classes. After doing some searching I found that putting references to multiple web services in a .disco...
10
3370
by: Logipro | last post by:
Hi, Can it's possible to send a local Web page (HTML) (with pictures) by Outlook, OE, MAPI or Jmail, simply by pass a Html path files ? I try many many tips like replace "cid:" before image in HTML but the picture was never embeded in the message. Simply like this : SendHTMLMail("C:\MyDoc.html")
2
2082
by: upendrajpr | last post by:
Dear friends I am sending mail from my site but it gives me some error "Warning: mail() : "sendmail_from" not set in php.ini or custom "From:" header missing in C:\P............." my code is this
0
1159
by: Jordan S. | last post by:
I'm just wondering if it's possible to have multiple HttpHandlerFactory classes active at once in an ASP.NET 3.5 app. In particular, I want for the "built-in" HttpHandlerFactory that ships with ASP.NET to service .aspx requests. I separately want for another HttpHandlerFactory that I write to service a SUBSET of those requests. I do plan to implement custom URL Rewriting (via custom http module that I
0
9994
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9842
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10859
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10488
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9662
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8036
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7191
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
4298
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3309
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.