Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-27-2019, 09:09 AM   #1
Access_Help
Newly Registered User
 
Join Date: Feb 2005
Posts: 86
Thanks: 29
Thanked 0 Times in 0 Posts
Access_Help is on a distinguished road
Attach second report to email

Hi,

I have a fully working script for attaching a report to an e-mail. I want to extend the script to attach another report. not sure how I can add a second sql script and attach the second report to the e-mail...

Code:
Public Sub Command6_Click()
Dim vTo, vCC, vSubj, vBody, vRpt, vForm, vHoy
Dim vFilePath
Dim i As Integer
vRpt = "Report"
vQry = "qry_difference_achievement"



'scan the list box
For i = 0 To lstEAddrs.ListCount - 1
   vItm = lstEAddrs.ItemData(i)   'get new item
   lstEAddrs = vItm                   'set list box to item
  
   DoCmd.OpenReport vRpt, acPreview, , "[Form]= '" & lstEAddrs.Column(0) & "'"
   
   vName = lstEAddrs.Column(1)
   vForm = lstEAddrs.Column(0)
   vHoy = lstEAddrs.Column(4)
   vSubj = "XYZ: " & vForm & ""
   vTo = lstEAddrs.Column(2)      'col 1 has ID, col2 has email  (in vb, combo box items starts with 0)
   vCC = "xyz@gmail.com;" & vHoy & ""
   vBody = "Dear .......
   
            'send report
   DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTo, vCC, , vSubj, vBody
   DoCmd.Close acReport, "Form_Report"
   
Next
End Sub

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Access_Help is offline   Reply With Quote
Old 02-27-2019, 09:21 AM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 12
Thanked 264 Times in 257 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Attach second report to email

Hi. Unfortunately, the SendObject method can only attach one object/file at a time. If you use Outlook as your default email client, you can use Outlook Automation. You can find some sample codes here. If not, you could try using CDO code with your SMTP server. Again, there are sample codes for those.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Access_Help (03-09-2019)
Old 02-27-2019, 11:43 AM   #3
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,391
Thanks: 0
Thanked 339 Times in 338 Posts
June7 will become famous soon enough
Re: Attach second report to email

Will have to save reports as PDF files and attach those files to email using automation code.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Access_Help (03-09-2019)
Old 03-09-2019, 12:02 AM   #4
Access_Help
Newly Registered User
 
Join Date: Feb 2005
Posts: 86
Thanks: 29
Thanked 0 Times in 0 Posts
Access_Help is on a distinguished road
Re: Attach second report to email

As an alternative, Can I add a hyperlink in the body of the email to a location on the server?
If, so how do I do this in this code?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Access_Help is offline   Reply With Quote
Old 03-09-2019, 12:40 PM   #5
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,391
Thanks: 0
Thanked 339 Times in 338 Posts
June7 will become famous soon enough
Re: Attach second report to email

Can't use SendObject, use Outlook automation. Email body has to be HTMLBody and use HTML code tags to embed hyperlink. Example:
Code:
Private Sub Email()
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .To = "email address"
'    .cc = ""
'    .bcc = ""
'    .Attachments.Add "filepath\filename"
    .Subject = "Test"
    .HTMLBody = "Test HL: <a href='folder path here'>Link description here</a>"
'    .Send
End With
End Sub
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 03-09-2019, 01:08 PM   #6
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 12
Thanked 264 Times in 257 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Attach second report to email

Quote:
Originally Posted by Access_Help View Post
As an alternative, Can I add a hyperlink in the body of the email to a location on the server?
If, so how do I do this in this code?
Sure, you could try that. For example, try:
Code:
file://servername/sharename/filename.ext
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 03-09-2019, 01:15 PM   #7
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,391
Thanks: 0
Thanked 339 Times in 338 Posts
June7 will become famous soon enough
Re: Attach second report to email

Tried that DBGuy, does not result in hyperlink, just simple text in email.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 03-09-2019, 01:33 PM   #8
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 12
Thanked 264 Times in 257 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Attach second report to email

Quote:
Originally Posted by June7 View Post
Tried that DBGuy, does not result in hyperlink, just simple text in email.
Hi. Did you try it with the email formatted/displayed as Rich Text? It should be just a matter of setting the email client to format/display the incoming email as Rich Text.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Access_Help (03-22-2019)
Old 03-09-2019, 03:25 PM   #9
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,391
Thanks: 0
Thanked 339 Times in 338 Posts
June7 will become famous soon enough
Re: Attach second report to email

Using SendObject. Opens message, I choose RichText, but the email received as HTML, no hyperlink.

I am on personal laptop, not network, so can't use the UNC pathing. Will have to let OP test your suggestion.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Access_Help (03-22-2019)
Old 03-09-2019, 03:38 PM   #10
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 12
Thanked 264 Times in 257 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Attach second report to email

Hi. For a local path, you could try:
Code:
file://c:/folder/file.ext
untested...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Access_Help (03-22-2019)
Old 03-09-2019, 04:46 PM   #11
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,391
Thanks: 0
Thanked 339 Times in 338 Posts
June7 will become famous soon enough
Re: Attach second report to email

Okay, that worked. Learned new trick.

However, with HTML tag, can display link description instead of the link itself.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Access_Help (03-22-2019)
Old 03-22-2019, 03:44 AM   #12
Access_Help
Newly Registered User
 
Join Date: Feb 2005
Posts: 86
Thanks: 29
Thanked 0 Times in 0 Posts
Access_Help is on a distinguished road
Re: Attach second report to email

the hyperlink works perfectly thank you!
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Access_Help is offline   Reply With Quote
Old 03-22-2019, 06:09 AM   #13
Access_Help
Newly Registered User
 
Join Date: Feb 2005
Posts: 86
Thanks: 29
Thanked 0 Times in 0 Posts
Access_Help is on a distinguished road
Re: Attach second report to email

How do I add the hyperlink text to disguise the file path?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Access_Help is offline   Reply With Quote
Old 03-22-2019, 10:03 AM   #14
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,615
Thanks: 16
Thanked 301 Times in 298 Posts
Mark_ will become famous soon enough
Re: Attach second report to email

In HTML it would be
Code:
<a HREF="YourURLHere">Your display text here</a>
Mark_ is offline   Reply With Quote
Old 03-22-2019, 11:46 AM   #15
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,391
Thanks: 0
Thanked 339 Times in 338 Posts
June7 will become famous soon enough
Re: Attach second report to email

That is described back in post 5.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
unable to attach a report to access email zoona Modules & VBA 1 11-03-2014 05:02 AM
Transfer data from form to report and then attach report to email cino89 Reports 0 10-24-2014 04:29 AM
Converting report to PDF and attach it to email crxftw Reports 2 07-05-2011 10:47 AM
Converting report to PDF and attach it to email crxftw General 2 07-05-2011 12:42 AM
Automatically attach a report as a PDF into my email myhnews Reports 5 05-10-2007 06:45 PM




All times are GMT -8. The time now is 10:04 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World