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

Sending emai via access form

100+
P: 106
Let say I have a form (MS Access) with the list of people and their email addresses and a text field which I can write my message.
I have button that when I press the button I want it to send email to all email adressess in this form.

Is this possible.

Or there is a better way of doing this, please help.
Oct 24 '06 #1
Share this Question
Share on Google+
32 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Let say I have a form (MS Access) with the list of people and their email addresses and a text field which I can write my message.
I have button that when I press the button I want it to send email to all email adressess in this form.

Is this possible.

Or there is a better way of doing this, please help.
Do you want to send the message to everyone in the list or only those selected?
Oct 24 '06 #2

100+
P: 106
Do you want to send the message to everyone in the list or only those selected?
Thank you for your help
The selected ones

The one I have availabe in the form becuase I run qurey on the backgroud which only displayes those emails I need.

Thanks
Oct 24 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry Tara

I understood from your first post that these people were in a list. Are they just separate records on the form and therefore the list is based on the query behind the record source of the form?


Thank you for your help
The selected ones

The one I have availabe in the form becuase I run qurey on the backgroud which only displayes those emails I need.

Thanks
Oct 24 '06 #4

100+
P: 106
Sorry Tara

I understood from your first post that these people were in a list. Are they just separate records on the form and therefore the list is based on the query behind the record source of the form?
Hi

Yes they are part of the list
you see I have this table which hold the list of name and their email addresses.

The form shows selected email address based on some criteria.

I hope this helps.
Oct 24 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry Tara

Still trying to clarify.

Is there an actual ListBox on your form with a RowSource of the names and emails

OR

have you designed a form to return names and emails of specified people based on a query?


Hi

Yes they are part of the list
you see I have this table which hold the list of name and their email addresses.

The form shows selected email address based on some criteria.

I hope this helps.
Oct 24 '06 #6

100+
P: 106
Ok
lets go a stet back,
I have a form that have a list box contaning the list of items that are availabe in my database, and a button next to it.
When a user select a item from the list and click on the button it will open a new form with information about that selected item in my case contacts name and email address. (there may be 1 contact per item or could be 100 contacts per item), now I have a button called send email, when I press it I want it to send email to those contacts.
I hope it is clear now, let me know if it is not

Thanks
Oct 24 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
OK, try this:

Put this function in a module:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function sendEmail(emailAddresses As String, subjectLine As String, message As String)
  3.     ' send the email
  4.     DoCmd.SendObject acSendNoObject, , , emailAddresses, , , subjectLine, message, False
  5.  
  6. End Function
  7.  
  8.  
Go to the Data tab and set the Bound Column to 2 (Assumes 2 columns Name and email in the listbox).
Put a second text box on your form to enter the Subject Line, I'm calling it subjectTxt for this example
I'm calling the message textbox messageTxt.
Note in your listbox in the email column all email addresses have to be separated using semi colons

Then behind the email button put the following code.

Expand|Select|Wrap|Line Numbers
  1.  
  2. sendEMail(lstBoxName, subjectTxt, messageTxt)
  3.  
  4.  
Oct 24 '06 #8

100+
P: 106
OK, try this:

Put this function in a module:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function sendEmail(emailAddresses As String, subjectLine As String, message As String)
  3.     ' send the email
  4.     DoCmd.SendObject acSendNoObject, , , emailAddresses, , , subjectLine, message, False
  5.  
  6. End Function
  7.  
  8.  
Go to the Data tab and set the Bound Column to 2 (Assumes 2 columns Name and email in the listbox).
Put a second text box on your form to enter the Subject Line, I'm calling it subjectTxt for this example
I'm calling the message textbox messageTxt.
Note in your listbox in the email column all email addresses have to be separated using semi colons

Then behind the email button put the following code.

Expand|Select|Wrap|Line Numbers
  1.  
  2. sendEMail(lstBoxName, subjectTxt, messageTxt)
  3.  
  4.  
Thank you so much Mccarty

I will try this, and please keep an ey open for this subject I may be back soon

Thanks again heaps
Oct 24 '06 #9

100+
P: 106
Thank you so much Mccarty

I will try this, and please keep an ey open for this subject I may be back soon

Thanks again heaps
I tried that code, it gave me error, some thing about sendobject
???
Oct 25 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
I tried that code, it gave me error, some thing about sendobject
???
I need to know exactly what the error was.
Oct 25 '06 #11

100+
P: 106
I need to know exactly what the error was.
It says compiler error: Syntax error

I have the following code behind the button

Private Sub Command7_Click()
sendEmail(List8,Text10,Text12)
End Sub

and this one in module

Function sendEmail(EmailAddresses As String, SubjectLine As String, Message As String)

DoCmd.SendObject acSendNoObject, , , EmailAddresses, , , SubjectLine, , , Message, False
End Function


Thanks
Oct 25 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
You have too many commas between SubjectLine and Message. It should be:

DoCmd.SendObject acSendNoObject, , , EmailAddresses, , , SubjectLine, Message, False
Oct 25 '06 #13

100+
P: 106
Hi
I tried removing those extra commas but still I get the same message, "Compiler error", "Syntax error"

Function sendEmail(EmailAddresses As String, SubjectLine As String, Message As String)

DoCmd.SendObject acSendNoObject, , , EmailAddresses, , , SubjectLine, Message, False

End Function

Thanks
Oct 25 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Can you show me the full text of the email addresses you are trying to send. If its too long I just need to see how the addresses are being stored and displayed so just include the first part of the string covering a few addresses.
Oct 25 '06 #15

100+
P: 106
Can you show me the full text of the email addresses you are trying to send. If its too long I just need to see how the addresses are being stored and displayed so just include the first part of the string covering a few addresses.
At the moment it only has two email addresses, hotmail and yahoo.

In Imediate window I run ?List8 and Text10 and Text12
They are all Null

Cheers
Oct 26 '06 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
At the moment it only has two email addresses, hotmail and yahoo.

In Imediate window I run ?List8 and Text10 and Text12
They are all Null

Cheers
Tara for this to work it needs a series of email addresses

pass through the following

sendEmail("somebody@yahoo.com;somebody@hotmail.com","Test1","MessageText")

just to test it.
Oct 26 '06 #17

100+
P: 106
Tara for this to work it needs a series of email addresses

pass through the following

sendEmail("somebody@yahoo.com;somebody@hotmail.com","Test1","MessageText")

just to test it.

I Tried that, still gives me the same error.

Do you think I need to add some library ????
Oct 26 '06 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
I Tried that, still gives me the same error.

Do you think I need to add some library ????
A syntax error doesn't indicate this and its part of the standard library.

Where are you putting this function?
Oct 26 '06 #19

100+
P: 106
A syntax error doesn't indicate this and its part of the standard library.

Where are you putting this function?
It is behind the button, (in form)
Private Sub Command7_Click()

sendEmail(List8,Text10,Text12)
'sendEmail("tara_f99@hotmail.com","testsubject","t estmessage")
End Sub


and this one in Module

Function sendEmail(EmailAddresses As String, SubjectLine As String, Message As String)

DoCmd.SendObject acSendNoObject, , , EmailAddresses, , , SubjectLine, Message, False

End Function


Thanks
Oct 27 '06 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
This should be working fine. Which email are you using outlook, outlook express or other?
Oct 27 '06 #21

100+
P: 106
This should be working fine. Which email are you using outlook, outlook express or other?
I ma using Novell GroupWise


Tara
Oct 29 '06 #22

MMcCarthy
Expert Mod 10K+
P: 14,534
I ma using Novell GroupWise


Tara
That's your problem. The Access sendObject only works well with outlook and outlook express. I'll see if I can find a routine that works with groupwise.
Oct 29 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
That's your problem. The Access sendObject only works well with outlook and outlook express. I'll see if I can find a routine that works with groupwise.
Tara

You will find a class for doing what you want to do here. Download and implement it as best you can and if you need any further help let me know.
Oct 29 '06 #24

NeoPa
Expert Mod 15k+
P: 31,419
You will find a class for doing what you want to do here. Download and implement it as best you can and if you need any further help let me know.
You didn't post any link M :(
Oct 29 '06 #25

100+
P: 106
Tara

You will find a class for doing what you want to do here. Download and implement it as best you can and if you need any further help let me know.
where is the link
Oct 29 '06 #26

MMcCarthy
Expert Mod 10K+
P: 14,534
where is the link

Sorry Tara

http://www.mvps.org/access/modules/mdl0059.htm
Oct 29 '06 #27

100+
P: 106
Sorry Tara

http://www.mvps.org/access/modules/mdl0059.htm
Hi
It doesn't opens this database,
If you are able to open it, would you please copy and paste it here so i can use it

thanks
Oct 30 '06 #28

100+
P: 106
Hi
It doesn't opens this database,
If you are able to open it, would you please copy and paste it here so i can use it

thanks
Hi Maccarthy

I have the first program in one module (What did you say to call the module? GW????), and the 2nd program in second module.

Now behind the button I have this code
sendGWEmail "List8", "Text10", "Text12"

I have added the few GroupWise libraries as well.
There is still compilation problem
It first complains about the
sendGWEmail "List8", "Text10", "Text12"

Then when I try to step though it complains about the “Invalid use of Me keyword”

Any idea

I thought it may be because of the module name
What did you say tho call the module?

Thanks
Oct 30 '06 #29

MMcCarthy
Expert Mod 10K+
P: 14,534

What did you say to call the module? GW????)
, and the 2nd program in second module.
This is correct the first module is GW


Now behind the button I have this code
sendGWEmail "List8", "Text10", "Text12"
if you type sendGWEmail followed by a space does it prompt you for entry


Then when I try to step though it complains about the “Invalid use of Me keyword”
Where have you used the Me keyword?
Oct 30 '06 #30

100+
P: 106
This is correct the first module is GW

if you type sendGWEmail followed by a space does it prompt you for entry
yes it does

Where have you used the Me keyword?
It is in the first program you send me

thanks
Oct 30 '06 #31

P: 9
I'm only a newbie and there may be severe limitations using this method and perhaps it may be appear overly complicated but it works for me at least the 4 people I've tested it on.

I include a column in a member table of email addresses and a column email y/n and link this to the display of details in a personal details main form so that individuals to receive email can be set

I do a query selecting all individuals from the member table attach this to a combo box

I attach this to a E-mail drop down form that has 3 butttons and a very large text box and the query form

1 Button uses a query update to update the main table emaily/n with all yesses
1 Button uses a query update to update the main table emaily/n with all noes (to reset all individuals selected)
1 Button Emails all the individuals currently selected.

I alter the empty text box caption to add all individuals selected using vb to add the individual details to the text box caption separated by a comma dropping the last comma.
I then Email this caption, using the method described in lots of places elsewhere in the community
Oct 30 '06 #32

Andrew Thackray
P: 76
This module uses the Windows MAPI object to open a mail session & send mail to a list of email addresses

Expand|Select|Wrap|Line Numbers
  1. Sub SendMail()
  2.  
  3. Dim objSession As MAPI.Session ' Local
  4. Dim objMessage As Message  ' local
  5. Dim objRecip As Recipient
  6. On Error GoTo error_olemsg
  7. Dim doc As busobj.IDocument
  8. Dim rep As busobj.Report
  9. Dim DPName As String
  10. Dim test As Boolean
  11.  
  12.  
  13.  
  14. Set objSession = CreateObject("MAPI.Session")
  15. objSession.Logon profileName:="bo_admin", newSession:=False, showDialog:=False
  16.  
  17. If objSession Is Nothing Then
  18.     Err.Raise 10, "MA MACRO", "must first log on; use Session->Logon"
  19.     Exit Sub
  20.     End If
  21.  
  22. Set objMessage = objSession.Outbox.Messages.Add
  23. If objMessage Is Nothing Then
  24.     Err.Raise 11, "MA MACRO", "could not create a new message in the Outbox"
  25.     Exit Sub
  26.     End If
  27.  
  28. With objMessage ' message object
  29.      ' Substitue this with your subject
  30.     .Subject = "Resort -Monthly Report"
  31.      ' Substitue with your the message in body part of the mail
  32.     .Text = "The Monthly reports  for " & Format(Now, "mmm") & " is attached herewith."
  33.     For i = 1 To ThisDocument.DataProviders.Count
  34.     If ActiveDocument Is Nothing Then
  35.      MsgBox "NO Active Document to refresh"
  36.   Else
  37.     Set doc = ActiveDocument
  38.    If Not doc.IsAddIn Then
  39.  
  40.             'use this for converting to csv
  41.  
  42.              DPName = "C:\" + DataProviders.Item(i).Name
  43.             test = DataProviders.Item(i).ConvertTo(boExpAsciiCSV, 1, DPName)
  44.  
  45.             'use this for converting to pdf format
  46.  
  47.  
  48.         Else
  49.     End If
  50.   End If
  51.     Set objAttach = .Attachments.Add ' add the attachment
  52.  
  53. If objAttach Is Nothing Then
  54.      Err.Raise 12, "MA MACRO", "Unable to create new Attachmentobject"
  55.      Exit Sub
  56.      End If
  57.  
  58.  
  59.      With objAttach
  60.  
  61.  
  62.             .Name = DataProviders.Item(i).Name & ".csv"
  63.             .Source = "C:\" & DataProviders.Item(i).Name & ".csv"
  64.  
  65.      End With
  66.  
  67.  
  68.     .Update ' update message to save attachment in MAPI system
  69.  
  70.     Next i
  71.  
  72.     Set objRecip = .Recipients.Add
  73.     With objRecip
  74.         objRecip.Name = ("MAILID") 'substitue with the mailid of the recipient or groupname
  75.         objRecip.Type = CdoTo
  76.         objRecip.Resolve
  77.         End With
  78.  
  79.     ' use this for sending to a recipient as cc
  80.  
  81.    'Set objRecip = .Recipients.Add
  82.     'With objRecip
  83.       ' objRecip.Name = ("ddas")
  84.        'objRecip.Type = CdoCc
  85.        'objRecip.Resolve
  86.        'End With
  87.  
  88.  
  89.     .Update
  90.     ' update message to save attachment in MAPI system
  91.     .Send showDialog:=False
  92.     End With
  93.     For i = 1 To ThisDocument.DataProviders.Count
  94.      Kill "C:\" & DataProviders.Item(i).Name & ".csv"
  95.    Next i
  96.     objSession.Logoff
  97.     Exit Sub
  98.  
  99.  
  100. error_olemsg:
  101. 'MsgBox "Error " & Str(Err) & ": " & Error$(Err)
  102.     Err.Raise 13, "MA MACRO", "Error " & Str(Err) & ": " & Error$(Err)
  103.     Resume Next
  104.  
  105. End Sub
  106.  
Oct 30 '06 #33

Post your reply

Sign in to post your reply or Sign up for a free account.