e-mail from Access

[Nick T]

Hi,
I have a query which stores information on my clients (including their
e-mail address).
I want to be able to simply click a command button on one of my forms and an
e-mail is sent to all those clients in my query for which the message is a
standard (presaved) message (ideally).

Any suggestions or guidance??

Many thanks

 

[Arvin Meyer [MVP]]

Try this:

http://www.datastrat.com/Code/OutlookEmail.txt

 

[Beetle]

Here are some links with information on the subject;

http://www.databasedev.co.uk/email-from-ms-access-form.html

http://support.microsoft.com/?id=145787

http://www.granite.ab.ca/Access/email.htm

 

[Nick T]

Thanks Arvin,

Dont know if im missing somthing, but, this code in a command button gives
me a compile error - 'User-defined type not defined'. ??

Im no pro, and may be biting off more than i can chew at the moment, however
i wont know unless i try! Any more simplistic guidance?

Thanks

 

[Beetle]

Open a code window an go to Tools/References. Make sure the
reference for "Microsoft Outlook #.0 Object Library" is checked. The
# will be a number that will vary depending on your version of Access.

You will need to scroll through the (rather long) list to find it.
(it's alphabetical)
--
_________

Sean Bailey

 

[Nick T]

Hi,
Seems to be working well so far, so thanks - Arvin, i seem to have sent an
e-mail to your address as in the code, so apologies for this (just ignor)!

Anyway, it seems as though i have to specify in the code what e-mail address
i want to send to. Is there any way i can code it so that when the command
button is clicked it sends the details to all e-mail addresses in one of my
queries??

All help greatly appreciated!

 

[Beetle]

Supposing that the field in your recordsource that stores email
addresses is named, for example, CustomerEMail. You would
modify the code like;

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = Me.CustomerEMail '<<<<< modified line
.Subject = "Look at this sample attachment"
.body = "The body doesn't matter, just the attachment"
.Attachments.Add "C:\Test.htm"
'.attachments.Add "c:\Path\to\the\next\file.txt"
.Send
'.ReadReceiptRequested
End With

 

[Nick T]

Do i need to specify the record source somwhere?
I have various queries, for which all will have e-mail addresses in(?)

Maybe a little further info will help me explain:

I have two queries - one for clients whos birthday it is next month, and one
for clients who's birthday it is in two months. Both these two queries get
their data from my main 'Client Details' table.
I want to click a command button on one of my forms and send a 'birthday
e-mail' to the clients whos birthday it is next month. And a separate
command button when clicked, will send a different letter to those clients in
my second query (i understand each cmd will need its own code). Is this
poss??

Thanks for the help!.

 

[Beetle]

I was assuming the email address was coming from the
table/query (recordset) that your form was based on, but it
sounds like that is not the case.

There are different ways you could approach this. If you want to do
it using code behind the command buttons on your existing form, then
it is going to require quite a bit more code. You will need to create a
recordset in code (using the same SQL that your current birthday
queries use), then loop through the records in that recordset adding
e-mails as you go. Post back if you want more info on how to do
it that way.

Alternatively, you could create a couple of extra forms, using your
existing queries as the recordsources, and use the code you already
have for command buttons on those forms (maybe have those forms
open from command buttons on your existing form).

 

[Nick T]

Hi,
If you could provide info on your 'option 1', i would be really grateful.

Thanks, & look forward to hearing from you.

 

[Arvin Meyer [MVP]]

Hi,
Seems to be working well so far, so thanks - Arvin, i seem to have sent an
e-mail to your address as in the code, so apologies for this (just ignor)!

I laughed and replied to you. I sometimes get a half dozen emails a day like
that. One fellow sent me 9 emails before he figured out where they were
going.

 

[Arvin Meyer [MVP]]

If your query returns more than a single record, and you want to email
several people, have a look at the following:

http://www.datastrat.com/Code/MultipleEmail.txt

 

[Nick T]

Thanks for the code.
Do i put this behind the on click cmd of my cmd button on my form??

Could you explain a little further as to what i do with this code & where i
put it?
As it stands at the moment, i dont see how it can relate to the cmd button

 

[Nick T]

Hi Beetle,
Ok, iv made a little progress,
Iv created a couple of extra forms (one for each of my queries)
Iv set the defult view on the form to 'continous forms' so that all the
records are displayed at once (without having to scrole through individual
records).

Iv got 1 command button in the footer with the following code behind it:

Private Sub Command21_Click()
'Arvin Meyer 03/12/1999
'Updated 7/21/2001
On Error GoTo Error_Handler

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = Me.Email_Address
.Subject = "Its Your Birthday Soon"
.Body = "Dear Customer, test, test, test"
.Attachments.Add "C:\Documents and
Settings\Compaq_Administrator\Desktop\Word\birthday letter.doc"
'.attachments.Add "c:\Path\to\the\next\file.txt"
.Send
'.ReadReceiptRequested
End With

Exit_Here:
Set objOutlook = Nothing
Exit Sub

Error_Handler:
MsgBox Err & ": " & Err.Description
Resume Exit_Here

End Sub

This sends the e-mail i want and works ok, however it only sends an e-mail
to the record which is selected. Can the code be adjusted so that it sends
e-mail to all the records on the form??

Thanks for your help - greatly appreciated.

 

[Beetle]

Here are a couple of examples for you.
Note: this is untested code so it may have some typos, etc. but it should give
you a general idea of how you can do it.

Option 1) This will create a string of e-mail addresses and then open
a single instance of the Outlook e-mail editor with all the
addresses in the To: field


Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strEMails As String
Dim i As Integer

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

strEMails = ""

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If

For i = 1 To .RecordCount
If strEMails <> "" Then strEMails = strEMails & "; "
If Len(.Email_Address) > 0 Then
strEMails = strEMails & .Email_Address
End If
.MoveNext
Next i
End With

With objEmail
.To = strEMails
.Subject = "Its Your Birthday Soon"
.Body = "Dear Customer, test, test, test"
.Attachments.Add "C:\Documents and
Settings\Compaq_Administrator\Desktop\Word\birthday letter.doc"
'.attachments.Add "c:\Path\to\the\next\file.txt"
.Send
'.ReadReceiptRequested
End With

Exit_Here:
Set objEMail = Nothing
Set objOutlook = Nothing
Exit Sub

Error_Handler:
MsgBox Err & ": " & Err.Description
Resume Exit_Here

End Sub


Option 2) This would open multiple instances (one for each recipient) of
the Outlook e-mail editor and you would need to send each one
separately.

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim i As Integer

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If

For i = 1 To .RecordCount
If Len(.Email_Address) > 0 Then
With objEmail
.To = .Email_Address
.Subject = "Its Your Birthday Soon"
.Body = "Dear Customer, test, test, test"
.Attachments.Add "C:\Documents and
Settings\Compaq_Administrator\Desktop\Word\birthday
letter.doc"
'.attachments.Add "c:\Path\to\the\next\file.txt"
.Send
'.ReadReceiptRequested
End With
End If
.MoveNext
Next i
End With

Exit_Here:
Set objEMail = Nothing
Set objOutlook = Nothing
Exit Sub

Error_Handler:
MsgBox Err & ": " & Err.Description
Resume Exit_Here

End Sub


Again, these are untested examples. Be sure to make a backup of your db
before you implement any of the above.

 

[Arvin Meyer [MVP]]

You can put in the form or in a standard module and call it from a command
button something like:

Sub EmailButton_Click()
Call Function Email([email protected], Me.txtSubject, _
Optional varMsg As Variant, Optional varAttachment As Variant)
End Sub

Using your own email address assures that the email will work without error,
and will get sent to you if there are no other names to send to.

 

[Nick T]

Hiya,
Thanks for the suggestions - i think option 1 best suites my needs. Iv put
this code in and had a read through to try and get my head round it all!

However, i get a Run-time error '438' (Object doesnt support this property
or method).
When i click debug, the following piece of code is highlighted:

If Len(.Email_Address) > 0 Then

Any suggestions??
Thanks so much for your help by the way - really appreciated (im self
tought, or not as the case may be, but trying)!!.

Hope to hear back from you.

 

[Beetle]

I'm not sure why you're getting that error. I've tested it on my end and
it seems to work fine. Can you post your code as you currently have it?

 

[Nick T]

Hi,
Thanks for the patience & help. My code as currently is:

Private Sub Command27_Click()
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strEMails As String
Dim i As Integer

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

strEMails = ""

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If

For i = 1 To .RecordCount
If strEMails <> "" Then strEMails = strEMails & "; "
If Len(.Email_Address) > 0 Then
strEMails = strEMails & .Email_Address
End If
.MoveNext
Next i
End With

With objEmail
.To = strEMails
.Subject = "Its Your Birthday Soon"
.Body = "Dear Customer, test, test, test"
.Attachments.Add "C:\Documents and
Settings\Compaq_Administrator\Desktop\Word\birthday letter.doc"
'.attachments.Add "c:\Path\to\the\next\file.txt"
.Send
'.ReadReceiptRequested
End With

Exit_Here:
Set objEmail = Nothing
Set objOutlook = Nothing
Exit Sub

Error_Handler:
MsgBox Err & ": " & Err.Description
Resume Exit_Here

End Sub

Thanks.

 

[Beetle]

Is Email_Address the correct name for the e-mail field in the recordsource
(query) that this form is based on? If not then change the line to reflect
the actual field name.

If Len(.NameOfTheEMailField) > 0 Then