Email in Access

[OdAwG]

Hello All,

Is it possible to send an email from Access? I found a Microsoft article on
how to do this but I keep getting an error "RUNTIME ERROR 438" -- Object
doesn't support this property or method. Listed below is the article from
microsoft

How to use a recordset to send Outlook e-mail to multiple recipients in
Microsoft Access
http://support.microsoft.com/?id=318881

Any and all help is greatly appreciated

Argus

[pietlinden]

Nope. patently impossible.

Great. how about showing us your code? Or why not search the NG. I
posted a whole bunch of junk on this. There's a really nice example on
www.amazecreations.com/downloads

There are literally hundreds of examples in this NG. All you gotta do
now is figure out how to use the search features on the website.

[OdAwG]

I found this code snip that works very well, but limited to only one (1) To,
one (1) CC, and one (1) BCC. Can you help me modify it to include at least
three (3) for each. If not then one (1) for each would have to do.

I didn't need the attachment part so I commented it out just in case.

In a module, I pasted the following code and then called it from a RunCode
SendEMail() in a macro

Function SendEMail()
Dim strTo As String, strSubject As String, _
varBody As Variant, strCC As String, _
strBCC As String, strAttachment As String, _
strAttachment1 As String, strAttachment2 As String, _
strAttachment3 As String, strAttachment4 As String

strTo = "Your recipient email goes here"
strCC = "Your CC recipient email goes here"
strBCC = " Your BCC email goes here"
strSubject = "Your Subject goes here"
varBody = "Body text goes here"
' Add more strattachments if needed and modify IF statement below
'strAttachment = "attachment1"
'strAttachment1 = "attachment2"
'strAttachment2 = "attachment3"
'strAttachment3 = "attachment4"
'strAttachment4 = "attachment5"
'Start Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

'Logon
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

'Send a message
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'Fill Out and Send Message
olMail.To = strTo
olMail.CC = strCC
olMail.BCC = strBCC
olMail.Subject = strSubject
olMail.Body = varBody
' Modify these statements if more attachmewnts are needed
'If Len(strAttachment) <0 Then
' olMail.Attachments.Add (strAttachment)
' If Len(strAttachment1) <0 Then
' olMail.Attachments.Add (strAttachment1)
' If Len(strAttachment2) <0 Then
' olMail.Attachments.Add (strAttachment2)
' If Len(strAttachment3) <0 Then
' olMail.Attachments.Add (strAttachment3)
' If Len(strAttachment4) <0 Then
' olMail.Attachments.Add (strAttachment4)
' End If
' End If
' End If
' End If
' End If

olMail.Send

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Function

<pi********@hotmail.comwrote in message
news:11********************@b28g2000cwb.googlegrou ps.com...

Nope. patently impossible.

Great. how about showing us your code? Or why not search the NG. I
posted a whole bunch of junk on this. There's a really nice example on
www.amazecreations.com/downloads

There are literally hundreds of examples in this NG. All you gotta do
now is figure out how to use the search features on the website.

[Rick Brandt]

"OdAwG" <Od***@goneloose.comwrote in message
news:CA*******************@tornado.texas.rr.com...

>I found this code snip that works very well, but limited to only one (1) To,
one (1) CC, and one (1) BCC. Can you help me modify it to include at least
three (3) for each. If not then one (1) for each would have to do.

In all three cases you just separate the recipients with semi-colons and you can
have as many as you want.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[pietlinden]

so open the recordset inside the function (or pass it the recordset as
an argument), and loop through it and add the info you need where it
should go.
OdAwG wrote:

I found this code snip that works very well, but limited to only one (1) To,
one (1) CC, and one (1) BCC. Can you help me modify it to include at least
three (3) for each. If not then one (1) for each would have to do.

I didn't need the attachment part so I commented it out just in case.
Function SendEMail()
Dim strTo As String, strSubject As String, _
varBody As Variant, strCC As String, _
strBCC As String, strAttachment As String, _
strAttachment1 As String, strAttachment2 As String, _
strAttachment3 As String, strAttachment4 As String

strTo = "Your recipient email goes here"
strCC = "Your CC recipient email goes here"
strBCC = " Your BCC email goes here"
strSubject = "Your Subject goes here"
varBody = "Body text goes here"
' Add more strattachments if needed and modify IF statement below
'strAttachment = "attachment1"
'strAttachment1 = "attachment2"
'strAttachment2 = "attachment3"
'strAttachment3 = "attachment4"
'strAttachment4 = "attachment5"
'Start Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

'Logon
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

'Send a message
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'Fill Out and Send Message
olMail.To = strTo
olMail.CC = strCC
olMail.BCC = strBCC
olMail.Subject = strSubject
olMail.Body = varBody
' Modify these statements if more attachmewnts are needed
'If Len(strAttachment) <0 Then
' olMail.Attachments.Add (strAttachment)
' If Len(strAttachment1) <0 Then
' olMail.Attachments.Add (strAttachment1)
' If Len(strAttachment2) <0 Then
' olMail.Attachments.Add (strAttachment2)
' If Len(strAttachment3) <0 Then
' olMail.Attachments.Add (strAttachment3)
' If Len(strAttachment4) <0 Then
' olMail.Attachments.Add (strAttachment4)
' End If
' End If
' End If
' End If
' End If

olMail.Send

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Function

pass the "multi-valued" variables in as arrays and then loop through
them?

say you're inside the code and you open a recordset in there.

dim rsRecip as dao.recordset
set rsRecip = dbengine(0)(0).OpenQueryDef("qryRecipients")
'--- basic loop...
'----use with Recipients, Attachments, Bcc, ... collections of email
message.
do until rsRecip.EOF
olkMsg.Recipients.Add(rsRecip.Fields("EmailAddress "))
rsRecip.MoveNext
Loop
... set all the other properties here...
olk.Attachments.Add(rsAttachments.Fields("FullPath ToFile"))
olkMsg.Bcc=rsX.Fields("AnotherEmailField")

[Nick 'The Database Guy']

i find a simple way of doing this is using the docmd.SendObject
acSendNoObject, , , <To address>, <Cc address>, <Bcc address>,
<Subject>, <Message textcommand.

pi********@hotmail.com wrote:

so open the recordset inside the function (or pass it the recordset as
an argument), and loop through it and add the info you need where it
should go.
OdAwG wrote:

I found this code snip that works very well, but limited to only one (1) To,
one (1) CC, and one (1) BCC. Can you help me modify it to include at least
three (3) for each. If not then one (1) for each would have to do.

I didn't need the attachment part so I commented it out just in case.
Function SendEMail()
Dim strTo As String, strSubject As String, _
varBody As Variant, strCC As String, _
strBCC As String, strAttachment As String, _
strAttachment1 As String, strAttachment2 As String, _
strAttachment3 As String, strAttachment4 As String

strTo = "Your recipient email goes here"
strCC = "Your CC recipient email goes here"
strBCC = " Your BCC email goes here"
strSubject = "Your Subject goes here"
varBody = "Body text goes here"
' Add more strattachments if needed and modify IF statement below
'strAttachment = "attachment1"
'strAttachment1 = "attachment2"
'strAttachment2 = "attachment3"
'strAttachment3 = "attachment4"
'strAttachment4 = "attachment5"
'Start Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

'Logon
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

'Send a message
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'Fill Out and Send Message
olMail.To = strTo
olMail.CC = strCC
olMail.BCC = strBCC
olMail.Subject = strSubject
olMail.Body = varBody
' Modify these statements if more attachmewnts are needed
'If Len(strAttachment) <0 Then
' olMail.Attachments.Add (strAttachment)
' If Len(strAttachment1) <0 Then
' olMail.Attachments.Add (strAttachment1)
' If Len(strAttachment2) <0 Then
' olMail.Attachments.Add (strAttachment2)
' If Len(strAttachment3) <0 Then
' olMail.Attachments.Add (strAttachment3)
' If Len(strAttachment4) <0 Then
' olMail.Attachments.Add (strAttachment4)
' End If
' End If
' End If
' End If
' End If

olMail.Send

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Function

pass the "multi-valued" variables in as arrays and then loop through
them?

say you're inside the code and you open a recordset in there.

dim rsRecip as dao.recordset
set rsRecip = dbengine(0)(0).OpenQueryDef("qryRecipients")
'--- basic loop...
'----use with Recipients, Attachments, Bcc, ... collections of email
message.
do until rsRecip.EOF
olkMsg.Recipients.Add(rsRecip.Fields("EmailAddress "))
rsRecip.MoveNext
Loop
... set all the other properties here...
olk.Attachments.Add(rsAttachments.Fields("FullPath ToFile"))
olkMsg.Bcc=rsX.Fields("AnotherEmailField")

[OdAwG]

whats the command to send the msg automatically. when i type in the example
you gave, it popped open an email with all the data but i had to hit the
send button to send it

thanks
"Nick 'The Database Guy'" <ni*****@btinternet.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

i find a simple way of doing this is using the docmd.SendObject
acSendNoObject, , , <To address>, <Cc address>, <Bcc address>,
<Subject>, <Message textcommand.

pi********@hotmail.com wrote:

so open the recordset inside the function (or pass it the recordset as
an argument), and loop through it and add the info you need where it
should go.
OdAwG wrote:

I found this code snip that works very well, but limited to only one

(1) To,

one (1) CC, and one (1) BCC. Can you help me modify it to include at

least

three (3) for each. If not then one (1) for each would have to do.
>
I didn't need the attachment part so I commented it out just in case.
>
>
Function SendEMail()
Dim strTo As String, strSubject As String, _
varBody As Variant, strCC As String, _
strBCC As String, strAttachment As String, _
strAttachment1 As String, strAttachment2 As String, _
strAttachment3 As String, strAttachment4 As String
>
strTo = "Your recipient email goes here"
strCC = "Your CC recipient email goes here"
strBCC = " Your BCC email goes here"
strSubject = "Your Subject goes here"
varBody = "Body text goes here"
' Add more strattachments if needed and modify IF statement below
'strAttachment = "attachment1"
'strAttachment1 = "attachment2"
'strAttachment2 = "attachment3"
'strAttachment3 = "attachment4"
'strAttachment4 = "attachment5"
'Start Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")
>
'Logon
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
>
'Send a message
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'Fill Out and Send Message
olMail.To = strTo
olMail.CC = strCC
olMail.BCC = strBCC
olMail.Subject = strSubject
olMail.Body = varBody
' Modify these statements if more attachmewnts are needed
'If Len(strAttachment) <0 Then
' olMail.Attachments.Add (strAttachment)
' If Len(strAttachment1) <0 Then
' olMail.Attachments.Add (strAttachment1)
' If Len(strAttachment2) <0 Then
' olMail.Attachments.Add (strAttachment2)
' If Len(strAttachment3) <0 Then
' olMail.Attachments.Add (strAttachment3)
' If Len(strAttachment4) <0 Then
' olMail.Attachments.Add (strAttachment4)
' End If
' End If
' End If
' End If
' End If
>
olMail.Send
>
Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing
>
End Function
>

pass the "multi-valued" variables in as arrays and then loop through
them?

say you're inside the code and you open a recordset in there.

dim rsRecip as dao.recordset
set rsRecip = dbengine(0)(0).OpenQueryDef("qryRecipients")
'--- basic loop...
'----use with Recipients, Attachments, Bcc, ... collections of email
message.
do until rsRecip.EOF
olkMsg.Recipients.Add(rsRecip.Fields("EmailAddress "))
rsRecip.MoveNext
Loop
... set all the other properties here...
olk.Attachments.Add(rsAttachments.Fields("FullPath ToFile"))
olkMsg.Bcc=rsX.Fields("AnotherEmailField")

[pietlinden]

probably something really cryptic, like

olkMsg.SEND

[Smartin]

OdAwG wrote:

whats the command to send the msg automatically. when i type in the example
you gave, it popped open an email with all the data but i had to hit the
send button to send it

There's another parameter after [MessageText] called [EditMessage].
(There are actually two more... you should really read the help file
entry for SendObject.) Set [EditMessage] to 1 (default) to enable
editing the message before sending or 0 to send without intervention.

Note: actual results with the 0 setting depend on the environment you
are working in. For me, at work the Exchange setup prohibits emails
generated by other applications to be sent without user intervention.
This is a security issue and there is no way around it.

At home, my Thunderbird client gives a warning, but this can be disabled.

--
Smartin

[OdAwG]

by doing the following, it works fine from home

Sub test()
DoCmd.SendObject acSendNoObject, "", "", od***@dawghouse.com, "", "", "Test
message", "Hello there!", False
End Sub

We are very security conscious at work. I VPN in and tried it and it also
works. I had to disable access warning and a few other access setting, but
all in all it worked

"Smartin" <sm********@yahoo.comwrote in message
news:4a******************************@giganews.com ...

OdAwG wrote:

whats the command to send the msg automatically. when i type in the

example

you gave, it popped open an email with all the data but i had to hit the
send button to send it

There's another parameter after [MessageText] called [EditMessage].
(There are actually two more... you should really read the help file
entry for SendObject.) Set [EditMessage] to 1 (default) to enable
editing the message before sending or 0 to send without intervention.

Note: actual results with the 0 setting depend on the environment you
are working in. For me, at work the Exchange setup prohibits emails
generated by other applications to be sent without user intervention.
This is a security issue and there is no way around it.

At home, my Thunderbird client gives a warning, but this can be disabled.

--
Smartin

[Smartin]

OdAwG wrote:

by doing the following, it works fine from home

Sub test()
DoCmd.SendObject acSendNoObject, "", "", od***@dawghouse.com, "", "", "Test
message", "Hello there!", False
End Sub

We are very security conscious at work. I VPN in and tried it and it also
works. I had to disable access warning and a few other access setting, but
all in all it worked

Not that it matters, but you don't need the empty strings. The following
will do just fine:
DoCmd.SendObject acSendNoObject, , , od***@dawghouse.com, etc.

Also, be aware that boolean False works for [EditMessage], but True
evaluates to -1, which will process the same as 0 (False). There are
Access constants intended for this parameter, but I find them a bit arcane:
acEdit (1)
adAdd (0)

I was really hoping to combine scheduled tasks + automated email to
create a 100% hands-off method of running a daily report off my PC
without putting my request into the trust of the IS prioritizers (and a
total rewrite for a different platform), but I can see the point of the
restriction. If a program could automatically create and send email
without anyone knowing, it /could/ open the door for a spam bot to
hijack the email system.

Glad it works for you, but don't tell the security admins (^; They may
not be happy about this vulnerability.
--
Smartin