UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Send Group By Email With Excel Attachment Automatically, Access 2016    
 
   
Mac1206
post May 20 2020, 01:45 PM
Post#1



Posts: 4
Joined: 22-April 20



Hello, I have my code in which I'm trying to get it to grab each specific record by ID and automatically send it out instead of clicking send 100+ times...I already created a button to output the file grouped by ID to my Test Folder... see attachment...I need help on getting my send email button to grab each file and send it to the appropriate client. Below is the code for sending out my email, it's only grabbing one record now...

Private Sub CmdEmailRpt_Click()

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSql As String
Dim strTo As String
Dim txtID As TempVars


Set db = CurrentDb()

strSql = "Select * FROM Qry_Max_Pymt_PymtEmailDetail;"

Set rst = CurrentDb.OpenRecordset(strSql)

With rst

Do While Not rst.EOF

strTo = !Email & ";" & strTo

TempVars("txtID") = rst!ID.Value

DoEvents
rst.MoveNext

Loop

End With

DoCmd.SendObject acOutputQuery, "Max_Pymt_EmailDetails1", acFormatXLS, strTo, , , "This is a Test", "This is to make sure it's properly working", False

rst.Close

Set rst = Nothing
Set db = Nothing

End Sub


Attached File  Testfolder_ExampleFiles.png ( 31.03K )Number of downloads: 0


Go to the top of the page
 
theDBguy
post May 20 2020, 05:35 PM
Post#2


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess!
welcome2UA.gif

Are you really using acOutputQuery? Would it make any difference if you use acSendQuery instead? Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post May 20 2020, 05:39 PM
Post#3



Posts: 3,799
Joined: 27-February 09



Does each recipient get a different report (well, same report different data) or a copy of the same one?
Go to the top of the page
 
Mac1206
post May 21 2020, 04:19 PM
Post#4



Posts: 4
Joined: 22-April 20



Hi MadPiet,

Each client gets the same report format but different data...I have 2 buttons currently, 1 for exporting the files by group level to a folder for review and another button to send max email to all the recipients which sometimes range from 200 - 500 different recipients...My export works great see below but now they would like to send these out automatically with the EmailTo, Subject and body populated and unsure how to accomplish this part:


Export to Test Folder Code by ID and PymtPayee code:

Private Sub cmdSendRpt_Click()

Dim rst As DAO.Recordset
Dim strSql As String
Dim txtID As TempVars


strSql = "Select * FROM Qry_Max_Pymt_PymtEmailDetail;"

Set rst = CurrentDb.OpenRecordset(strSql)

Do While Not rst.EOF

TempVars("txtID") = rst!ID.Value

DoCmd.OutputTo acOutputQuery, "Max_Pymt_EmailDetails1", acFormatXLS, "C:\Users\amac\Desktop\TestFolder" & "\" & rst![ID] & "_" & rst![PymtsPayee] & ".xls"

DoEvents
rst.MoveNext

Loop

rst.Close
Set rst = Nothing

End Sub

Attached File  TestFolderfiles.png ( 16.92K )Number of downloads: 0

This post has been edited by Mac1206: May 21 2020, 04:24 PM
Go to the top of the page
 
Mac1206
post May 21 2020, 05:58 PM
Post#5



Posts: 4
Joined: 22-April 20



Hi TheDBGuy,

I'm unsure which is the correct one to use, I just researched it and you are right...

The SendObject method has the following arguments:

Type This is the type of the object attached to the mail. The options are:
acSendDataAccessPage
acSendForm
acSendModule
acSendNoObject
acSendQuery
acSendReport
acSendTable

If nothing is specified acSendNoObject is taken as default. This is also to be selected when there is no attachment.
This post has been edited by Mac1206: May 21 2020, 06:04 PM
Go to the top of the page
 
theDBguy
post May 25 2020, 08:54 AM
Post#6


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


So, did using acSendQuery fix the problem?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2020 - 10:49 AM