Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Question Unanswered: filter and email reports

    Hi

    i have a bit of an annoying one. i am fine with setting up transferspreadsheet/ sendobject type actions however this one goes a little bit further and i need some help.

    At present i have a query "qrymainform" that brings together fields from 2 tables "tblFactorScores" (fields - SU_ID, Title, Score) and "TblSU" (Fields - SU_ID, SUName, EmailAdd). at present i can filter the query based on the name of the SU, and then either export it, or email it (although i have to manually select the name of the person im sending it to).

    What i want to do is develop this further and automate a process that will email the relevant details to the address stored in the EmailAdd field on "TblSU". So all of unit 1's are emailed to person 1, Unit 2's are emailed to Person 2 and so on. I want to set it so that the file is in excel format when it is emailed. I dont want to hard code this in case the name of the SU changes, or the person the file needs to be email to.

    Any help is appreciated.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Paul

  3. #3
    Join Date
    Jan 2008
    Posts
    2

    getting there

    the site is very useful, i can see how it fits together now. Working through on the DAO code

    Dim MyDB As Database, RS As Recordset
    Dim strBody As String, lngCount As Long, lngRSCount As Long

    DoCmd.RunCommand acCmdSaveRecord
    Set MyDB = DBEngine.Workspaces(0).Databases(0)

    Me!txtProgress = Null

    Set RS = MyDB.OpenRecordset _
    ("Email - Outstanding Promos")
    lngRSCount = RS.RecordCount
    If lngRSCount = 0 Then
    MsgBox "No promo email messages to send.", vbInformation
    Else
    RS.MoveLast
    RS.MoveFirst
    Do Until RS.EOF
    lngCount = lngCount + 1
    lblStatus.Caption = "Writing Message " & CStr(lngCount) _
    & " of " & CStr(lngRSCount) & "..."
    strTo = RS!cEmailAddress
    intMessageID = Year(Now) & Month(Now) & Day(Now) & Fix(Timer) & "_MabryMail"

    ' Send the email using some technique or other

    RS.Edit
    RS("cpeDateTimeEmailed") = Now()
    RS.Update
    RS.MoveNext
    Loop

    End If
    RS.Close
    MyDB.Close
    Set RS = Nothing
    Set MyDB = Nothing
    Close

    Me!txtProgress = "Sent " & CStr(lngRSCount) & " emails."
    lblStatus.Caption = "Email disconnected"
    MsgBox "Done sending Promo email. ", vbInformation, "Done"
    lblStatus.Caption = "Idle..."
    Exit Sub

    Some_Err:
    'MousePointer = 0
    MsgBox "Error (" & CStr(Err.Number) & ") " & Err.Description, _
    vbExclamation, "Error!"
    lblStatus.Caption = "Email disconnected"

    Do i need to reference the query im using or the field that it needs to cycle through, and if i need to specify that where would i set it up, combo box on the same form as the combo button, specify field on the query etc?

    Im still working on getting to grips with the theory on how these type of things work.

    Cheers

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Your table or query name would go here:

    Set RS = MyDB.OpenRecordset _
    ("Email - Outstanding Promos")

    Field name anyplace you see RS!... or RS("..."), like:

    strTo = RS!cEmailAddress
    RS("cpeDateTimeEmailed") = Now()

    The one thing I don't like about that sample is that it's a bit of overkill with the status box and such. Not that they're not handy, but they confuse the real issue of looping the recordset and sending the email.
    Paul

Posting Permissions

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