Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2008
    Posts
    13

    Unanswered: Send Mass Emails

    Hi,
    Hope everyone is doing good.
    this is my first post in the forum and i find it great. i have a database that i use to send mass emails to agents that have cases on thier queue basiclly i run just a macro and macro run a function than emails start sending through outlook that should be open, everything works fine and never have a problem, now what i would like to do is to send mass email in HTML format and also i don't want use Outlook to send the emails i want to use CDO. message using our server and still be able to check if the email sent or not because current i have a flag on the table shows that email sent or not. please find below the code i use to send the modele.

    Thanks.
    ____________________________
    Function SEmail()

    On Error GoTo ErrorHandler

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim Outlook As Outlook.Application
    Dim Email As Outlook.MailItem
    Dim sSubject As String
    Dim sBody As String
    Dim fso As FileSystemObject
    Dim Body As TextStream
    Dim BodyText As String

    'set Outlook
    Set Outlook = New Outlook.Application

    'set up the database and query connections
    Set db = CurrentDb()

    'warnings OFF
    DoCmd.SetWarnings False

    'clear working table
    Call DeleteTable("tSendEmails")

    'warnings OFF
    DoCmd.SetWarnings False

    'add data to working table
    DoCmd.OpenQuery "qaSendEmails"

    'warnings ON
    DoCmd.SetWarnings True

    'set query/recordset
    Set rs = db.OpenRecordset("tSendEmails", dbOpenDynaset)

    'loop through our list of addresses,
    'adding them to e-mails and sending them.
    Do Until rs.EOF

    'create the e-mail
    Set Email = Outlook.CreateItem(olMailItem)

    'address the e-mail
    Email.To = rs("Email")

    'subject of the e-mail
    Email.Subject = "Case on Your Queue"

    'body of the e-mail message
    Email.Body = "Dear " & VBA.Trim(rs("AgentName")) & ":"
    Email.Body = Email.Body & vbCrLf
    Email.Body = Email.Body & vbCrLf
    Email.Body = Email.Body & EmailText(8)
    Email.Body = Email.Body & vbCrLf
    Email.Body = Email.Body & vbCrLf
    Email.Body = Email.Body & rs("CaseID")
    Email.Body = Email.Body & vbCrLf
    Email.Body = Email.Body & vbCrLf
    Email.Body = Email.Body & EmailText(9)

    'flag e-mail as confidential
    Email.Sensitivity = olConfidential

    'flag e-mail as read receipt requested
    'Email.ReadReceiptRequested = True

    'send e-mail
    Email.Send

    Out:
    'move to the next one
    rs.MoveNext

    Loop

    MsgBox "Email Process Complete"

    rs.Close
    db.Close

    Set Email = Nothing
    Set Outlook = Nothing
    Set rs = Nothing
    Set db = Nothing

    Exit Function

    ErrorHandler:
    'Error handler will be used to flag job table when Outlook cannot send an
    'e-mail because e-mail address is invalid

    rs.Edit
    rs("Invalid") = True
    rs.Update
    Resume Out

    End Function
    Last edited by wisdom2004; 09-13-08 at 18:26.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi wisdom

    You've duplicated your post with a subtle difference:
    http://www.dbforums.com/showthread.php?t=1633881
    Do you want to do this in ASP or Access?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2008
    Posts
    13
    I want to do it in Access First.

  4. #4
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Wisdom2004 look at following links:
    http://www.paulsadowski.com/WSH/cdo.htm
    http://www.paulsadowski.com/WSH/massmailer.htm
    Also you can search some OLD topics regarding SMTP here.
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  5. #5
    Join Date
    Sep 2008
    Posts
    13
    Hi,

    Thanks for the links, i did use one methode and when i clicked on the macro to test the emails, i was surprise that i received 240 emails and i just put one email : below find below the code and advise what is the problem, also i couldn't add the status for undeliverable emails :


    Function Smail()

    SendEmail:
    Dim iMsg
    Dim iConf
    Dim Flds
    Dim strHTML
    Dim sSubject As String
    Dim sBody As String
    Dim fso As FileSystemObject
    Dim Body As TextStream
    Dim BodyHTML As String
    Dim Balance As Currency
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

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



    With Flds
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "00007.coop.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
    .Update
    End With


    'set up the database and query connections
    Set db = CurrentDb()

    'warnings OFF
    DoCmd.SetWarnings False

    'clear working table
    Call DeleteTable("tSendEmails")

    'warnings OFF
    DoCmd.SetWarnings False

    'add data to working table
    DoCmd.OpenQuery "qaSendEmails"

    'warnings ON
    DoCmd.SetWarnings True

    'set query/recordset
    Set rs = db.OpenRecordset("tSendEmails", dbOpenDynaset)

    'loop through our list of addresses,
    'adding them to e-mails and sending them.
    Do Until rs.EOF





    'body of the e-mail message
    strHTML = strHTML
    strHTML = strHTML & EmailText(1)
    strHTML = strHTML
    strHTML = "Dear " & VBA.Trim(rs("AgentName")) & ":"
    strHTML = strHTML & EmailText(2)
    strHTML = strHTML & rs("CaseID") & "." & vbCrLf
    strHTML = strHTML & EmailText(20)

    With iMsg
    Set .Configuration = iConf
    .To = rs("Email")
    .from = "Team
    .subject = "Case in your Queue"
    .HTMLBody = strHTML
    .Send
    End With



    Loop

    MsgBox "Email Process Complete"

    rs.Close
    db.Close



    Exit Function


    End Function

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You need to issue an
    Code:
    rs.MoveNext
    George
    Home | Blog

  7. #7
    Join Date
    Sep 2008
    Posts
    13
    Thank you so much Georgev. i just add rs.moveNext and i received only one perfect. my other question was about how to check if the emails went through and also i would to put the name from where the emails is coming plus the email, how can we do that?

    Thanks again.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Do you mean you wish to request a read-receipt?

    Can you clarify what you mean when you say you want to put "the name from where the email is coming plus the email" as this doesn't make sense to me?
    George
    Home | Blog

  9. #9
    Join Date
    Sep 2008
    Posts
    13
    what i mean is to be able to have a flag showing that the email went through, per example if i add invalid email when i will back to the table i will have a flag showing that email is invalid. also when the agent receive the email its only shows from team, i would like them to see the email coming from team but when they hit reply the email will be there too.

    And thank you so much for your support.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •