Results 1 to 13 of 13
  1. #1
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95

    Improving module code sending emails with specific files to specific recipients

    Hi all,
    I modified a database used for sending multiple emails to a fixed group of recipients. This works but I feel I am using a convoluted method with spaghetti code.

    In my example, I have three files and three recipients -I want to send them to-each recipient only receiving their own file.

    My database has 3 tables (TbEmails1, 2 and 3), 3 queries QryAddress1, 2 and 3 query each table for the email address and 3 further queries-QrySource1, 2 and 3.

    QryAddress1 SQL;
    Code:
    SELECT TbEmails1.EMailAddress AS EMail
    FROM TbEmails1;
    QrySource1 SQL:


    Code:
    SELECT *
    FROM Tbemails1;
    I have a form with a button that fires a macro:
    Code:
    Private Sub Email_Click()
    stDocName = "SendMail"
    DoCmd.RunMacro stDocName
    MsgBox "Files Emailed", vbInformation, "Complete"
    End Sub
    This macro launches 3 functions-EmailMerger1, 2 and 3. EmailMerger1 code:

    Code:
    Public Function SendEMail1()
    Dim db As DAO.Database
    Dim MailList As DAO.Recordset
    Dim MyOutlook As Outlook.Application
    Dim MyMail As Outlook.MailItem
    Dim Subjectline As String
    Dim fso As FileSystemObject
    Dim stDocName As String
    Set fso = New FileSystemObject
    Subjectline$ = "Charles"
    Set MyOutlook = New Outlook.Application
    Set db = CurrentDb()
    Set MailList = db.OpenRecordset("QryAddress1")
    Dim MyRecip As Outlook.Recipient
    Do Until MailList.EOF
    Set MyMail = MyOutlook.CreateItem(olMailItem)
    Set MyRecip = MyMail.Recipients.Add(MailList("email"))
    MyRecip.Type = olTo
    MyMail.Subject = Subjectline$
    MyMail.Attachments.Add "C:\My documents\Charles.XLS", olByValue, 1, "My Displayname"
    Dim MyQuery As QueryDef
    Set MyQuery = CurrentDb.QueryDefs("QrySource1")
    MyQuery.SQL = "select * from Tbemails1 "
    MyQuery.Close
    MyMail.Send
    MailList.MoveNext
    Loop
    Set MyMail = Nothing
    MyOutlook.Quit
    Set MyOutlook = Nothing
    MailList.Close
    Set MailList = Nothing
    db.Close
    Set db = Nothing
    End Function
    Effectively, I can send an existing unique file to an individual for every set of TbEmailX, QryAddressX, QrySourceX and EmailMergerX function.

    I’m trying to simplify it to run from a single tables and queries using a loop, where a table TbEmailsAll containing the filename, recipient and email address using subsequent Queries joined to TbEmailsAll in the query e.g. QryAddressAll, QrySourceAll and EmailMergerAll. Would attach, but even stripped and zipped, its 2.4Mb-just over the limit.
    If anyone can advise/point me in the right direction, I would be very interested.
    Many thanks,
    Mattbro451

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi there, there is much to improve here, can you try to remove some of the tables/ data and compact the file before archiving and see if you can upload it.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Thanks-stripped down version attached.
    Many thanks,
    Mattbro451
    Attached Files Attached Files

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Matt,

    Sorry but the way it is doesn't make much sense, you re probably trying to simplify it for the forums. If each user is supposed to get a file why not have one table with the user name, user email, user file path, user email subject, etc.... Then you would just loop through that table and send all your emails (or through a query based on the table if extra conditions needed).

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Hi Vlad. That's exactly what I want to do, but struggling with coding...I can create a table with user, path and email, write the query, but then code for said paths and loop. It's a bit beyond my level of expertise.....any input appreciated...
    M

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Matt,

    Have a look at the attached sample.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Hi Vlad, It looks amazing, though I am getting a 'runtime error 3521,peration not supported for this type of object' on the modEmail function line;
    Code:
    MyMail.Attachments.Add rMailList("UserFile"), olByValue, 1, StripLast(rMailList("UserFile"))
    Have tried references but no joy. Any idea?
    Thanks for this,
    Mattbro451

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I left your original line in and update it, can you try just
    Code:
    MyMail.Attachments.Add rMailList("UserFile")
    and make sure there is a file in that location with that name.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Hi VIad. I’ve ensured the files exist at the specified location and rerun with code:
    Code:
    MyMail.Attachments.Add rMailList("UserFile")
    Getting the same runtime error 6521 message.

    Hovering over ‘rMailList’ in debug reveals the file location so it’s pointing at the right file, and have ensured that the files exist at the given location.
    Just to be sure its not me, I changed location of docs and added new address on tblUsers. Hovering on debug reveals correct location change so everything is where it should be.
    Any input greatly appreciated.
    Thanks,
    Mattbro451

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry Matt, I think I know the problem, just enclose the file in brackets:

    MyMail.Attachments.Add (rMailList("UserFile"))

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Thanks Vlad. It's fantastic. That removes the bug perfectly.
    There is an odd thing-it generates the emails, I get an Outlook prompt on each message asking if I want to save changes. I choose 'yes' on each, then Outlook saves the emails to the draft folder and then closes down without actually sending them. I thought it might be due to the line
    Code:
    MyMail.Display  'Send
    ...but changing that to;
    Code:
    MyMail.Send
    still gives the same result of emails saved to draft. I will play with it, but if you can see why, would be very interested.
    Many thanks as ever,
    Mattbro451

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Matt,
    Try this please, is the MyOutlook.Quit that triggers the prompt:
    Code:
    Public Sub vcSendAllEMail1()
    Dim db As DAO.Database
    Dim rMailList As DAO.Recordset
    Dim MyOutlook As Object
    Dim MyMail As Object
    Dim Subjectline As String
    Dim fso As FileSystemObject
    Dim stDocName As String
    
    
    
    
    Set fso = New FileSystemObject
    
    
    Set MyOutlook = CreateObject("Outlook.Application")
    MyOutlook.Session.Logon
      
    
    
    Set db = CurrentDb()
    Set rMailList = db.OpenRecordset("tblUsers")
    
    
    'start loop through tblusers
    Do Until rMailList.EOF
        Set MyMail = MyOutlook.CreateItem(0)
        'Set MyRec
        MyMail.To = rMailList("UserEmail")
        MyMail.Subject = rMailList("EmailSubject")
        MyMail.Attachments.Add (rMailList("UserFile"))
        MyMail.Send
        
        rMailList.MoveNext
    Loop
    
    
    
    
    'clean up variables
    Set MyMail = Nothing
    'MyOutlook.Quit
    Set MyOutlook = Nothing
    rMailList.Close
    Set rMailList = Nothing
    db.Close
    Set db = Nothing
    End Sub
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    Mattbro is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    95
    Thats it. Fantastic! Thank you very much indeed Vlad.
    Mattbro451

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

Similar Threads

  1. Replies: 6
    Last Post: 01-26-2017, 06:15 AM
  2. Replies: 5
    Last Post: 11-27-2015, 04:45 PM
  3. Replies: 1
    Last Post: 10-08-2015, 04:27 AM
  4. sending specific details to pdf and email
    By blappy347 in forum Import/Export Data
    Replies: 1
    Last Post: 03-29-2013, 12:46 PM
  5. Replies: 1
    Last Post: 06-26-2012, 09:37 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