EmailDatabaseObject content as bodytext of email

E

ECEK

Registered User.
Local time
Today, 16:33
Joined
Dec 19, 2012
Messages
717
I have several fields in my report that I wish to email.
I love the ability to send as a pdf (and that works) however i need to send as an email with the contents of my data appearing in the Message Text of the email?
 
Last edited:
Can't people dbl-click the attachment?
Or is that too much work?
 
maybe this will give you a start.
http://allenbrowne.com/AppOutputHtmlCode.html

the code generate html file.
now on your outlook code, you only need to write a function that reads this file (Scripting.FileSystemObject) and put it in .HTMLBody of your outlook object.
 
Last edited:
Can't people dbl-click the attachment? YES
Or is that too much work? YES

Hence the question !
 
Thanks Arnie !! Homework for me !! ill get back to you with my progress.
 
I'm afraid that's way too complex for me, but thanks for the input !!
 
What have you tried? Are you doing this from a form where all the data is displayed or a recordset/query ? I think this is fairly straightforward either way e.g. from a form - Air Code assuming plain text;


Dim sMsg as String

sMsg = "Data1 : " & Me.MyFormdatafield1 & vbcrlf
sMsg = sMsg & "Data2: " & Me.MyFormdatafield2 etc etc

Now to use this you will need to use some Outlook Automation there are a shed load of threads about this on here, or a pretty straight forward example here; https://www.microsoftaccessexpert.com/Microsoft-Access-Code-Outlook-Email.aspx

If you are using a recordset / query to pull the data it gets a little more interesting, but it's only a bit more code.
Have a look and see what you think
 
I have my records and when I click on a name a form appears with just their data. I have a button on that form to EmailDatabaseObject as a pdf.

What would be better would be to have the data in the body of the email.
 
Okay - so are you happy building the email body data from the information on your form?
If so would you like it in HTML - that becomes a bit more involved as you have to embed the HTML Characters into the string.
A Sample - (Sorry it's not tested I'm just too busy at the moment) is below - this should get you Heading in the right direction.
Code: 
Dim OutApp As Object
    Dim OutMail As Object
    Dim signature As String
    '-- Standard Email Variables
    Dim Variable_To As String
    Dim Variable_Subject As String
    Dim Variable_Body As String
    Dim Variable_AttachmentFile As String

    Variable_To = "Someone@Somwhere.com"
    Variable_Subject = "My Email about something"

    Variable_Body = "<style> " & _
     "p {font-size:11pt; font-family:Calibri}" & _
     "</style>" & _
     "<p>" & "Good Morning " & Me.ContactFirstName & "," & "</p>" & _
     "<p>" & "" & "</p>" & _
     "<p>" & "Your data goes in here: " & Me.Data1 & "<br/>" & _
     "More Text goes here : " & Me.SecondDatafield & "</p>"

  Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail        ' This creates a blank email and captures the users default signature.
        .BodyFormat = olFormatHTML
        .Display
    End With
    
    signature = OutMail.HTMLBody
        
    With OutMail
    
        .To = Variable_To
        .CC = ""
        .BCC = ""
        .Subject = Variable_Subject
        '.Attachments.Add (Variable_AttachmentFile)
        .HTMLBody = Variable_Body & signature
        .Display   'or use .Send
        .ReadReceiptRequested = False
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
 
maybe he wants it in a table (<td><tr>) format.
 
Yes a table would be fantastic !!!
 
ECEK said:
Yes a table would be fantastic !!!
Click to expand...

In that case Allen Browne's code is pre-written to do that. It is very well documented and pretty simple to use. Copy and paste it into a new module and play with it.
 
since mr.browne's code uses stylesheet file, you can embed them to the code.
just put it before the </head> portion of the code:

'I embedded it here
Print #1, "<style>"
Print #1, "BODY { font-family:Trebuchet MS, Helvetica; color:#000000; font-size:10pt }"
Print #1, "TABLE { font-family:Trebuchet MS, Helvetica; color:#000000; font-size: 10pt; }"
Print #1, "th { color:#FFFFFF; background-color:#000066 }"
Print #1, "td { vertical-align:top; background-color:#CCCCCC }"
Print #1, "H1 { font-family: Trebuchet MS, Helvetica, sans-serif; font-weight:normal; color: #000066; font-size:18pt }"
Print #1, "H2 { font-family: Trebuchet MS, Helvetica, sans-serif; font-weight:normal; color: #000066; font-size:16pt }"
Print #1, "H3 { font-family: Trebuchet MS, Helvetica, sans-serif; font-weight:normal; color: #000066; font-size:12pt }"
Print #1, "H4 { font-family: Trebuchet MS, Helvetica, sans-serif; font-weight:normal; color: #000066; font-size:10pt }"
Print #1, "</style>"
Print #1, "</head>"
 
This is my solution: However I would love the results in a table. Anybody able to adjust this html ?

Code: 
Private Sub Command28_Click()
Dim OutApp As Object
    Dim OutMail As Object
    Dim signature As String
    '-- Standard Email Variables
    'Dim Variable_To As String
    Dim Variable_Subject As String
    Dim Variable_Body As String
    Dim Variable_AttachmentFile As String

    'Variable_To = ""
    Variable_Subject = "Call Back Required for " & Me.Adviser

    Variable_Body = "<style> " & _
     "p {font-size:12pt; font-family:Calibri}" & _
     "</style>" & _
     "<p>" & Me.Greet & "," & "</p>" & _
     "<p>" & "" & "</p>" & _
     "<p>" & "<b>Company Name:</b>" & Me.companyname & _
     "<br/>" & "<b>Name:</b>" & Me.forename & _
     "<br/>" & "<b>Address 1:</b>" & Me.UseAddress1 & _
     "<br/>" & "<b>Postcode:</b>" & Me.UsePostcode & _
     "<br/>" & "<b>Home Tel:</b>" & Me.TelephoneH & _
     "<br/>" & "<b>Source:</b>" & Me.source & _
     "<br/>" & "<b>Mobile:</b>" & Me.TelephoneM & _
     "<br/>" & "<b>Reference:</b>" & Me.CLIENTREFuser & "</p>"

  Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail        ' This creates a blank email and captures the users default signature.
        .BodyFormat = olFormatHTML
        .Display
    End With
    
    signature = OutMail.HTMLBody
        
    With OutMail
    
        .To = Variable_To
        .CC = ""
        .BCC = ""
        .Subject = Variable_Subject
        '.Attachments.Add (Variable_AttachmentFile)
        .HTMLBody = Variable_Body & signature
        .Display   'or use .Send
        .ReadReceiptRequested = False
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 
haven't tested this yet:

Code: 
    Variable_Body = "<style> " & _
            "p {font-size:12pt; font-family:Calibri} " & _
            "th { color:#FFFFFF; background-color:#000066 } " & _
            "td { vertical-align:top; background-color:#CCCCCC } " & _
            "</style>" & _
            "<p>" & Me.Greet & "," & "</p>" & _
            "<p>" & "" & "</p>" & _
            "<p>"
    Variable_Body = Variable_Body & _
            "<table width=" & """100""" & ">" & _
            "<tr>" & _
            "<th>Company Name</th>" & _
            "<th>Name</th>" & _
            "<th>Address 1</th>" & _
            "<th>Postcode</th>" & _
            "<th>Home Tel</th>" & _
            "<th>Source</th>" & _
            "<th>Mobile</th>" & _
            "<th>Reference</th>"
    Variable_Body = Variable_Body & _
            "</tr>" & _
            "<tr>" & Me.companyname & "</tr>" & _
            "<tr>" & Me.forename & "</tr>" & _
            "<tr>" & Me.UseAddress1 & "</tr>" & _
            "<tr>" & Me.UsePostCode & "</tr>" & _
            "<tr>" & Me.TelephoneH & "</tr>" & _
            "<tr>" & Me.Source & "</tr>" & _
            "<tr>" & Me.TElephoneM & "</tr>" & _
            "<tr>" & Me.CLIENTREFuser & "</tr>" & _
            "</table>"
 
I'm afraid that won't work, you need to build each row as a series of <td>'s (Table Data) then the next row <tr> would be a repeat for each row of data. If it's just one row of data then you could build it statically.

Multiple row's : That's why Allen's code is pretty involved.
 
Code: 
    variable_body = "<style> " & _
            "p {font-size:12pt; font-family:Calibri} " & _
            "th { color:#FFFFFF; background-color:#000066 } " & _
            "td { vertical-align:top; background-color:#CCCCCC } " & _
            "</style>" & _
            "<p>" & Me.Greet & "," & "</p>" & _
            "<p>" & "" & "</p>" & _
            "<p>"
    variable_body = variable_body & _
            "<table width=" & """100""" & ">" & _
            "<tr>" & _
            "<th>Company Name</th>" & _
            "<th>Name</th>" & _
            "<th>Address 1</th>" & _
            "<th>Postcode</th>" & _
            "<th>Home Tel</th>" & _
            "<th>Source</th>" & _
            "<th>Mobile</th>" & _
            "<th>Reference</th>" & _
            "</tr>"
    variable_body = variable_body & _
            "<tr>" & _
            "<td>" & Me.companyname & "</td>" & _
            "<td>" & Me.forename & "</td>" & _
            "<td>" & Me.UseAddress1 & "</td>" & _
            "<td>" & Me.UsePostCode & "</td>" & _
            "<td>" & Me.TelephoneH & "</td>" & _
            "<td>" & Me.Source & "</td>" & _
            "<td>" & Me.TElephoneM & "</td>" & _
            "<td>" & Me.CLIENTREFuser & "</td>" & _
            "</tr>" & _
            "</table>"
 
Hi Arnie. Close !!! Frustratingly so.
The results of the code are Capture (attached)

What Im looking for is "Capture2. (Attached)
 

Attachments

  • Capture.PNG
    Capture.PNG
    6.1 KB · Views: 236
  • Capture2.PNG
    Capture2.PNG
    5 KB · Views: 230
In that case take a good look the at the structure he posted.

td = table data
tr = table row

Change them to suit your requirements
I assumed you wanted multiple rows of data, not single date points per line so Arnelgp changed it around.
 
I've managed to work out that (in a simple form) this is what I need, but Im struggling to put it into the code ?

Code: 
<table>
  <tr>
    <th>Company Name</th>
    <th>Me.companyname</th>
  </tr>
  <tr>
    <td>Name</td>
    <td>Me.forename</td>
  </tr>
  <tr>
    <td>Address 1</td>
    <td>UseAddress1</td>
  </tr>
  <tr>
    <td>Postcode</td>
    <td>Me.UsePostcode</td>
  </tr>
  <tr>
    <td>Home Tel</td>
    <td>Me.TelephoneH</td>
  </tr>
  <tr>
    <td>Source</td>
    <td>Me.source</td>
  </tr>
  <tr>
    <td>Mobile</td>
    <td>TelephoneM</td>
  </tr>
  <tr>
    <td>Reference</td>
    <td>Me.CLIENTREFuser</td>
  </tr>
</table>
 
You must log in or register to reply here.

Similar threads

J
Setting the Email address to send an outlook email
Replies
4
Views
499
Gasman
Gasman
L
Sending Emails With Windows 11 Office 365 (New Outlook)
2
Replies
26
Views
2,032
GPGeorge
GPGeorge
S
Error: The command or action 'SendObject' isn't available now.
Replies
9
Views
1,033
sonic8
S
A
Solved Sendobject PDF, cc field sometimes empty gives error
Replies
4
Views
315
andymartin3186
A
Isaac
Google Voice Feature - Schedule Send
Replies
2
Views
1,144
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom