Page 1 of 7 1234567 LastLast
Results 1 to 15 of 101
  1. #1
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136

    Need help with VB to send multiple emails

    Some time ago I received help to send multiple emails by scrolling through a table and identifying those selected in order to send emails. I need to modify this code but am a rookie when it comes to VB!



    I have made some minor changes to the code that I had but I need some guidance as to how I can send an email with an attached report containing information which is specific to each of the recipients. I have created a Report called 'All Payments to Partners' which has about 30 individual pages each of which contains the information which is specific to the individual partners. This report is based on a Query with the same name which contains 4 fields including one called 'E-mail address'.

    What modifications do I need to make to the following code to achieve my objective? I think the black sections are OK but I need help with what should replace the red section.

    Code:
    Private Sub Command88_Click()
     Dim varItem As Variant      'Selected items
       Dim strWhere As String      'String to use as WhereCondition
       Dim strDescrip As String    'Description of WhereCondition
       Dim lngLen As Long          'Length of string
       Dim strDelim As String      'Delimiter for this field type.
       Dim strDoc As String        'Name of report to open.
       Dim db As Database
       Dim rs As DAO.Recordset
       Dim ToVar As String
       Dim sql As String
       Dim strEmail As String
      
       strDelim = """"            'Delimiter appropriate to field type.
       strDoc = "All Payments to Partners"
       DoCmd.SetWarnings False
     
      'Loop through the ItemsSelected in the list box.
       With Me.lstCategory
           For Each varItem In .ItemsSelected
               strWhere = ""
               strDescrip = ""
              
               If Not IsNull(varItem) Then
                   'Build up the filter from the bound column (hidden).
                   strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                   'Build up the description from the text in the visible column.
                   strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
                   strEmail = """" & .Column(1, varItem) & ""
     
                End If
              
                   'Remove trailing comma. Add field name, IN operator, and brackets.
                       lngLen = Len(strWhere) - 1
                       If lngLen > 0 Then
                           strWhere = "[Name of Mission] IN (" & Left$(strWhere, lngLen) & ")"
                           lngLen = Len(strDescrip) - 2
                           If lngLen > 0 Then
                               strDescrip = "Email: " & Left$(strDescrip, lngLen)
                           End If
                       End If
                      
                       'Report will not filter if open, so close it.
                       If CurrentProject.AllReports(strDoc).IsLoaded Then
                           DoCmd.Close acReport, strDoc
                       End If
                      
                   DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
                   DoCmd.SendObject acSendReport, strDoc, acFormatPDF, strEmail, , , "Receipt Request", "Attached is a request for an acknowledgement for all moneys received through iMap in the past year.", True
                   DoCmd.Close acReport, strDoc
           Next
           
       End With
       DoCmd.Close acReport, strDoc
    DoCmd.SetWarnings True
    End Sub

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I'll tell you how I would do it, being in a similar situation to you. Get a query to generate the results you wish then use word mail merge. Select that query as your data source and compose your email and insert the fields where you want them.

    I don't actually use this for email but that's what its intended for. It can be used similarly to produce individual documents too with an addon. (that's how I use it.)

    Andy.

  3. #3
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Thanks for the suggestion, Andy, but the original coding automates the sending of the emails without the operator having to go through the Mail Merge process. It's that convenience that I am wanting to replicate for the latest exercise but I need help to alter the Visual Basic code.

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    The mail merge process is automated also once you set up the document. As you would need to do with VBA. You just open the word template document and select the query. If its all setup that's it.

  5. #5
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Just by way of clarification - within Access I have generated a report which has on each page the name of the Partner and then a variable number of lines depending on the number of payments made to each in the course of the year. So I want to automatically send the specific page to the appropriate email address. That is what I assume can be done by some adaptation of the VB code that I pasted in my first message.

    I trust someone will be able to help me with this.

  6. #6
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Code:
    within Access I have generated a report
    Just do your report in word.
    Code:
     the name of the Partner and then a variable number of lines depending on the number of payments made to each in the course of the year
    you can reference this query in word. its a lot easier than doing it on a report. you just click from a drop down menu which fields you want where.

    Code:
    So I want to automatically send the specific page to the appropriate email address
    word will use any mailing list associated with each record.

    In access you still have to create the report same as word. word does this with no code and a much simpler interface to achieve the formatting you want.

    This is what a mail merge is. It's standard to do it this way. If you wish to apply VBA you want to look at integration with word.. I have that set up too. Mines totally done by access.
    http://www.kallal.ca/wordmerge/index.html <-- this is the code I use. within the sample database.

    It does exactly what you want just instead of using a report its text from a word document. There is no way you can take out the step of producing the text surrounding record fields. You are doing this now in your report.

    But that addon is still probably a waste of your time it saves seconds compared to about 3 mouse clicks in word... that's just my opinion.

  7. #7
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I actually spent about 3 weeks of my time chopping/changing code/using addons to make my system as easy to use as possible. Although I'm making individual documents as I said before not emailing them. That's why I genuinely am trying to save you time.

    EDIT: I'd just like to add. In access when you update your query, word still links to that query. If its setup exactly the same as previously you just open word and hit go.. that's it!

    If you do need to make changes its as simple as changing any text document.

    Access is designed to work alongside the other Microsoft software. I recommend looking into how you can use excel also. Its great!

  8. #8
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    I am familiar with Mailmerge in MS Word and have used it many times for different things. But how in a Word document can I get the individual pages to show what I have already produced in my Database report? I need my Word document to include the following after the body of the letter -

    Partner Name

    Date Amount
    Date Amount
    Date Amount
    (The number of these lines varies from page to page)
    My query in the Database has a line for each of these entries (including the Partner name in each line). If I simply produce a Mailmerge document (unless there is something within Word Mailmerge that I haven't found yet) I won't be able to set out the Partner name once and then the variable number of lines for the rest of the information. Maybe you could throw some light on that for me? Within Access my report does exactly what I need.

  9. #9
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I'll look into this now for you; I'm assuming:

    partner name is the parent record and you wish to include all child records for costing?

    If you can could you produce an example or just take a screenshot of the one on access so I can see? I'll try and replicate it for you.

  10. #10
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Right so the problem you have is multiple child records relating to the parent. In mail merge it only displays one of the records relating. Depending on the formatting of your document, this might not matter. you could concatenate the child records so they still display and include some formatting.

    alternatively you could look into making custom fields in your query to separate them and keep them all as one record.

    apologies for the misunderstanding of the problem preventing you from doing this. I just thought you were against mail merge haha. I still think this can be fixed though.

  11. #11
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Click image for larger version. 

Name:	Sample.jpg 
Views:	62 
Size:	14.8 KB 
ID:	22755
    Sample above - sorry it's small!

  12. #12
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    http://www.gmayor.com/ManyToOne.htm < this may be what you need.
    http://stackoverflow.com/questions/2...-child-records < failing that; maybe this helps?

  13. #13
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    http://answers.microsoft.com/en-us/o...e-63cad4236398
    the link on the post there seems good also.

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Regarding your OP, what is your objective? I see code that loops through a listbox and send an email. From your description, this is what you desire. What is not working, specifically?

  15. #15
    Baldeagle is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    I'm about to have a look at the websites that Andy has pointed me to.

    But in answer to ItsMe's question -
    The code that I quoted in my first post was given to me for another exercise. How can I adapt it to draw the information from my Query called 'PartnerDonations' which has 4 fields - Partner, Date, Amount & Email. What I need at the end of the exercise is for an email to be sent containing a .pdf version of a single page report containing the information that I copied in my last post to Andy. My report as presently designed has about 30 pages with all the information that I need for all 30 Partners. I need to get the code to send each email with one of those pages attached. I trust that is clear, if not, just ask for further clarification.

Page 1 of 7 1234567 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Send Emails from outlook
    By WhiskyLima in forum Access
    Replies: 8
    Last Post: 08-11-2014, 11:02 AM
  2. Loop Through Query, Send Multiple Emails
    By tdoolittle in forum Reports
    Replies: 6
    Last Post: 05-12-2014, 09:33 PM
  3. Programming Access to Send Emails?
    By BMW150 in forum Access
    Replies: 8
    Last Post: 09-17-2013, 06:14 PM
  4. Send Emails Automatically
    By cbrsix in forum Programming
    Replies: 10
    Last Post: 10-19-2012, 10:52 AM
  5. Replies: 1
    Last Post: 06-26-2012, 09:37 AM

Tags for this Thread

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