none
Sending MS Access Current Recordset to Table List of Recipients RRS feed

  • Question

  • Hello, I have an access database that I currently have emailing pdf reports to individuals through the macro option. However, when emails change or get added frequently, this gets hard to manage and a new front end needs to be distributed too often.

    I created a table list of email addresses and would like to go that method, but not sure how to create the needed VBA code required. We are using Lotus Notes 9 and MS Access 2013.

    the email table name is tblEmail  that contains multiple email addresses.

    The report that is generated could be named rptTest for this purpose

    I have a query that is created based up the current ID of the record. ie;

    [Forms]![frmName]![Current_RecordID]

    Can someone please help me with the code needed for an onClick event. I've looked all over and cannot seem to get this working correctly. Any help would be greatly appreciated!

    Thank you,
    Kelly



    • Edited by KellyRosal Saturday, October 3, 2015 6:38 PM changed verbage
    Saturday, October 3, 2015 6:21 PM

Answers

  • Hi Kelly,

    Thanks for posting the code. Try replacing the following line:

    DoCmd.SendObject acReport, "qry_NoStock", "PDFFormat(*.pdf)", "EMail1.Test1@email.com;EMail2.Test2@email.com;EMail3.Test3@email.com", "", "", "No Stock - PDC", "Attached is the No Stock Component Report - PDC", True, ""

    With this one:

    DoCmd.SendObject acReport, "qry_NoStock", "PDFFormat(*.pdf)", SimpleCSV("SELECT EmailAddress FROM tblEmail",";"), "", "", "No Stock - PDC", "Attached is the No Stock Component Report - PDC", True, ""

    Tuesday, October 6, 2015 12:34 AM
  • Hi Kelly. If I understand it correctly, for the first part of your code, you might be able to use this simple function instead: SimpleCSV() 

    Please let me know if you need help with using it. You shouldn't have to modify the function at all. Cheers!

    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:37 AM
    Saturday, October 3, 2015 11:17 PM
  • Hi Kelly. Yes, that's exactly what the SimpleCSV() function will do, so you won't have to modify it at all. You just use it properly as instructed. If the instruction is not clear, please let me know. If you try to use the code you posted earlier, you'll have to modify it to fit your table structure. The SimpleCSV() function doesn't care about your table structure. You just pass it a SQL statement based on your table or query, and it will return the list of email addresses for you automatically. Cheers!
    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:38 AM
    Sunday, October 4, 2015 2:39 AM
  • Hi Kelly. What does your original code to send out the email look like. That is what you'll need to modify by inserting a call to the SimpleCSV() function. For example, if you previously had something like the following:

    DoCmd.SendObject acSendReport, "ReportName", acFormatPDF, Me.Email, , , "Subj", "Body", True

    You can now change it to something like this:

    DoCmd.SendObject acSendReport, "ReportName", acFormatPDF, SimpleCSV("SELECT EmailAddress FROM tblEmail WHERE EmailGroup=" & Me.EmailGroup, ";"), , , "Subj", "Body", True

    Hope that helps...

    Reminder: You don't need to modify the code for the SimpleCSV() function. You just use it like I showed above. Cheers!

    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:38 AM
    Sunday, October 4, 2015 6:28 PM
  • Hi DB Guy,

    OK, I get it. I do need to figure out how to get the sending of the email working though. I'm have a little trouble with the code for that. I will see if I can get this working.

    I surely appreciate it!

    Thank you,

    Kelly


    Hi Kelly. But I thought you said that you already have a macro that sends the email out to one person? If so, all you have to do is modify it to use the SimpleCSV() function, so that it will send the email to multiple addresses.
    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:38 AM
    Sunday, October 4, 2015 9:53 PM
  • Hi Kelly. All code  should be in the front end and not the back end. You won't need to change the front end each time you change the records in the table because your code will not hard code the names or e-mail addresses. If you use the technique I showed you, it will work using either a macro or VBA.  If you post your macro, we can  help you adjust it.
    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:38 AM
    Monday, October 5, 2015 10:34 AM
  • Hi DB Guy,

    Wow! that worked perfect!!

    It did exactly what you said it would, very nice!

    I will try it with the real information, but that worked so good I cannot believe it. I've been looking for something to accomplish this task for quite some time and have found nothing that worked, but yours did the job perfectly.

    Thank you so much DB Guy!

    Kelly

    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:15 AM
    Tuesday, October 6, 2015 1:41 AM
  • Hi DB guy,

    That worked really great, I had no problems directing it to my query.

    I really like how you made this function. I was able to manipulate the output of the filenames of the pdf file by using your code.

    The project is completed now and I sincerely appreciate your help!

    Thank you,

    Kelly

    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:12 AM
    Wednesday, October 7, 2015 10:40 PM
  • I can do this, but I wonder why from my PC at work, the exact same front end works every time?

    That's why I wanted to isolate the code. I asked if your computer was any different from theirs, and you said they're the same. So, we need to check the code on their machines now because you said the old code worked on their machines. We just need to check that it still does. If not, then it's their machines - not yours. It's hard to explain without seeing what's happening. Sorry...
    • Marked as answer by KellyRosal Monday, October 12, 2015 10:29 AM
    Friday, October 9, 2015 4:16 PM
  • Hi DB Guy,

    Well, I figured out what the issue was. The original code in the macro had the record being saved before it would generate the email. I'm not sure how I missed that, so all is working great now.

    Thank you for all you help!

    Kelly

    • Marked as answer by KellyRosal Monday, October 12, 2015 10:29 AM
    Monday, October 12, 2015 10:29 AM

All replies

  • Hi Kelly. Are you trying to look up one email address from the table based on the person's record ID? If so, you should be able to use the DLookup() function in your macro. For example:

    To: =DLookup("EmailField","TableName","RecordID=" & Forms!frmName.Curret_RecordID)

    Hope that helps... Please let us know if you're actually trying to pull multiple email addresses from the table.

    Saturday, October 3, 2015 7:14 PM
  • Hello, I need to send the report to multiple email addresses. I also have separate email grouping that this will be needing. I have created (2) tables, 1 for the group and 1 for the email addresses. so the members of the group will have an associated group ID. There will be a button on the form that will be linked to each group for the emailing.

    I did find a good example on this website of how I can maybe accomplish the task of sending an email to multiple addresses.

    https://social.msdn.microsoft.com/Forums/office/en-US/31ce9c90-3e7f-44f9-9817-d42be50431b7/sending-a-report-to-multiple-email-recipients?forum=accessdev

    First the code for the sub routine for querying the email addresses;

    Sub fillRecipients(ByRef stTo as String, ByRef, stCC as String)
        Dim rs as DAO.Recordset
    	
    	Set rs = CurrentDB.OpenRecordset("Query1")
    	do until rs.EOF
    		For each f in rs.Fields
    			if left(f.name,5) = "Email" Then
    				IF len(nz(f.value)) > 0 then stTo = stTo & ";" & f.value
    			end if
    			if left(f.name,2) = "CC" Then
    				IF len(nz(f.value)) > 0 then stCC = stCC & ";" & f.value
    			end if
    		Next
    		rs.MoveNext
    	loop
    	
    	rs.Close
    	set rs = nothing
    	if stTo <> "" then stTo = Mid(stTo,2)
    	if stCC <> "" then stCC = Mid(stCC,2)
    End Sub

    ---------------------------------------------------------------------------------------------------------

    Next the code for the emailing procedure;

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click
    
    Dim stDocName As String
    Dim stToName As String
    Dim stCCName As String
    Dim stSubject As String
    Dim stMessage As String
    
    stDocName = "Larmst5"
    fillRecipients stToName, stCCName
    stSubject = "Penske Truck Leasing ACH Notification"
    stMessage = "Please open the attached file to view the invoices to be pulled."
        
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "1", acViewNormal, acEdit
        DoCmd.Close acQuery, "1"
        DoCmd.OpenQuery "2", acViewNormal, acEdit
        DoCmd.Close acQuery, "2"
        DoCmd.OpenQuery "3", acViewNormal, acEdit
        DoCmd.Close acQuery, "3"
        DoCmd.OpenQuery "4", acViewNormal, acEdit
        DoCmd.OpenQuery "Query1", acViewNormal, acEdit
        DoCmd.SendObject acSendReport, stDocName, acFormatPDF, stToName, stCCName, "", stSubject, stMessage, True, ""
    
    
    Exit_Command0_Click:
        Exit Sub
    
    Err_Command0_Click:
        MsgBox Err.Description
        Resume Exit_Command0_Click
        
    End Sub

    I didn't have a chance to test it yet, but if I can figure out what changes I need to make for my database, it looks like it might work.

    Hopefully, I can get this working.

    Thank you,

    Kelly

    Saturday, October 3, 2015 11:09 PM
  • Hi Kelly. If I understand it correctly, for the first part of your code, you might be able to use this simple function instead: SimpleCSV() 

    Please let me know if you need help with using it. You shouldn't have to modify the function at all. Cheers!

    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:37 AM
    Saturday, October 3, 2015 11:17 PM
  • Hello,

    I guess I'm not understating the code correctly for the sub routine. I was thinking that the sub went through the list of email addresses so instead of only selecting (1) email address, it would select multiple addresses for the email. Is that what you are meaning that your code could be used for?

    If so, that would work be great.

    If not and I need to use the above sub routine, I have a question on how to insert the sub routine within the VBA. Do I just place it above the procedure code?

    Thank you for your help!

    Kelly

    Sunday, October 4, 2015 2:33 AM
  • Hi Kelly. Yes, that's exactly what the SimpleCSV() function will do, so you won't have to modify it at all. You just use it properly as instructed. If the instruction is not clear, please let me know. If you try to use the code you posted earlier, you'll have to modify it to fit your table structure. The SimpleCSV() function doesn't care about your table structure. You just pass it a SQL statement based on your table or query, and it will return the list of email addresses for you automatically. Cheers!
    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:38 AM
    Sunday, October 4, 2015 2:39 AM
  • OK, I got your code inserted into a Public function, I guess I need to know where to point it to my query or where the sql information is placed.

    SimpleCSV("SELECT CustomerName FROM tblCustomers WHERE CompanyID=" & [CompanyID])

    Would it be in here possibly?

    Public Function SimpleCSV(strSQL As String, _
    Optional strDelim As String = ",") As String

    Thank you,

    Kelly

    Sunday, October 4, 2015 3:42 AM
  • In my case, the sql would be;

    ("SELECT EmailAddress FROM tblEmail WHERE EmailID=" & [EmailID])

    I think this is correct, my table is the following;

    EmailID

    EmailGroup

    FirstName

    LastName

    EmailAddress

    Sunday, October 4, 2015 4:03 AM
  • Hello, it looks like I will need your assistance in getting tis to work.

    Thank you,

    Kelly

    Sunday, October 4, 2015 12:52 PM
  • Hi Kelly. What does your original code to send out the email look like. That is what you'll need to modify by inserting a call to the SimpleCSV() function. For example, if you previously had something like the following:

    DoCmd.SendObject acSendReport, "ReportName", acFormatPDF, Me.Email, , , "Subj", "Body", True

    You can now change it to something like this:

    DoCmd.SendObject acSendReport, "ReportName", acFormatPDF, SimpleCSV("SELECT EmailAddress FROM tblEmail WHERE EmailGroup=" & Me.EmailGroup, ";"), , , "Subj", "Body", True

    Hope that helps...

    Reminder: You don't need to modify the code for the SimpleCSV() function. You just use it like I showed above. Cheers!

    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:38 AM
    Sunday, October 4, 2015 6:28 PM
  • Hi DB Guy,

    OK, I get it. I do need to figure out how to get the sending of the email working though. I'm have a little trouble with the code for that. I will see if I can get this working.

    I surely appreciate it!

    Thank you,

    Kelly

    Sunday, October 4, 2015 6:58 PM
  • Hi DB Guy,

    OK, I get it. I do need to figure out how to get the sending of the email working though. I'm have a little trouble with the code for that. I will see if I can get this working.

    I surely appreciate it!

    Thank you,

    Kelly


    Hi Kelly. But I thought you said that you already have a macro that sends the email out to one person? If so, all you have to do is modify it to use the SimpleCSV() function, so that it will send the email to multiple addresses.
    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:38 AM
    Sunday, October 4, 2015 9:53 PM
  • Hi DB Guy,

    Yes, I have a macro that I've been using to send out emails to selected individuals. However, the macro is setup in the front end and I want to have the function work in the back end by using a table list of users. This way I don't have to keep handing out a new front end to everyone every time a new user needs to be added or removed. I wonder if I convert the macro to vba if that would work for me?

    Thank you,

    Kelly

    Monday, October 5, 2015 9:24 AM
  • Hi Kelly. All code  should be in the front end and not the back end. You won't need to change the front end each time you change the records in the table because your code will not hard code the names or e-mail addresses. If you use the technique I showed you, it will work using either a macro or VBA.  If you post your macro, we can  help you adjust it.
    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:38 AM
    Monday, October 5, 2015 10:34 AM
  • Hi DB Guy,

    I have attached the macro code converted to vba, I wasn't sure how to give you the macro itself. And yes, I'm aware that this needs to be in the front end and the table will be in the back end. I have the table information below the code.

    Function mcr_SendPDC()
    On Error GoTo mcr_SendPDC_Err
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.SendObject acReport, "qry_NoStock", "PDFFormat(*.pdf)", "EMail1.Test1@email.com;EMail2.Test2@email.com;EMail3.Test3@email.com", "", "", "No Stock - PDC", "Attached is the No Stock Component Report - PDC", True, ""

    mcr_SendPDC_Exit:
        Exit Function
    mcr_SendPDC_Err:
        MsgBox Error$
        Resume mcr_SendPDC_Exit

    End Function

    _________________________________________________________________________________

    Table information;

    tblEmail

    <tfoot></tfoot>
    tblEmail
    EmailID FirstName LastName EmailAddress
    1 Email1 Test1 EMail1.Test1@email.com
    2 Email2 Test2 EMail2.Test2@email.com
    3 Email3 Test3 EMail3.Test3@email.com

    I thank you so much for your help!

    Sincerely,

    Kelly


    • Edited by KellyRosal Monday, October 5, 2015 11:58 PM edit
    Monday, October 5, 2015 11:57 PM
  • Hi Kelly,

    Thanks for posting the code. Try replacing the following line:

    DoCmd.SendObject acReport, "qry_NoStock", "PDFFormat(*.pdf)", "EMail1.Test1@email.com;EMail2.Test2@email.com;EMail3.Test3@email.com", "", "", "No Stock - PDC", "Attached is the No Stock Component Report - PDC", True, ""

    With this one:

    DoCmd.SendObject acReport, "qry_NoStock", "PDFFormat(*.pdf)", SimpleCSV("SELECT EmailAddress FROM tblEmail",";"), "", "", "No Stock - PDC", "Attached is the No Stock Component Report - PDC", True, ""

    Tuesday, October 6, 2015 12:34 AM
  • Hi DB Guy,

    Wow! that worked perfect!!

    It did exactly what you said it would, very nice!

    I will try it with the real information, but that worked so good I cannot believe it. I've been looking for something to accomplish this task for quite some time and have found nothing that worked, but yours did the job perfectly.

    Thank you so much DB Guy!

    Kelly

    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:15 AM
    Tuesday, October 6, 2015 1:41 AM
  • Hi Kelly. You're welcome. Please let us know if you need more help. I know you want to filter the e-mail by groups, so let us know if you get stuck with that one. Good luck.
    Tuesday, October 6, 2015 3:12 AM
  • Hi DB guy,

    OK, I created a couple more tables

    ie;

    tblGroup

    tblGroupName

    tblEmail

    I did this so I can query by the GroupID and separate what emails are selected. My question is, I'm thinking I should be able to use your code on a query, instead of a table.

    DoCmd.SendObject acReport, "qry_NoStock", "PDFFormat(*.pdf)", SimpleCSV("SELECT EmailAddress FROM qryPDC",";"), "", "", "No Stock - PDC", "Attached is the No Stock Component Report - PDC", True, ""

    Will this work if I change the code to reference the query as shown above. If so, then I'm all good.

    Thank you again, very great!

    Sincerely,

    Kelly

    Tuesday, October 6, 2015 9:41 AM
  • Hi Kelly,

    Yes, you can use a query as long as it's not a parameter query.

    Tuesday, October 6, 2015 2:37 PM
  • Hi DB guy,

    That worked really great, I had no problems directing it to my query.

    I really like how you made this function. I was able to manipulate the output of the filenames of the pdf file by using your code.

    The project is completed now and I sincerely appreciate your help!

    Thank you,

    Kelly

    • Marked as answer by KellyRosal Thursday, October 8, 2015 9:12 AM
    Wednesday, October 7, 2015 10:40 PM
  • Hi Kelly,

    I am glad your issue has been resolved, and I suggest you mark the helpful reply as answer to close this thread.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, October 8, 2015 5:08 AM
  • Hi DB guy,

    I have another question or issue on this topic. When I send the email to the specified groups, the data from the query is in the report. However, when they send it, the attachment is blank. Do you know what would cause this?

    Also, when I relink the tables to the backend, it ask me for a location for every table instead of doing all the tables at one time like it has in the past. Is my database having a problem now or is there a Library that needs to be added to the database, that they don't have installed?

    When I do the same front end from my PC at work, the data is populated, but theirs is not.

    Very odd...

    Kelly

    Friday, October 9, 2015 9:59 AM
  • Hi Kelly,

    Are they using the same version as you? Do they have all the updates? Do they have full permission to the backend folder?

    Friday, October 9, 2015 1:22 PM
  • Hi DB guy,

    Yes, we are all running the 2013 version and have full permission's to the folder. All the updates are the same. I did all the install's.

    Previous to this thread, they were able to send a pdf file that contained data from the macro's that I had on the front end.

    Thank you,

    Kelly


    • Edited by KellyRosal Friday, October 9, 2015 2:10 PM added
    Friday, October 9, 2015 2:08 PM
  • Just to verify, try creating a separate macro that did exactly what you had before and compare the results. If they can send the email fine with the new macro, then you'll have to compare the macros to see what is different. If they still cannot send the email properly, then the problem is coming from elsewhere.
    Friday, October 9, 2015 3:17 PM
  • I can do this, but I wonder why from my PC at work, the exact same front end works every time?
    Friday, October 9, 2015 3:55 PM
  • I can do this, but I wonder why from my PC at work, the exact same front end works every time?

    That's why I wanted to isolate the code. I asked if your computer was any different from theirs, and you said they're the same. So, we need to check the code on their machines now because you said the old code worked on their machines. We just need to check that it still does. If not, then it's their machines - not yours. It's hard to explain without seeing what's happening. Sorry...
    • Marked as answer by KellyRosal Monday, October 12, 2015 10:29 AM
    Friday, October 9, 2015 4:16 PM
  • I'm thinking it's something in the office security settings and won't be able to check this out until Monday. I'll let you know.

    Thank you,

    Saturday, October 10, 2015 6:08 PM
  • Hi DB Guy,

    Well, I figured out what the issue was. The original code in the macro had the record being saved before it would generate the email. I'm not sure how I missed that, so all is working great now.

    Thank you for all you help!

    Kelly

    • Marked as answer by KellyRosal Monday, October 12, 2015 10:29 AM
    Monday, October 12, 2015 10:29 AM
  • Hi Kelly. Glad to hear you got it sorted out. Good luck!
    Monday, October 12, 2015 3:17 PM