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

Send multiple custom e-mails from Access

P: n/a
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.com 443
Mary Smit ma*******@www.com 321
Abe Adam ab******@www.com 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.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

Sep 25 '06 #1
Share this Question
Share on Google+
17 Replies


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

Sep 25 '06 #2

P: n/a
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********@hotmail.com wrote:
>See "e-mail senate" on this page:
http://www.granite.ab.ca/access/email.htm
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

Sep 25 '06 #3

P: n/a
..rdemyan via AccessMonster.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
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 = "ShipmentsID=" & 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 = "ShipmentsID=" & 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("qryBuildingMailing"). OpenRecordset
do until rs.eof

me.Filter="BuildingID=" & rs.Fields("BuildingID")
me.FilterOn=True
....
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

P: n/a
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********@hotmail.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 = "ShipmentsID=" & 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 = "ShipmentsID=" & 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("qryBuildingMailing"). OpenRecordset
do until rs.eof

me.Filter="BuildingID=" & rs.Fields("BuildingID")
me.FilterOn=True
...
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.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

Sep 25 '06 #5

P: n/a
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.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

Sep 26 '06 #6

P: n/a
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.com 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

P: n/a
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.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

Sep 26 '06 #8

P: n/a
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.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

Sep 26 '06 #9

P: n/a
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

P: n/a
I've been doing some more research and it looks like SendKeys is not reliable
(even MS mentions that in one of their articles but I forgot to note which
one).

Maybe instead I could send out the 'mailer' emails from Outlook. So maybe
the following:

1) Create all of the reports in my application in snapshot or maybe pdf
format. Give the instances of the reports a name that includes the building
number. Store all the reports in a folder.

2) Have the user open Outlook and open a custom form created for them there.
That form will use VBA code to actually do the mailing. I've never
programmed in Outlook but I can probably figure it out. Also, I understand
that Outlook 2003 does not display the security warnings if the code is in
Outlook and uses native Outlook items (whatever that exactly means).

I'm just thinking that I might have more success if I go this way.

Thoughts, suggestions, etc.

pi********@hotmail.com wrote:
>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?!)
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

Sep 26 '06 #11

P: n/a
so why won't CDOSys work? Lyle posted a nice example of it here...
just dig around.

Sep 26 '06 #12

P: n/a
I don't know what that is. Does an executable or dll have to be installed.
If so, then it won't work.
Users have no administrator rights on their computers and the IT department
does not allow
executables or other software to be installed without their approval
(typically takes 12 to 18 months).
I think you see the problems that I face.

Thanks.

pi********@hotmail.com wrote:
>so why won't CDOSys work? Lyle posted a nice example of it here...
just dig around.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

Sep 27 '06 #13

P: n/a
rdemyan via AccessMonster.com wrote:
I don't know what that is. Does an executable or dll have to be
installed. If so, then it won't work.
Users have no administrator rights on their computers and the IT
department does not allow
executables or other software to be installed without their approval
(typically takes 12 to 18 months).
I think you see the problems that I face.
CDOSys is included automatically in Windows 2000 and XP.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Sep 27 '06 #14

P: n/a
Thanks, I've been doing a little research on it and it does look interesting
and promising. I'll try it out over the weekend and report back.

Rick Brandt wrote:
>I don't know what that is. Does an executable or dll have to be
installed. If so, then it won't work.
[quoted text clipped - 3 lines]
>(typically takes 12 to 18 months).
I think you see the problems that I face.

CDOSys is included automatically in Windows 2000 and XP.
--
Message posted via http://www.accessmonster.com

Sep 28 '06 #15

P: n/a
rdemyan via AccessMonster.com wrote:
Thanks, I've been doing a little research on it and it does look interesting
and promising. I'll try it out over the weekend and report back.
If you are going to use CDO and you are distributing your application
to dissimilar machines and system-setups, you may find it much less
troublesome to use late binding, than to set a reference to CDO.

This is the only late-binding thingme I can lay my hands on right now;
It has a lot of extraneous stuff. (It sends a report as one page of
html.)

Option Explicit

Public Sub SendReportAsHTML( _
ByVal ReportName As String, _
ByVal SMTPServer As String, _
ByVal SendUserName As String, _
ByVal SendPassword As String, _
ByVal SendEmailAddress As String, _
ByVal Subject As String, _
ByVal Recipients As String, _
Optional ByVal NumberofPagesAllowed As Long = 10)

Dim Buffer As String
Dim Position As Long
Dim FileNumber As Integer
Dim HTML As String
Dim HTMLFullPath As String
Dim iCfg As Object
Dim iMsg As Object
Dim Skelton As String
Dim TempDirectory As String
Dim Truncate As Long

Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")

TempDirectory = Environ$("temp")
If Len(TempDirectory) = 0 Then TempDirectory = CurDir$()
TempDirectory = TempDirectory & "\"
Skelton = Format(Now(), "mmmddyyyyhhnnss")
HTMLFullPath = TempDirectory & Skelton & ".html"

DoCmd.OutputTo acOutputReport, ReportName, acFormatHTML, HTMLFullPath

HTMLFullPath = Dir$(TempDirectory & Skelton & "*.html")
While Len(HTMLFullPath) <0 And NumberofPagesAllowed <0
HTMLFullPath = TempDirectory & HTMLFullPath
FileNumber = FreeFile()
Open HTMLFullPath For Binary As #FileNumber
Buffer = String(LOF(FileNumber), vbNullChar)
Get #FileNumber, , Buffer
Close #FileNumber
Position = InStr(Buffer, "</TABLE>")
While Position <0
Truncate = Position
Position = InStr(Truncate + 1, Buffer, "</TABLE>")
Wend
HTML = HTML & Left(Buffer, Truncate + 7)
HTML = HTML & "<hr>"
Kill HTMLFullPath
HTMLFullPath = Dir$()
NumberofPagesAllowed = NumberofPagesAllowed - 1
Wend

If Len(HTMLFullPath) <0 And NumberofPagesAllowed = 0 Then _
HTML = HTML & "<br><b>Partial Report: Additional Pages not Shown"

With iCfg.Fields
..Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
= 25
..Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
SMTPServer
..Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
= 1
..Item("http://schemas.microsoft.com/cdo/configuration/sendusername") =
SendUserName
..Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =
SendPassword
..Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress")
= SendEmailAddress
..Update
End With
With iMsg
..Configuration = iCfg
..Subject = Subject
..To = Recipients
..HTMLBody = HTML
..Send
End With

SendReportAsHTMLExit:
Close
Set iMsg = Nothing
Set iCfg = Nothing
Exit Sub

SendReportAsHTMLErr:
With Err
MsgBox .Description, vbCritical, "Error " & .Number
End With
Resume SendReportAsHTMLExit

End Sub

Sep 28 '06 #16

P: n/a
Thanks Lyle:

I'll extract the needed parts and try this over the weekend.

Lyle Fairfield wrote:
>Thanks, I've been doing a little research on it and it does look interesting
and promising. I'll try it out over the weekend and report back.

If you are going to use CDO and you are distributing your application
to dissimilar machines and system-setups, you may find it much less
troublesome to use late binding, than to set a reference to CDO.

This is the only late-binding thingme I can lay my hands on right now;
It has a lot of extraneous stuff. (It sends a report as one page of
html.)

Option Explicit

Public Sub SendReportAsHTML( _
ByVal ReportName As String, _
ByVal SMTPServer As String, _
ByVal SendUserName As String, _
ByVal SendPassword As String, _
ByVal SendEmailAddress As String, _
ByVal Subject As String, _
ByVal Recipients As String, _
Optional ByVal NumberofPagesAllowed As Long = 10)

Dim Buffer As String
Dim Position As Long
Dim FileNumber As Integer
Dim HTML As String
Dim HTMLFullPath As String
Dim iCfg As Object
Dim iMsg As Object
Dim Skelton As String
Dim TempDirectory As String
Dim Truncate As Long

Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")

TempDirectory = Environ$("temp")
If Len(TempDirectory) = 0 Then TempDirectory = CurDir$()
TempDirectory = TempDirectory & "\"
Skelton = Format(Now(), "mmmddyyyyhhnnss")
HTMLFullPath = TempDirectory & Skelton & ".html"

DoCmd.OutputTo acOutputReport, ReportName, acFormatHTML, HTMLFullPath

HTMLFullPath = Dir$(TempDirectory & Skelton & "*.html")
While Len(HTMLFullPath) <0 And NumberofPagesAllowed <0
HTMLFullPath = TempDirectory & HTMLFullPath
FileNumber = FreeFile()
Open HTMLFullPath For Binary As #FileNumber
Buffer = String(LOF(FileNumber), vbNullChar)
Get #FileNumber, , Buffer
Close #FileNumber
Position = InStr(Buffer, "</TABLE>")
While Position <0
Truncate = Position
Position = InStr(Truncate + 1, Buffer, "</TABLE>")
Wend
HTML = HTML & Left(Buffer, Truncate + 7)
HTML = HTML & "<hr>"
Kill HTMLFullPath
HTMLFullPath = Dir$()
NumberofPagesAllowed = NumberofPagesAllowed - 1
Wend

If Len(HTMLFullPath) <0 And NumberofPagesAllowed = 0 Then _
HTML = HTML & "<br><b>Partial Report: Additional Pages not Shown"

With iCfg.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
= 25
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
SMTPServer
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
= 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") =
SendUserName
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =
SendPassword
.Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress")
= SendEmailAddress
.Update
End With
With iMsg
.Configuration = iCfg
.Subject = Subject
.To = Recipients
.HTMLBody = HTML
.Send
End With

SendReportAsHTMLExit:
Close
Set iMsg = Nothing
Set iCfg = Nothing
Exit Sub

SendReportAsHTMLErr:
With Err
MsgBox .Description, vbCritical, "Error " & .Number
End With
Resume SendReportAsHTMLExit

End Sub
--
Message posted via http://www.accessmonster.com

Sep 29 '06 #17

P: n/a
CDO seems to be working quite well. I'm able to send out multiple customized
e-mails to individuals each having different attachments. No security
warnings to deal with.

It will be interesting to see what happens when I try this in a highly secure
IT environment (i.e. selected e-mail attachments are removed, users do not
have any Windows administrator rights, etc) which my home office network is
not.

Thanks for all the help.

rdemyan wrote:
>Thanks Lyle:

I'll extract the needed parts and try this over the weekend.
>>Thanks, I've been doing a little research on it and it does look interesting
and promising. I'll try it out over the weekend and report back.
[quoted text clipped - 101 lines]
>>
End Sub
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Oct 2 '06 #18

This discussion thread is closed

Replies have been disabled for this discussion.