Solved Report link to Access email (1 Viewer)

Alejo

Member
Local time
Today, 19:22
Joined
Jun 14, 2021
Messages
78
Hi All,

I would like to seek your assistance on the output that I wanted to generate.
I made a simple database2 with Table, Query,Form, Report to show the required assistance I needed.

In form there is a button (Send Email) with VBA code to generate an outlook email but due to lack of my experience on the VBA code I cannot display my desired output
Here's below the output that I cant produce

1. In the Subject, I put the code >>>Subject = "Sample" + " - " + "Me.Date1.Value"<<< I added "Me.Date1.Value" to display the current date unfortunately it doesn't.
2. In the Body, the value of the report should automatically be added to the Body (with the given table + colors)
3. I cant add the Signatures

Attached are the following for reference:
a. Database2 - the sample database
b. PDF file to show exactly the three items that I cant generate

Thank you in advance to all
 

Attachments

  • Database2.accdb
    704 KB · Views: 272
  • Current Output.pdf
    67.5 KB · Views: 274

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:22
Joined
Feb 28, 2001
Messages
26,999
For the date part, question #1...

Code:
Subject = "Sample" + " - " + Format( Me.Date1, "dd-mmm-yyyy" )

Or some other date-formatting pattern - there are many options.

As to questions #2 and #3

Not sure what you mean by "value of the report." A report is an object, not a value, and cannot be added as though it were a text string. It is closer in nature to an image - actually, a rendered text image - and normally is attached to the mail, not embedded in it. Embedding as a text string would lose coloration. Attachment would preserve it. To attach the report, you have to do a DoCmd.OutputTo to make a file (which therefore had a file name) that you can attach as an object.

When I peeked at the .ACCDB file, I also noted that you didn't have an Outlook reference in the Tools >> References dialog box from the VBA page. You have a late binding to Outlook based on the way you created the Outlook object, but that lack of library references to Outlook (the Outlook n.0 Object Library, whatever version of n is current for your system) prevented Access Intellisense from helping you.

There is no property I can find in an Outlook MailItem object that says ".Signature" or ".Signatures" so I have to ask where you got that idea? There is such a thing as adding a DIGITIAL (security-related) signature to a message but not the way you were trying it. Normally, the "yours truly, Joe Schmuckatelli" type of signature is merely another element that you add to the body of the message via concatenation.
 

Alejo

Member
Local time
Today, 19:22
Joined
Jun 14, 2021
Messages
78
For the date part, question #1...

Code:
Subject = "Sample" + " - " + Format( Me.Date1, "dd-mmm-yyyy" )

Or some other date-formatting pattern - there are many options.

As to questions #2 and #3

Not sure what you mean by "value of the report." A report is an object, not a value, and cannot be added as though it were a text string. It is closer in nature to an image - actually, a rendered text image - and normally is attached to the mail, not embedded in it. Embedding as a text string would lose coloration. Attachment would preserve it. To attach the report, you have to do a DoCmd.OutputTo to make a file (which therefore had a file name) that you can attach as an object.

When I peeked at the .ACCDB file, I also noted that you didn't have an Outlook reference in the Tools >> References dialog box from the VBA page. You have a late binding to Outlook based on the way you created the Outlook object, but that lack of library references to Outlook (the Outlook n.0 Object Library, whatever version of n is current for your system) prevented Access Intellisense from helping you.

There is no property I can find in an Outlook MailItem object that says ".Signature" or ".Signatures" so I have to ask where you got that idea? There is such a thing as adding a DIGITIAL (security-related) signature to a message but not the way you were trying it. Normally, the "yours truly, Joe Schmuckatelli" type of signature is merely another element that you add to the body of the message via concatenation.
hi Doc Man,
Thank you for the solution on the first query, it works as expected.

About the second query, I heard about the option to attach the generated report in the email. but in this case the request is how to automatically add the report information as part of the body of the email. Currently, i'm manually copying the information from the report manually to the body of the email.

About third query, as I mentioned im not that experienced in this vba, its just a play certain combination on how to have an automated signatures. Currently, after I copied manually the values in report in the body of the email, I'm manually adding my signatures into the email before sending it.

For #2 and #3, if you have additional inputs to share please let me know and would really appreciate it
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:22
Joined
Feb 28, 2001
Messages
26,999
Regarding the #2 question, I don't think you can do it directly because (a) an Access report is a rendered image, not a line of text, and (b) using an Outlook message body, you are limited to plain text or HTML. An Outlook message body in HTML actually DOES support insertion of pictures in-line because HTML gives it that capability. These articles come from Excel, which also uses VBA. They discuss the process. You'll probably have to play with it a bit, though.



This implies that you would have to build your report and save it as a file first, then use HTML tags to declare a picture in the sequence, more or less like you would do with a web page. You name the report as though it were a picture. By the way, you know you have an image in reports because you can draw lines and insert black-and-white fill patterns. That's not a font - that's an image.

Regarding #3 question, your "manually adding my signatures" is the only way to do "ordinary" signatures because the SMTP methods used for sending mail don't have a signature option. A message's "standard" salutation and signature are part of the message body, not separate items.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:22
Joined
May 7, 2009
Messages
19,169
using Allen Browne's.
 

Attachments

  • Database2 (2).accdb
    528 KB · Views: 264

Alejo

Member
Local time
Today, 19:22
Joined
Jun 14, 2021
Messages
78
Regarding the #2 question, I don't think you can do it directly because (a) an Access report is a rendered image, not a line of text, and (b) using an Outlook message body, you are limited to plain text or HTML. An Outlook message body in HTML actually DOES support insertion of pictures in-line because HTML gives it that capability. These articles come from Excel, which also uses VBA. They discuss the process. You'll probably have to play with it a bit, though.



This implies that you would have to build your report and save it as a file first, then use HTML tags to declare a picture in the sequence, more or less like you would do with a web page. You name the report as though it were a picture. By the way, you know you have an image in reports because you can draw lines and insert black-and-white fill patterns. That's not a font - that's an image.

Regarding #3 question, your "manually adding my signatures" is the only way to do "ordinary" signatures because the SMTP methods used for sending mail don't have a signature option. A message's "standard" salutation and signature are part of the message body, not separate items.
hi Doc Man,

Thank you for all the suggestions, appreciated
 

Alejo

Member
Local time
Today, 19:22
Joined
Jun 14, 2021
Messages
78
using Allen Browne's.
hi Arnelgp,

It resolves the query no.2 based on the test in a database2, thank you.

I transferred the VBA code and the module that you made to our actual database but when I run it there is a "Compile error" Sub of Function not defined" at this level "Call OutputHTML("Email", sFile)" Attached is the screen shot of the error.

I note that the file name: Form1, Query1, Report are exact file name in our database.

May I know if I still need to rename or additional action I need to do to transfer the codes to our actual file?


Private Sub Form1_Click()
Dim OutApp As Object
Dim OutMail As Object
'arnelgp
Dim sFile As String
sFile = Environ("UserProfile") & "\Documents\myHtml.htm"
If Len(Dir$(sFile)) <> 0 Then
Kill sFile
End If
Call OutputHTML("Query1", sFile)
'end of arnelgp

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.To = "email@yahoo.com;email1@yahoo.com"
.CC = "email@yahoo.com"

.Subject = "Sample" + " - " + "Me.Date1.Value"

'arnelgp
.HtmlBody = "Hi<br><br>This is the second line<br>" & _
CreateObject("Scripting.FileSystemObject").OpenTextFile(sFile, 1, True, -2).ReadAll


.Signatures = "The Author"




'.Attachments.Add ActiveWorkbook.FullName 'I turned this off

.Display 'Can use .Display to edit email or .Send to immediately send email without edit.

End With

On Error GoTo 0

Set OutMail = Nothing

Set OutApp = Nothing
End Sub
 

Attachments

  • Error.JPG
    Error.JPG
    40 KB · Views: 239

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:22
Joined
May 7, 2009
Messages
19,169
maybe you Named your Module same as the name of the function (OutputHtml).
if this is true delete the module and create New one (name it other than the name of the function (like modOutputHtml).

also the "colors" of the table is embedded in the code, so if you have knowledge of CSS (style)
you can edit those colors to your choice.
 

Alejo

Member
Local time
Today, 19:22
Joined
Jun 14, 2021
Messages
78
It is working now, thanks a lot.

For the colors, yes I need to adjust it, I will search how to work on it.
Can your confirm if the below part of the Modules is the CSS portion that I need to edit


'arnelgp
'I embedded it here
Print #iFileNum, "<style>"
Print #iFileNum, "BODY { font-family:Trebuchet MS, Helvetica; color:#000000; font-size:10pt }"
Print #iFileNum, "TABLE { font-family:Trebuchet MS, Helvetica; color:#000000; font-size: 10pt; }"
Print #iFileNum, "th { color:#FFFFFF; background-color:#000066 }"
Print #iFileNum, "td { vertical-align:top; background-color:#CCCCCC }"
Print #iFileNum, "H1 { font-family: Trebuchet MS, Helvetica, sans-serif; font-weight:normal; color: #000066; font-size:18pt }"
Print #iFileNum, "H2 { font-family: Trebuchet MS, Helvetica, sans-serif; font-weight:normal; color: #000066; font-size:16pt }"
Print #iFileNum, "H3 { font-family: Trebuchet MS, Helvetica, sans-serif; font-weight:normal; color: #000066; font-size:12pt }"
Print #iFileNum, "H4 { font-family: Trebuchet MS, Helvetica, sans-serif; font-weight:normal; color: #000066; font-size:10pt }"
Print #iFileNum, "</style>"
'end of arnelgp
Print #iFileNum, "</head>"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:22
Joined
May 7, 2009
Messages
19,169
yes that is the part.

here is the new code and will resolve pt.1.
for pt.3, signature, you open Outlook first and determine the Name of your
signature by going to Outlook Options->Mail->Signature.

do not run to form yet.
open it in Design view.
then open the code (click event of the button in design view) and
put the Correct name of your signature
in substitute to mySignature.htm (yoursignature.htm).

sSig = ReadSignature("mySignature.htm")
 

Attachments

  • Database2 (2).accdb
    952 KB · Views: 271

Alejo

Member
Local time
Today, 19:22
Joined
Jun 14, 2021
Messages
78
Really appreciated your help Anelgp,

I will check and test all the information you have added, not now but later.
Have some urgent things to go to....

Thank you and have a nice day
 

Alejo

Member
Local time
Today, 19:22
Joined
Jun 14, 2021
Messages
78
Really appreciated your help Anelgp,

I will check and test all the information you have added, not now but later.
Have some urgent things to go to....

Thank you and have a nice day
Hi Arnelgp,

It works, the best.
Query 1, 2 and 3 works as expected based on the database you provided.

Its just an addition, I'm trying to add 2 more line after the table but its giving an error.
Attached is the sample of the line that I want to add

.HtmlBody = "Hi<br><br>This is the second line<br>" & _
CreateObject("Scripting.FileSystemObject").OpenTextFile(sFile, 1, True, -2).ReadAll & _
"<br><br><br>" & _
sSig
 

Attachments

  • Capture.JPG
    Capture.JPG
    18.6 KB · Views: 254

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:22
Joined
May 7, 2009
Messages
19,169
.HtmlBody = "Hi<br><br>This is the second line<br>" & _
CreateObject("Scripting.FileSystemObject").OpenTextFile(sFile, 1, True, -2).ReadAll & _
"<br><br>This is the 3rd line" & _
"<br><br>This is the 4th line" & _
"<br><br><br>" & sSig
 

Users who are viewing this thread

Top Bottom