Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Help with SendObject VBA

    I'm using SendObject VBA with Access 2010 to automate email distribution to approx 200 managers, all of whom receive a separate email with standardized content and a PDF file attached containing info on their employees.

    The temporary report creation and attachment works perfect, however, the email message itself is being duplicated based upon the count of managers. For example, the first manager receives the email with attachment as expected. The second manager receives an email with the standarized content repeated twice in the email - attached report is fine. The last manger receives an email with the content repeated 200 times (poor bastard) - attached report is fine.

    I've assembled this code from multiple examples in knowledgebases, but something is obviously amiss.

    Thanks in advance for taking the time to read.


    Function MANAGEMENT_REPORTING()

    Dim EmlTo As String
    Dim EmlSubject As String
    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim stDocName As String
    Dim StrAttach As String
    Dim strHTML

    Dim objOutlook As Outlook.Application
    Dim objEmail As Outlook.MailItem
    Set objOutlook = CreateObject("Outlook.application")
    Set objEmail = objOutlook.CreateItem(olMailItem)

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("UsysQry_Mgr")

    With rst
    If .RecordCount <> 0 Then
    Do While Not rst.EOF
    StrAttach = "C:\Temp\Manager Reporting.pdf"
    stDocName = "Manager Reporting"


    DoCmd.OpenReport stDocName, acViewPreview, , "[MANAGERID] ='" & rst("MANAGERID") & "'"
    DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, StrAttach, False
    DoCmd.Close acReport, stDocName
    Set objEmail = objOutlook.CreateItem(olMailItem)
    EmlTo = rst("MGREMAILID")
    EmlSubject = "Manager Reporting"
    strHTML = strHTML & "<br />"
    strHTML = strHTML & "<FONT Face=Arial Size=2>My standardized message goes here.</FONT><br />"
    strHTML = strHTML & "<br />"
    strHTML = strHTML & "<FONT Face=Arial Size=2>My standardized message goes here.<br />"
    strHTML = strHTML & "<br />"
    strHTML = strHTML & "<FONT Face=Arial Size=2><i><b>My standardized message goes here.<br />"
    strHTML = strHTML & "<br />"
    strHTML = strHTML & "<FONT Face=Arial Size=2>Thank you.<br />"


    With objEmail
    .To = EmlTo
    .Subject = EmlSubject
    .Attachments.Add StrAttach
    .Importance = 2 ' olImportanceHigh
    .HTMLBody = strHTML
    .Send 'change to .Send to auto-send all emails

    End With
    .MoveNext
    Kill StrAttach
    Loop
    End If
    End With

    End Function

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by JamesB View Post
    The last manger receives an email with the content repeated 200 times (poor bastard)
    I literally LOL'd when I read that. How embarrassing when you're sat in a room full of people. Oh well.

    Dump the code in some [ CODE ] ... [ /CODE ] tags, and I'll have a look.
    Looking for the perfect beer...

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Infact, simple problem.

    You're not clearing the message at the start of the loop.

    Try:

    Code:
    strHTML = ""
    strHTML = strHTML & "<br />"
    strHTML = strHTML & "<FONT Face=Arial Size=2>My standardized message goes here.</FONT><br />"
    strHTML = strHTML & "<br />"
    strHTML = strHTML & "<FONT Face=Arial Size=2>My standardized message goes here.<br />"
    strHTML = strHTML & "<br />"
    strHTML = strHTML & "<FONT Face=Arial Size=2><i><b>My standardized message goes here.<br />"
    strHTML = strHTML & "<br />"
    strHTML = strHTML & "<FONT Face=Arial Size=2>Thank you.<br />"
    Should do the trick.

    You were simply adding more and more text to the email with every loop iteration. Easily done.
    Looking for the perfect beer...

  4. #4
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Yes, that was certainly a disappointing result after a good deal of work! I had actually used a simplified version of this code on more than one occasion with success, but things went astray when I added the html component.

    The GOOD news is your suggested correction has resolved the issue. I just tested (using only my email ID of course). AND, I still have my job.

    Thank you very much!

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    You're welcome buddy.
    Looking for the perfect beer...

Posting Permissions

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