Attach second report to email

[Access_Help]

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...

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]

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]

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

 

[Access_Help]

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]

Can't use SendObject, use Outlook automation. Email body has to be HTMLBody and use HTML code tags to embed hyperlink. Example:

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

 

[theDBguy]

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:

file://servername/sharename/filename.ext

 

[June7]

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

 

[theDBguy]

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]

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]

Hi. For a local path, you could try:

file://c:/folder/file.ext

untested...

 

[June7]

Okay, that worked. Learned new trick.

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

 

[Access_Help]

the hyperlink works perfectly thank you!

 

[Access_Help]

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

 

[Mark_]

In HTML it would be

<a HREF="YourURLHere">Your display text here</a>

 

[June7]

That is described back in post 5.

 

[Access_Help]

That is described back in post 5.

Thanks, but how do I modify the original post in Post#1 to make HTML compatible hyperlinks?

 

[June7]

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]

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?

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]

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.