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
> Email Report Page To Specific Id (sampledb Included), Access 2016    
 
   
AccessSqlHelp
post Feb 27 2020, 05:48 PM
Post#1



Posts: 35
Joined: 20-February 20



Hello everyone,
I have the goal of emailing a report page to the user shown on the page.

I realize this is a topic that has been discussed a million times, but for some reason I cant get it to work in the sample db I attached.

I am very new to vba and know almost zero.

I spent about a week reading threads from multiple forums and trying to cobble together code that would work.

Because I have made little or no progress on my actual db, I created a sampledb that shows what I am trying to do.

I was hoping someone who is experienced could take a look at the db and tell me what I need to put behind the button to make this work.

For whatever reason, I can't get the the code to

1. save a pdf of the current page to a folder

2. attach the current page as an email to the person indicated on the record


Thanks in advance for any help.


ps. I use thunderbird--it works with access but I don't know how that would change any specific calls to outlook.
Go to the top of the page
 
June7
post Feb 27 2020, 06:03 PM
Post#2



Posts: 1,312
Joined: 25-January 16
From: The Great Land


Button should be on a form, not a report. Code causes error opening report because report is already open.

Open report in PrintPreview filtered to desired record.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
AccessSqlHelp
post Feb 27 2020, 07:48 PM
Post#3



Posts: 35
Joined: 20-February 20



I attached a second sample here because I saw the report was page-breaking incorrectly.

QUOTE
Button should be on a form, not a report. Code causes error opening report because report is already open.

Open report in PrintPreview filtered to desired record.

Hi June7, thank you very much for your quick reply.

I'm sorry if I wasn't clear with my goal.
My end goal with this particular db is to hit the button, create an individual email based on the report page that particular human's information appears on.
I will have thousands of records to send, so I would prefer to automate. (I don't know how to edit the original description of the issue to include this)
I created the sampledb to show the particular situation I am working with--it does not include forms, it only has a report.

The report details are pulled from different rows of the table.

I do not get an error when I hit the button with the code in the attached db. It simply attaches the entire report.
Are you getting an error?

Without knowing any better, it seems like my first task would be
1. save a pdf of the current report page to a folder
for example: save file as reportid.pdf in H:\reportfolder

I am having trouble doing this and need help.

My second task (from my understanding) would be to pipe the output of 1 to some type of code like I included in the db that uses the information included in the table
For example:

open email client
get email address from table
match to file name in
H:\reportfolder"recordid.pdf"
either save as draft or send.

Again, I'm sorry if this is not clear. I appreciate the time.
This post has been edited by AccessSqlHelp: Feb 27 2020, 08:20 PM
Go to the top of the page
 
June7
post Feb 27 2020, 08:32 PM
Post#4



Posts: 1,312
Joined: 25-January 16
From: The Great Land


I did get error.

Yes, this can be automated and is a common topic. Conventional approach is:

1. open a recordset of emails

2. loop through recordset

3. open report filtered by person ID in record

4. send email to address in record

5. close report

6. move to next record and repeat

Here is one discussion https://www.accessforums.net/showthread.php?t=79802

This post has been edited by June7: Feb 27 2020, 08:36 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
AccessSqlHelp
post Feb 27 2020, 10:00 PM
Post#5



Posts: 35
Joined: 20-February 20



I don't get an error. When I click the button I get what shows up in the attached screenshot.
Could it have to do with me running 64bit?

I agree with the post you quoted in the other form where "micron" says that many newcomers have problems with this.
I have read a lot of posts and tried to cobble code together and I keep failing.
From what I have read, attaching an individual report page and automating individual emails is easy for experts.
That is why I created the sampledb, to show that I am willing do the legwork so an access expert can simply "Show" me what to do in the example I provided, instead of of "Tell"ing me what to do.
Thanks again for reading and thanks for all you do.
This post has been edited by AccessSqlHelp: Feb 27 2020, 10:04 PM
Attached File(s)
Attached File  Screenshot__74_.zip ( 46.76K )Number of downloads: 3
 
Go to the top of the page
 
June7
post Feb 27 2020, 10:07 PM
Post#6



Posts: 1,312
Joined: 25-January 16
From: The Great Land


You run 64-bit Access? I use 32-bit Access with 64-bit computer. I won't be able to test code behind report.

You have not yet attempted what I outlined. Follow example provided to build looping code.


This post has been edited by June7: Feb 27 2020, 10:16 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
AccessSqlHelp
post Feb 27 2020, 11:06 PM
Post#7



Posts: 35
Joined: 20-February 20



Yes, I have 2019 64bit. Do you know anyone who could help by showing me with the db that I created rather than telling me what to do?
QUOTE
You have not yet attempted what I outlined. Follow example provided to build looping code.
I read through the link you provided, and then followed more links on the subsequent pages. I don't see a specific
QUOTE
example provided to build looping code.
and I don't know how to loop vba. The examples I do see are long and don't include sample dbs like my post does. If I understood how to apply the info in those links and situations, I would have done it already. Again, that is why I took the time to build an example that shows what I am trying to do.
June7 Please don't feel obligated to reply if you are frustrated, you have helped a lot already. Are there any other access mvps or experts who have a minute to look at the sample db I created and "show" me how to print a single page pdf and attach to individual email using the actual sample db I created?
This post has been edited by AccessSqlHelp: Feb 27 2020, 11:07 PM
Go to the top of the page
 
June7
post Feb 27 2020, 11:43 PM
Post#8



Posts: 1,312
Joined: 25-January 16
From: The Great Land


Apologies.

Something like this:
CODE
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT recordID, ContactEmail FROM info")
Do Until rs.EOF
      DoCmd.OpenReport "Letter1", acViewPreview, , "recordID=" & rs!recordID
      DoCmd.SendObject acSendReport, , acFormatPDF, rs!ContactEmail, , , "text here", "text here", False
      DoCmd.Close
Loop


This post has been edited by June7: Feb 27 2020, 11:49 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
AccessSqlHelp
post Feb 28 2020, 05:59 AM
Post#9



Posts: 35
Joined: 20-February 20



When I add that code to the DB I get an error.

I attached the most current version

If June7 cannot check this particular code, is anyone else able to help?

Thanks for reading everyone!
Go to the top of the page
 
June7
post Feb 28 2020, 01:57 PM
Post#10



Posts: 1,312
Joined: 25-January 16
From: The Great Land


Can't have both my suggested code and your original code within the recordset loop. Adapt your original code to use filter criteria and pull info from recordset as shown in my example.

This post has been edited by June7: Feb 28 2020, 02:04 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
AccessSqlHelp
post Feb 28 2020, 04:35 PM
Post#11



Posts: 35
Joined: 20-February 20



Hi, thanks again for replying June7.
When I insert your code I get error 3464.

At this point I need a visual example and understand if you cannot provide.

Is anyone reading this able to download the db, insert the code, and upload the db with a response?
I am working with access 2019 64 bit


attached is the most recent example.
Thanks!
This post has been edited by AccessSqlHelp: Feb 28 2020, 04:38 PM
Attached File(s)
Attached File  emailsample__4_.zip ( 194.12K )Number of downloads: 2
 
Go to the top of the page
 
June7
post Feb 28 2020, 04:59 PM
Post#12



Posts: 1,312
Joined: 25-January 16
From: The Great Land


I tested code in a general module.
CODE
Private Sub Command117_Click()
Dim sExistingReportName As String
Dim sAttachmentName As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT recordID, contactemail FROM info")
Do Until rs.EOF
    'Input variables
    sExistingReportName = "letter1"    'Name of the Access report Object to send
    sAttachmentName = "test"    'Name to be used for the attachment in the e-mail

    'The code to make it happen
    DoCmd.OpenReport sExistingReportName, acViewPreview, , "recordID='" & rs!recordid & "'", acHidden
    Reports(sExistingReportName).Caption = sAttachmentName    'by changing the report caption
                                                        'you effectively change the name
                                                        'used for the attachment in the
                                                        '.SendObject method
    DoCmd.SendObject acSendReport, sExistingReportName, acFormatPDF, rs!ContactEmail, , , "Subject", "E-mail Body", False
    DoCmd.Close acReport, sExistingReportName
Loop
End Sub
Change "Subject" and "E-mail Body" to whatever you want to say. How do you want to determine what value to use for sAttachmentName?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
AccessSqlHelp
post Mar 2 2020, 06:47 PM
Post#13



Posts: 35
Joined: 20-February 20



Thank you so much June7! It now creates a pdf and my client prompts me if I would like to allow 3rd party to email. This is great for now, ultimately I'd like to figure out how to save as a draft.

I guess Sattachmentname would be humanname_recordid"sometext". I have tried to make this work in the code but have not had any success. In general, how would I call the xfield that matches the current recordid?

I have also been trying to get the subject line to reference the humanname so Dear[info].[humanname] would be the "Subject".

CODE
DoCmd.SendObject acSendReport, sExistingReportName, acFormatPDF, rs!ContactEmail, , , "Subject", "E-mail Body", False

I really appreciate your patience and effort.
Go to the top of the page
 
June7
post Mar 2 2020, 08:16 PM
Post#14



Posts: 1,312
Joined: 25-January 16
From: The Great Land


Include whatever fields you need in recordset.

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT recordid, humanname, contactemail FROM info")

Now reference humanname field to build Subject and recordid field to build attachment name.

Really need a table where persons are not duplicate records.

This post has been edited by June7: Mar 2 2020, 08:16 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
AccessSqlHelp
post Mar 2 2020, 09:26 PM
Post#15



Posts: 35
Joined: 20-February 20



Yes, I had tried
CODE
Dim sExistingReportName As String
Dim sAttachmentName As String

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT recordID, humanname, contactemail FROM info")
Do Until rs.EOF
    'Input variables
    sExistingReportName = "letter1"    'Name of the Access report Object to send
    sAttachmentName = humanname_recordID    'Name to be used for the attachment in the e-mail

    'The code to make it happen
    DoCmd.OpenReport sExistingReportName, acViewPreview, , "recordID='" & rs!recordid & "'", acHidden
    Reports(sExistingReportName).Caption = sAttachmentName    'by changing the report caption
                                                        'you effectively change the name
                                                        'used for the attachment in the
                                                        '.SendObject method
    DoCmd.SendObject acSendReport, sExistingReportName, acFormatPDF, rs!ContactEmail, , , [humanname], "Please call if you have any questions", False
    DoCmd.Close acReport, sExistingReportName
Loop
End Sub


but I can't get the syntax correct when referring to [humanname] or any other existing value when building subject, body, attachment, etc.
This post has been edited by AccessSqlHelp: Mar 2 2020, 09:27 PM
Go to the top of the page
 
June7
post Mar 2 2020, 09:39 PM
Post#16



Posts: 1,312
Joined: 25-January 16
From: The Great Land


Reference recordset variable, same is shown for rs!contactemail and rs!recordid:

rs!humanname

This post has been edited by June7: Mar 2 2020, 09:40 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
AccessSqlHelp
post Mar 2 2020, 10:11 PM
Post#17



Posts: 35
Joined: 20-February 20



Right, that makes sense but I can't figure out the syntax for>
Dear rs!humanname

or how to combine (concat?) text and field Dear [name]

Go to the top of the page
 
June7
post Mar 2 2020, 10:36 PM
Post#18



Posts: 1,312
Joined: 25-January 16
From: The Great Land


Literal text goes between quote marks:

"Dear " & rs!humanname & ":"


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th April 2020 - 06:56 AM