Attach second report to email (1 Viewer)

Access_Help

Registered User.
Local time
Today, 07:35
Joined
Feb 12, 2005
Messages
136
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:35
Joined
Oct 29, 2018
Messages
21,357
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.
 

June7

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,423
Will have to save reports as PDF files and attach those files to email using automation code.
 

Access_Help

Registered User.
Local time
Today, 07:35
Joined
Feb 12, 2005
Messages
136
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?
 

June7

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,423
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>"
    .Display
'    .Send
End With
End Sub
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 07:35
Joined
Oct 29, 2018
Messages
21,357
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
 

June7

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,423
Tried that DBGuy, does not result in hyperlink, just simple text in email.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:35
Joined
Oct 29, 2018
Messages
21,357
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.
 

June7

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,423
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:35
Joined
Oct 29, 2018
Messages
21,357
Hi. For a local path, you could try:
Code:
file://c:/folder/file.ext
untested...
 

June7

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,423
Okay, that worked. Learned new trick.

However, with HTML tag, can display link description instead of the link itself.
 

Mark_

Longboard on the internet
Local time
Today, 07:35
Joined
Sep 12, 2017
Messages
2,111
In HTML it would be
Code:
<a HREF="YourURLHere">Your display text here</a>
 

June7

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,423
What exactly do you not understand about the example code? You said your hyperlink works so you must have already written code to test. If you want to dynamically build hyperlink in code, then concatenate variable. Where will the hyperlink string come from? Post your attempted code.
 

Access_Help

Registered User.
Local time
Today, 07:35
Joined
Feb 12, 2005
Messages
136
What exactly do you not understand about the example code? You said your hyperlink works so you must have already written code to test. If you want to dynamically build hyperlink in code, then concatenate variable. Where will the hyperlink string come from? Post your attempted code.

Sorry, I should have been clearer. I am trying to add a text to the hyperlink as shown in bold below, but it just displays the code and does not convert it to an actual hyperlink. How do I modify the code to allow HTML formatting?

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 = [B][B]" "Test HL: <a href='folder path here'>Link description here</a>""[/B][/B]

   
            'send report
   DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTo, vCC, , vSubj, vBody
   DoCmd.Close acReport, "Form_Report"
   
Next
End Sub
 

June7

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,423
That's really the same procedure you originally posted.

Then what do you mean when you said the hyperlink works perfectly?

Follow the example code. Modify to use your info.

Can't use SendObject. Must use Outlook automation and HTML code tags.

And if you want to include report attachment, the report must be saved to a PDF file then that file can be attached to email.
 

Users who are viewing this thread

Top Bottom