Results 1 to 10 of 10
  1. #1
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118

    Send report based on Email selections

    I like this form I have that allows me to send attachment when email is entered.
    However, I would like to have a list box of Name and Email Address.
    When selected the email address is now set (same as before but just as a drop down)
    I type my message etc...

    But the attachment part I would like to send a specific report (that connects to that email address) so that it will only send jobs connected to that person.

    Here is the code below: Can you help me readjust it to match what I'm looking for?

    Private Sub Email_Output_Click()
    '
    ' Email API Outlook example programming code
    ' Send email from to Outlook
    '
    Select Case Me.Email_Output_Option
    Case 1
    Dim mess_body As String
    Dim rst As DAO.Recordset
    Dim appOutLook As Outlook.Application


    Dim MailOutLook As Outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    Set rst = Form_F_People_Mailings.RecordsetClone
    rst.MoveFirst
    Do While Not rst.EOF
    If IsNull(rst!Email) Then
    MsgBox "skipping " & _
    Form_F_People_Mailings.LastName & _
    " no email address."
    GoTo skip_email
    End If
    mess_body = "Dear " & rst!Salutation & " " & _
    rst!LastName & "," & _
    vbCrLf & vbCrLf & Me.Mess_Text
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook
    .To = rst!Email
    .Subject = Me.Mess_Subject
    .Body = mess_body
    If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
    .Attachments.Add (Me.Mail_Attachment_Path)
    End If
    'next line would let MS Outlook API send the note
    'without storing it in your sent bin
    '.DeleteAfterSubmit = True
    .Send
    End With
    skip_email:
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Case 2
    ...

    Private Sub Command20_Click()

    End Sub

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    1) This code is currently looping through the recordset that has been
    attached to the form. In order to use a listbox on the form to
    limit the email output to a single recipient, then you would use the
    AfterUpdate event on the listbox to filter the recordset to only
    the one recipient. (Back up your database, copy the form to a new form,
    and try this on the new form to see whether it gets you what you want.)

    2) Your attachment information needs to come from somewhere. If you
    want to automatically select the reports relevant to the person, then
    you'll need to tell us how you know what's relevant, before we can give
    you any suggestions on how to code that function.

  3. #3
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    2) the reports is one standard report that is saved u:/ etc

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Pretend that I'm some not-particularly-bright guy on the Internet who has never been in your office and has no idea what you are talking about, because he doesn't know your workflow and can't even hear the inflections in your voice.

    You said this -
    But the attachment part I would like to send a specific report (that connects to that email address) so that it will only send jobs connected to that person.
    This sounds like you want the system to automatically select which report or reports are right for a particular recipient, and to attach the reports automatically.

    If there's only one report, always the same place, and the user is just picking who to send it to, then there's no need to change that part of the code. On the other hand, if different people get different reports, then the system designer has to know more about the people and more about the reports, before he/she could code such a process.

  5. #5
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Right just one report but the attachment is only for individuals that have selected a certain type if job. I have a drop down of four choices: "areas) for examples.
    So only the people that selected a certain area get the attachment.

    Any idea how I would change the code to reflect this ?

  6. #6
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    You already have a spot in the code that looks like this:
    Code:
    If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
       .Attachments.Add (Me.Mail_Attachment_Path)
    End If
    That is the spot that you need to change.

    Again, I'm not there looking over your shoulder, so I don't understand the exact meaning of the phrase " individuals that have selected a certain type if job" or "the people that selected a certain area".

    Do you mean the user of the screen will select an "area" dropdownbox as well as the recipient dropdownbox, and the area box will determine whether the recipient gets an attachment? Or do you mean that the recipients will have selected an area at some prior point, and that area information will be stored in the database for each recipient?

  7. #7
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Meaning if technician go to a job that is for govt then based on these types selected from drop down when I send this one attachment should be posted to that job type only that has that selected item.

    Makes sense
    Ex: Sam the technician has a job that is for govt (one of selections in drop box) when details are sent because he had this job that one attachment should be sent as well.

    Not sure how to bring these specifics in the code

  8. #8
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Okay, so YOU, wnicole, are the user.

    Sam, the technician, is (in this example) the recipient of the email.

    The email is about a job that Sam is supposed to do.

    If the job is a "govt" job, then one specific attachment will always be sent.

    That "govt" attachment will always be stored in one specific place.

    If the job is some other kind of job, either a different attachment or no attachment will be sent.

    Do I have that all correct?

    If so, then let's assume that the attachments will be stored on your LAN somewhere on the U drive. Let's also assume that your dropdownbox on your form is called lstArea, and that the two values for lstArea that have attachments on the U drive are "govt" and "misc". Replace this code:
    Code:
       If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
          .Attachments.Add (Me.Mail_Attachment_Path)
       End If
    with this code:
    Code:
       SELECT CASE Me.lstArea
    
           CASE "govt"
     'attach the government attachment
             .Attachments.Add ("U:/FullPathToGovtTAttachment.PDF")
    
           CASE "misc"
     'attach the miscellaneous attachment
             .Attachments.Add ("U:/FullPathToMiscAttachment.DOC")
    
           CASE ELSE
            'nothing to do
    
       END SELECT
    Obviously, you need to use the exact name of the dropdownbox, the exact value of the values of the dropdownbox, and the exact path to the attachments for each value, in the above code. That's the spots I've marked in bold italic red.

  9. #9
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742

    Do This Instead

    Alternately, or maybe preferably, you could put code like the following in the dropdownbox lstArea's AfterUpdate event, and leave the other code alone:
    Code:
    Private Sub lstArea_AfterUpdate
    
       SELECT CASE Me.lstArea
    
           CASE "govt"
    'attach the government attachment
             Me.Mail_Attachment_Path = "U:/FullPathToGovtTAttachment.PDF"
    
           CASE "misc"
    'attach the miscellaneous attachment
             Me.Mail_Attachment_Path = "U:/FullPathToMiscAttachment.DOC"
    
           CASE ELSE
            'nothing to do
             Me.Mail_Attachment_Path = "<nothing>"
       END SELECT
    
    End Sub
    Make the same changes I mentioned above.
    Obviously, you need to use the exact name of the dropdownbox, the exact value of the values of the dropdownbox, and the exact path to the attachments for each value, in the above code. That's the spots I've marked in bold italic red.
    The reason that should work is because it is putting the information about the desired attachment in the same place where the prior code is expecting it to be. I'd probably do that, not knowing anything else about your application.

  10. #10
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Ok I will try it and let you know how it goes
    thanks!!!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. "Send Email" button on Query-Based Report
    By athyeh in forum Programming
    Replies: 5
    Last Post: 07-05-2013, 12:44 PM
  2. Send Report as body of email
    By chris.williams in forum Reports
    Replies: 3
    Last Post: 09-15-2012, 09:43 AM
  3. Send Report and Attachments in Email
    By Pimped in forum Programming
    Replies: 1
    Last Post: 06-21-2011, 02:51 AM
  4. Replies: 4
    Last Post: 04-13-2011, 10:11 AM
  5. How to send filtered report via email
    By degras in forum Reports
    Replies: 4
    Last Post: 03-31-2011, 09:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums