Sending Email Bulk - i really need help thanks! :) 6

[asafb]

Hello, can someone help me - i want to create a button that when you press it it will take all the emails from all of the records in the recordset of the form and open outlook and it will show in the to box like this

( i have email field in the table )

Record 1 email address george@nyc.rr.com
name george


Record 2 ...

like this in outlook:

To: george@nyc.com, smith@sam.com, george@george.com

anyway to do this?

i already have the SQL statements and everything

Anybody know?

Asaf

 

[pyoungmwl]

Try something like this attached to a command button. It will send the mail via MS Outlook .......

Option Compare Database
Option Explicit
' You need to declare a reference to the Outlook library, and the filesystemobject.
'
' Look in the menu above, and click Tools, then select References
'
' Scroll down the list until you see:-
' Microsoft Scripting Runtime - check it
' Microsoft Outlook Object Library - check it
' Micosoft DAO Object Library - check it

Public Function SendEMail()

Dim Db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim Attachment As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

Set fso = New FileSystemObject

'Ask for the Subject of the e-mail
Subjectline$ = InputBox$("Please enter the subject line for this E-Mail", "Batch E-Mails&quot

'If there's no subject, call it a day.
If Subjectline$ = "" Then
MsgBox "No subject line entered" & vbNewLine & vbNewLine & "Quitting...", vbCritical, "Batch E-Mails"
Exit Function
End If

'Now we need a body for the e-mail
BodyFile$ = InputBox$("Please enter the filename of the body of the message.", "Batch E-Mails&quot

'If there's nothing to say, call it a day.
If BodyFile$ = "" Then
MsgBox "No message body" & vbNewLine & vbNewLine & "Quitting...", vbCritical, "Batch E-Mails"
Exit Function
End If

'Check to make sure the file exists...
If fso.FileExists(BodyFile$) = False Then
MsgBox "The body file isn't where you say it is. " & vbNewLine & vbNewLine & "Quitting...", vbCritical, "Batch E-Mails"
Exit Function
End If

'Since we got a file, we can open it up.
Set MyBody = fspenTextFile(BodyFile, ForReading, False, TristateUseDefault)
'and read it into a variable.
MyBodyText = MyBody.ReadAll
'and close the file.
MyBody.Close

'Is there an attachment?
Attachment$ = InputBox$("Please enter the filename of any attachment to the message.", "Batch E-Mails&quot
If Not Attachment$ = "" Then
'Check to make sure the file exists...
If fso.FileExists(Attachment$) = False Then
MsgBox "The attachment file isn't where you say it is. " & vbNewLine & vbNewLine & "Quitting...", vbCritical, "Batch E-Mails"
Exit Function
End If
End If


' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application

' Set up the database and query connections

Set Db = CurrentDb()
Set MailList = Db.OpenRecordset("tblEMails&quot

'Loop through our list of addresses, adding them to e-mails and sending them if the FLAG is set

Do Until MailList.EOF
'Check the Flag
If Not MailList("EMail Flag&quot Then GoTo EMailLoop

' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
' This addresses it
MyMail.To = MailList("E-Mail&quot
'This gives it a subject
MyMail.Subject = Subjectline$
'This gives it the body
MyMail.Body = MyBodyText
'This gives it the attachment
If Not Attachment$ = "" Then
MyMail.Attachments.Add Attachment$
End If

'This sends it!
MyMail.Send

'And on to the next one...
EMailLoop:
MailList.MoveNext

Loop

'Cleanup

Set MyMail = Nothing

'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
Db.Close
Set Db = Nothing

End Function

 

[asafb]

hello i tried attaching this to the button but it always says "general" under vba; can you help out? I attached it as event procedure but it's not letting me save.

where do i paste this code exactly?

thanks
asaf

 

[asafb]

Dear pyoungmwl,

thank you so much! it works perfectly.
asaf

 

[asafb]

dear pyoungmwl, anyway to do it so that it sends just one email with all the addresses in the TO field?

whats happening is that the dialog box opens up and says "Trying to send automatically" it does it like 100 times depending on how many email addresses.... can you re-write the code so it says

to: asaf@nyc.com, test@nyc.com, etc. etc???

instead of separate emails?

thanks asaf

 

[pyoungmwl]

Dear asafb,

Glad the code did the trick ....

Not sure whether you can generate just one e-mail with all of the recipients in the "To" box, but I appreciate that it's a bit of a pain that Outlook keeps on warning you that another application is trying to send mail.

If you change "Mymail.Send" to "Mymail.Display", what happens is that all of the outgoing e-mails are opened in separate windows very quickly, and you can then click "Send" on each one. It's not perfect, but it eliminates the few seconds delay that otherwise happens on each individual e-mail.

 

[glenmac]

pyoungmwl a Star for you I'm using your code too. Thanks!!

 

[asafb]

Dear pyoungmwl, thanks for your advice. But the problem with *that* is what if i have like 100 emails? I have to press SEND 100 times? Youch!!

 

[asafb]

Why cant we take the data of an email table, lets say we have 100 emails in a table called "emailtable", and then somehow copy that data into teh TO filed in VBA code, anyway to try this?

 

[pyoungmwl]

Dear asafb,

Try this; the code has been slightly changed around to concatenate a list of users. I've also put in a default .txt file name for the e-mail body and a default .doc file for an attachment.


Option Compare Database
Option Explicit
' You need to declare a reference to the Outlook library, and the filesystemobject.
'
' Look in the menu above, and click Tools, then select References
'
' Scroll down the list until you see:-
' Microsoft Scripting Runtime - check it
' Microsoft Outlook Object Library - check it
' Micosoft DAO Object Library - check it

Public Function SendEMailTest()

Dim Db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim Attachment As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim ToList As String

Set fso = New FileSystemObject

'Ask for the Subject of the e-mail
Subjectline$ = InputBox$("Please enter the subject line for this E-Mail", "Batch E-Mails&quot

'If there's no subject, call it a day.
If Subjectline$ = "" Then
MsgBox "No subject line entered" & vbNewLine & vbNewLine & "Quitting...", vbCritical, "Batch E-Mails"
Exit Function
End If

'Now we need a body for the e-mail
BodyFile$ = InputBox$("Please enter the filename of the body of the message.", "Batch E-Mails", "C:\Guides.txt&quot

'If there's nothing to say, call it a day.
If BodyFile$ = "" Then
MsgBox "No message body" & vbNewLine & vbNewLine & "Quitting...", vbCritical, "Batch E-Mails"
Exit Function
End If

'Check to make sure the file exists...
If fso.FileExists(BodyFile$) = False Then
MsgBox "The body file isn't where you say it is. " & vbNewLine & vbNewLine & "Quitting...", vbCritical, "Batch E-Mails"
Exit Function
End If

'Since we got a file, we can open it up.
Set MyBody = fspenTextFile(BodyFile, ForReading, False, TristateUseDefault)
'and read it into a variable.
MyBodyText = MyBody.ReadAll
'and close the file.
MyBody.Close

'Is there an attachment?
Attachment$ = InputBox$("Please enter the filename of any attachment to the message.", "Batch E-Mails", "C:\Guides.doc&quot
If Not Attachment$ = "" Then
'Check to make sure the file exists...
If fso.FileExists(Attachment$) = False Then
MsgBox "The attachment file isn't where you say it is. " & vbNewLine & vbNewLine & "Quitting...", vbCritical, "Batch E-Mails"
Exit Function
End If
End If


' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application

' Set up the database and query connections

Set Db = CurrentDb()
Set MailList = Db.OpenRecordset("tblEMails&quot

'Loop through our list of addresses, adding them to e-mails and sending them if the FLAG is set

Do Until MailList.EOF
'Check the Flag
If Not MailList("EMail Flag&quot Then GoTo EMailLoop
' This addresses it
ToList = ToList & MailList("E-Mail&quot & ";"
'And on to the next one...
EMailLoop:
MailList.MoveNext

Loop


' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
' This addresses it
MyMail.To = ToList
'This gives it a subject
MyMail.Subject = Subjectline$
'This gives it the body
MyMail.Body = MyBodyText
'This gives it the attachment
If Not Attachment$ = "" Then
MyMail.Attachments.Add Attachment$
End If

'This sends it!
MyMail.Display


'Cleanup

Set MyMail = Nothing

'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
Db.Close
Set Db = Nothing

End Function

 

[asafb]

yes it worked! you are fantastic! thank you so much!

asaf

 

[SpankYou]

Hey pyoungmwl,

Cheers for the code helped me out greatly have a star on me!

Cheers

Sam

 

[sammylou]

would like to use this code but although I have ticked the boxes under Tools and References, I get an error when compiling saying "user defined type not defined [fso As FileSystemObject]

How do I declare a reference to the Outlook library, and the filesystemobject apart from ticking the boxes??

 

[pyoungmwl]

Have you got the Microsoft DAO 3.6 Object Library ticked? There are several different versions of this library.

 

[JonM4024]

This is a great example for making a text message. I am wondering if you can place a microsoft word document with pictures into a message. It does not seem to work with the current code.

 

[pyoungmwl]

The code sample allows one file to be attached - it doesn't really matter what it is. If you want to embed pictures within a Word document and then attach the word document to the e-mail it should still work.

 

[JonM4024]

Thanks for responding so quickly. Unfortuneately I am afraid that I did not explain my problem thoroughly enough. I have a word document template. When creating an email I use word to insert images and text. Then I use the word document as an email template. The document is inserted "as text". Is there a way that I can do this using your code?

 

[pyoungmwl]

Sorry - it's not that clever! The code just allows you to use a text-only e-mail template, such as a Notepad file.

 

[JamesTV]

I've tried testing the code. I created a new database with a table called "tble-mails" and included one field with 10 e-mail addresses. The code works fine for entering the message subject line, the message text file, the message attachment however I then get a run-time error "3265" - item not found it this collection. It occurs when the code loops through the list, adding them to e-mails and sending them if the flag is set. However I'm not sure how to set the flag. Is there something extra I would need to do to the e-mail field in the table to mark the "flag" as set??

The problem begins from the following code:

If Not MailList("EMail Flag&quot Then GoTo EMailLoop

Any help appreciated. Thank you.

 

[pyoungmwl]

The EMailFlag is simply a Yes/No type field in the table which can be set or cleared by ticking/unticking a box on a form bound to the table. It simply allows the user more control in selecting to whom the emails are to be sent rather than only offering the option to send to everyone in the table.