Mass E-Mail Code 9

[Caryisms]

Can I use the code below to send to a list of e-mail addresses? How do I augment what I already have? Thank you.

Public Sub Command0_Click()

Dim otk As Outlook.Application
Dim eml As Outlook.MailItem

Set otk = CreateObject("Outlook.Application")
Set eml = otk.CreateItem(olMailItem)

With eml
.To = [strEmail]
.Subject = "Test Message"
.Body = "This is a test."
.Attachments.Add "C:\test.doc"
.Send
End With

Set eml = Nothing
otk.Quit

End Sub

 

[SgtJarrow]

1. set your .To to a name that is a distobution list

2. create you list and populate the .To
Dim strTo as String
strTo = "robert johnson;george smith;mark jones;"
.To = strTo

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Caryisms]

Robert,

Please excuse me, I am a newbie. The table name is Table1 and the field name is EmailAddress. How do I implement your changes? Thank you.

 

[BuGlen]

Try this:

Public Sub Command0_Click()

    Dim otk As Outlook.Application
    Dim eml As Outlook.MailItem
    Dim rs As Recordset
    Dim strList As String

    ' Open your table and build your distribution list
    Set rs = OpenRecordset("Table1")
    Do While Not rs.EOF
        strList = strList & rs.Fields("EmailAddress").Value & ";"
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
            
    Set otk = CreateObject("Outlook.Application")
    Set eml = otk.CreateItem(olMailItem)
    
    With eml
        .To = strList
        .Subject = "Test Message"
        .Body = "This is a test."
        .Attachments.Add "C:\test.doc"
        .Send
    End With
    
    Set eml = Nothing
    otk.Quit
    
End Sub

This will, however, show the list of recipients to all persons receiving the email. If this is not acceptable, post back and I'll show you another solution.

Hope this helps.

- Glen

Know thy data.

 

[SgtJarrow]

good answer glen....clear and concise. have a star.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Caryisms]

Glen -

When I use your code, I get a compile error. Sub or function not defined. It seems to be the "set rs = OpenRecordset" line.

I apologize for any confusion. I did not mean a distribution list. I need to be able to send the same email body and attachment to many email addresses. I only want them to see their email address in the to field. The addresses are listed in the EmailAddress field of Table1.

Thank you for your help...

 

[SgtJarrow]

Caryisms:

Try the following modification of Glen's code. It basically "walks" through the table one line at a time and sends the same email to each person

Public Sub Command0_Click()

    Dim otk As Outlook.Application
    Dim eml As Outlook.MailItem
    Dim rs As Recordset

    ' Open your table and build your distribution list
    Set rs = New ADODB.Recordset
    
    With rs
        .Open "Table1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
        .MoveFirst
        Do While Not .EOF
            Set otk = CreateObject("Outlook.Application")
            Set eml = otk.CreateItem(olMailItem)
            eml.To = .Fields("EmailAddress")
            eml.Subject = "Test Message"
            eml.Body = "This is a test."
            eml.Attachments.Add "C:\test.doc"
            eml.Send
            .MoveNext
        Loop
        .Close
    End With
      
End Sub

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Caryisms]

Robert,

Thank you very much. I sent you a star... Can I add an id number that is already in the table in the subject line of the email?

ie. Test Message - id 1

 

[SgtJarrow]

sure....

eml.subject = "test message - " & .fields("idfieldname")

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[LeeJames75]

After some extensive searching through the archives, I have stumbled across this.

I am currently working as a Data Analyst/Administrator for a Skatepark in England.

They have a database of 9000 members. And wish to send bulk emails to SKATERS, BOARDERS and BMXERS.

Now I have 3 queries containing email address of SKATERS, BOARDERS and BMXERS.

Can I use the 2nd coded example to send bulk emails with some text regarding special competitions and updates.


Regards

Lee James
England

 

[LeeJames75]

Horray to you all.

The 2nd example with a minor modification has worked a treat.

By replacing eml.TO with eml.BCC the other members will not be able to see the other email addresses.

With the searching and applying the code and the modifying and testing, has only taken me less than 1 hour.

You all get stars

Regards

Lee James
England

 

[Thanyellin]

Newbie here

When I post the following code I get an error "Compile error User defined type not defined" with the first dim statement highlighted. What am I doing wrong?

Public Sub Command0_Click()

Dim otk As Outlook.Application
Dim eml As Outlook.MailItem
Dim rs As Recordset

' Open your table and build your distribution list
Set rs = New ADODB.Recordset

With rs
.Open "Student_Data1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
.MoveFirst
Do While Not .EOF
Set otk = CreateObject("Outlook.Application")
Set eml = otk.CreateItem(olMailItem)
eml.Bcc = .Fields("Email")
eml.Subject = "Test Message"
eml.Body = "This is a test."
eml.Attachments.Add "C:\mailing\Survey Final Version6.doc"
eml.Send
.MoveNext
Loop
.Close
End With

End Sub

 

[PHV]

You have to reference the Outlook object library.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SgtJarrow]

open any code window...click tools -> References. look for and make sure you have Microsoft Outlook x.x Objects checked...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[BuGlen]

Hi Thanyellin,

You could set the reference to Outlook under Tools->References in the VBA IDE. However, I don't generally recommend a direct reference to an Office application as that reference will break if the user has (or installs) a different version of the application.

The best way is to use late binding, which you've already started to do with the CreateObject method. Change your Dim statements for the outlook items to general object references and set a constant for the olMailItem as well:

    Dim otk As Object
    Dim eml As Object
    Dim rs As ADODB.Recordset

    Const olMailItem = 0

That should do the trick.

Hope this helps.

- Glen

Know thy data.

 

[LeeJames75]

Ok.

Having no VBA experience whatsoever and only having used VBA for 5 or 6 modules.

I have been testing this code and have found a problem,
Those with huge distribution lists Please take note !!!

The second example above opens multiple instances of Outlook and then places each email address into each instance of To:

Therefore in reality I have over 9000 email addresses.
Thus 9000 instances of Outlook, HUGE drain on Memory and processor !!!!!

My amended code will only open 1 instance of Outlook, thus placing all the email addresses into 1 string separated with ";" and then place into the Bcc stirng.

Then it will remain open for you to edit if you require, and then all you need to do is click on Send.

If you do need to edit or apply any personal touches.
Replace .DISPLAY with .SEND

Regards
Lee James
England

Public Sub Command4_Click()

Dim otk As Outlook.Application
Dim eml As Outlook.MailItem
Dim rs As Recordset
Dim Strlist As String

' Opens the table/query and builds the distribution list
Set rs = New ADODB.Recordset

With rs
.Open "qry_email", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
.MoveFirst
Set otk = CreateObject("Outlook.Application")
Set eml = otk.CreateItem(olMailItem)

Do While Not .EOF
Strlist = Strlist & .Fields("Email Address").Value & ";"

'This bit is for automatic same report emails -

'eml.Subject = "Test Message"
'eml.Body = "This is another test from access email program."
' use the following to add multiple Attachments
'eml.Attachments.Add "S:\ESC\LJames\invoice.xls"
'eml.Attachments.Add "S:\ESC\LJames\invoice2.xls"

.MoveNext
Loop
.Close
eml.BCC = Strlist

'This will show the email you are sending ideal for manual editing
eml.Display

'This will automatically send the email without showing the email

' eml.Send

End With

End Sub

 

[BuGlen]

That's similar to what I had in the first example with some revision. You could also use the second example with the following changes to send a single email to each person using late binding and without overloading your memory. (note the changes in blue text)

Public Sub Command0_Click()

    Dim otk As [blue]Object[/blue]
    Dim eml As [blue]Object[/blue]
    Dim rs As [blue]ADODB.[/blue]Recordset
    [blue]Const olMailItem = 0[/blue]

    ' Open your table and build your distribution list
    Set rs = New ADODB.Recordset
    [blue]Set otk = CreateObject("Outlook.Application")[/blue]

    With rs
        .Open "Student_Data1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
        .MoveFirst
        Do While Not .EOF
            Set eml = otk.CreateItem(olMailItem)
            [blue]With eml[/blue]
                .Bcc = .Fields("Email")
                .Subject = "Test Message"
                .Body = "This is a test."
                .Attachments.Add "C:\mailing\Survey Final Version6.doc"
                .Send
            [blue]End With[/blue]
            [blue]Set eml = Nothing[/blue]
            .MoveNext
        Loop
        .Close
    End With
    [blue]otk.Quit[/blue]
    [blue]Set otk = Nothing[/blue]
End Sub

The basic idea is to open only 1 instance of Outlook and always clear your objects from memory when your done using them.

Hope this helps.

- Glen

Know thy data.

 

[optjco]

I have tried using a mixture of the code in this thread this is what i have at the moment but i am getting an error message of "Sub or Function not Defined"

Private Sub Form_Current()
 Dim otk As Object
    Dim eml As Object
    Dim rs As ADODB.Recordset
    Const olMailItem = 0

    ' Open your table and build your distribution list
    Set rs = OpenRecordset("tblTaskEMail")
    Do While Not rs.EOF
        strList = strList & rs.Fields("EMailAddress").Value & ";"
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
            Set otk = CreateObject("Outlook.Application")
            Set eml = otk.CreateItem(olMailItem)
            With eml
                .To = strList
                .subject = "Test Message"
                .Body = "This is a test."
                .Display
            End With
            Set eml = Nothing
            .MoveNext
        Loop
        .Close
    End With
    otk.Quit
    Set otk = Nothing
End Sub

Regards

Olly

 

[AccessGuruCarl]

This is for the first code snippet...


Adding these may help resolve some errors individuals are receiving.

[COLOR=blue]Dim db AS Database
  Set db = CurrentDB[/color]
'Change this line
[COLOR=blue]      Set rs = db.OpenRecordset("Table1")[/color]
[COLOR=green] ' Check the options available opening a recordset.   ex. dbOpenSnapshot[/color]

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

I hope this helps...