Form to send email (email blast) (1 Viewer)

Is it possible to have a form that when the user clicks on a button it looks up a column in a table of email addresses and sends the email without the use of Outlook or other email applications?
I am after a form that when the user inputs in fields e.g. subject, the message itself and then clicks a send button. This would then look up the addresses in a table called contacts (created when the user clicks on a button on the form) and sends the email without opening an email application. These emails can be plain text and not have any attachments as the email would have a link to the attachment instead to reduce the email size.
Is it possible to add a delay say about 10 seconds before each email is sent (or longer if required) I have had a look at CDO but I am getting stuck on where to start. If someone has a basic example form with what I am after that would be very useful for me to test and understand the basics. Thanks

the code is not difficult - this is the simplest example I can find

http://www.rondebruin.nl/win/s1/cdo.htm

Perhaps if you provided your attempt we can suggest where you are going wrong

You can use this for the delay:

http://access.mvps.org/access/api/api0021.htm

Thanks for the replies. When you say "Perhaps if you provided your attempt we can suggest where you are going wrong" The screenshot in the first message is the latest I have. I have no code behind it. After looking at the website given I am still struggling to understand it sorry. Is it possible to send a basic form with the code behind it? Or shall I send a sample of the form I have and someone edit it for the code to be placed in the correct place? Sorry thinking about it I should have sent the database and not a screenshot. I see the link takes me to a excel example but I am using Access?! I understand the code behind it is VBA but I just need to start as basic as possible. Since the code is there is it possible?

excel vba is pretty much the same as access vba.

I was referring to your comment

I have had a look at CDO but I am getting stuck on where to start.

Click to expand...

Copy and paste the code I provided in my link to the click event of the send email button, then change names to match yours and try to run it. You will almost certainly get errors, but the error description should tell you where the problems are.

Get it working for a single email - hardcoding an email address if required - then look to expand it to loop through a number of emails.

I don't know what the 'create email macro' button? label? is supposed to do

Hi CJ_London. Many thanks for your help on this. So far I have started again with the form so all there is a button that says send email and I have right clicked > Properties and on the "onclick" event I have added the code under the "Event Procedure" and entered the code on the VBA screen. From the example for gmail from the website you have shown I have copied the gmail module and edited the lines to my email address. I do get errors but I don't understand them? Shall I send a copy of the form with the code behind (removing my personal email settings) so you can see where it is going wrong?

Regarding the "Create email macro button" query. All I will have behind this is a macro that copies a list of emails and inserts them into a spreadsheet. I would then like the form to read the spreadsheet and send the emails out in a loop. Looking at the sample you have sent this seems to be the doable but again I am stuck even at the first stage of adding the code. Many thanks.

No, just copy and paste the code as you now have it (hide your email details), plus the error description. From the error message, if you click debug it will go to the code and highlight where the error is, so advise that as well.

It is better to go this route so you understand what the code is doing and why. If I just fix it for you, you won't learn anything.

Hi CJ_London. Many thanks for the help so far. Much appreciation.

Here is the code taken from the website and now edited for sharing purposes.
Below the code is the error message I am getting.

Option Compare Database

Private Sub Command0_Click()
'If you have a GMail account then you can try this example to use the GMail smtp server
'The example will send a small text message
'You must change four code lines before you can test the code

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "MY GMAIL ADDRESS ENTERED HERE"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "MY GMAIL PASSWORD ENTERNED HERE"

'Use your own mail address to test the code in this line
.To = "MY GMAIL ADDRESS ENTERED HERE"

'Change YourName to the From name you want to use
.From = """MY NAME HERE"" <MY GMAIL ADDRESS ENTERED HERE>"

'If you get this error : The transport failed to connect to the server
'then try to change the SMTP port from 25 to 465

'Possible that you must also enable the "Less Secure" option for GMail
'https://www.google.com/settings/security/lesssecureapps

Sub CDO_Mail_Small_Text_2()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant

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

iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "MY GMAIL ADDRESS ENTERED HERE"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "MY GMAIL PASSWORD ENTERNED HERE"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

With iMsg
Set .Configuration = iConf
.To = "Mail address receiver"
.CC = ""
.BCC = ""
' Note: The reply address is not working if you use this Gmail example
' It will use your Gmail address automatic. But you can add this line
' to change the reply address
.ReplyTo = "MY GMAIL ADDRESS ENTERED HERE"
.From = """MY NAME HERE"" MY GMAIL ADDRESS ENTERED HERE"
.Subject = "Important message"
.TextBody = strbody
.Send
End With

End Sub
End Sub

When I click the button I get "invalid or unqualified reference". I click OK and then it takes be to the VBA code and highlights .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") =

you are missing a bit of code, so check the example and instructions against yours

.item is the problem, it will either be

something.item...........

or


also recommend you use the advanced editor so you can use the code tags as I have here to preserve the indenting - makes code a lot easier to read and understand

Hi CJ_London. Thanks for the reply.
I have re-downloaded the example and opened the code from the gmail module and I cannot see where I am missing the code. I don't understand? There is nothing missing in front of the .item in the example code from that website. I have followed the instructions you have sent. Have you downloaded that example as well and does it work for you?

OK I will use the advanced editor button. I've been clicking quick reply.

previous message deleted - I didn't see your subsequent code, - all these lines need to be deleted or commented out (not all of them are) - they are just commentary leading up to what the actual function

'If you have a GMail account then you can try this example to use the GMail smtp server
'The example will send a small text message
'You must change four code lines before you can test the code

.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "MY GMAIL ADDRESS ENTERED HERE"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "MY GMAIL PASSWORD ENTERNED HERE"

'Use your own mail address to test the code in this line
.To = "MY GMAIL ADDRESS ENTERED HERE"

'Change YourName to the From name you want to use
.From = """MY NAME HERE"" <MY GMAIL ADDRESS ENTERED HERE>"

'If you get this error : The transport failed to connect to the server
'then try to change the SMTP port from 25 to 465

'Possible that you must also enable the "Less Secure" option for GMail
'https://www.google.com/settings/security/lesssecureapps

Sub CDO_Mail_Small_Text_2()

And also comment out/delete one of the End Subs at the bottom

Just to confirm that a ' is a comment line? Is a . one as well? So i make .item.... now item... and it should work?

no, only anything to the right of a ' is a comment (except where ' is within a string)

Then im confused now as you said I am missing some code from the example but I copied it from the gmail module and took out the comments that I needed to starting with the ' lines and entered the personal information I needed to use. See above. If I have copied the gmail code and it still errors on the .item line why does it work for you? What code is missing?

all you need is

Hi CJ_London. Starting from the very beginning and re-reading the code I made a fresh copy. I have them copied your code and changed the lines I had to and now it works! Finally I can send an email from a click of a button. (Start with one email address like you say and work from there) The next stage I will be looking at will be for the code to read a spreadsheet of contacts and send an email out to them from text that is from a text box on the form. Sounds simple but I may be back for further assistance. Many thanks for the help so far.

hooby1 said:

Regarding the "Create email macro button" query. All I will have behind this is a macro that copies a list of emails and inserts them into a spreadsheet. I would then like the form to read the spreadsheet and send the emails out in a loop.

Click to expand...

The spreadsheet is an unnecessary step, unless you need them there for other purposes. Here's a start, though it contains some extra bits that aren't required:

http://www.granite.ab.ca/access/email/recordsetloop.htm

Hi pbaldy. Thanks for the reply. When you say the spreadsheet is an unnecessary step, unless you need them there for other purposes. - The contacts will always change and never be the same so it really is button that just will open a query to show the contacts that it will go to. Some macro will save this to an xls which I have working already.
I have had a look at that link and to be honest it's a bit over my head really. I am after really a form that the end user will enter some text into a text box for the email, clicks on a button that will "read" what ever is in the textbox and then sends the email.

My point is that you can loop the query, you don't need to export the query and then loop the spreadsheet.

Hi pbaldy. Many thanks for the reply. I see what you mean now. The only thing I need is to somehow put a delay or a pause in the loop so it doesn't appear I am a spammer sending loads of messages out at once. Say 10 messages every 1 hour. There about 50 messages in total. I think here is a post about that from another poster but if it can be done within the loop that would be good.