Insert a table in the body in the E-mail (1 Viewer)

keawee · Nov 16, 2004

Hello,

I would like your help about a code wich sent a E-mail.

I would like to insert in the body of my e-mail, a table from access like this.

I have do this to sent the message but i don't know how can i insert in the body a query.

Private Sub cmdSendEmail()
On Error GoTo Error_Handler

Dim objOutlook As Outlook.Application
Dim objMsg As Outlook.MailItem
Dim objRecipient As Outlook.Recipient
Dim objMailAttachment As Outlook.Attachment

Set objOutlook = CreateObject("Outlook.Application")
Set objMsg = objOutlook.CreateItem(olMailItem)

With objMsg
.To = "Hello@Access.com"
.Subject = "subject of e-mail"
.Body = "Hello, have a nice day everybody" <-- How can i do ?
.send
End With

Set objOutlook = Nothing
Set objMsg = Nothing
Set objRecipient = Nothing
Set objMailAttachment = Nothing

Error_Handler:
If Err.Number = "287" Then
MsgBox "You clicked No to the Outlook security warning. " & _
"Rerun the procedure and click Yes to access e-mail" & _
"addresses to send your message. "
End If

End Sub

Thanks for your help

Keawee

daveUK · Nov 16, 2004

You will need to insert the query as an attachment.

keawee · Nov 18, 2004

Thanks DaveUK for your answer.

I have perhaps an idea about my problems but i would your opinion. I have a request and i can insert the records in a recordset. I have trying to create a table in my mail body in html and insert with my recordset the date in the table. I have do this like below. Is it a good solution for you. My code doesn't run but i 'am working on it.

Thanks for your advice:

Code:

Private Sub cmdSendEmail()
On Error GoTo Error_Handler

    Dim objOutlook As Outlook.Application
    Dim objMsg As Outlook.MailItem
    Dim objRecipient As Outlook.Recipient
    Dim objMailAttachment As Outlook.Attachment
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objMsg = objOutlook.CreateItem(olMailItem)
    
    With objMsg
        .To = "nicolas.lefebvre@fr.delarue.com"
        .Subject = "subject of e-mail"
        .HTMLBody = "<HTML><table border=1 cellspacing=0 cellpadding=0 style='border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt;mso-padding-alt:0cm 3.5pt 0cm 3.5pt'></HTML>" _
                    "HTML><tr></HTML>" _
                    "HTML><td width=326 valign=top style='width:244.45pt;border:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt'></HTML>" _
                    "HTML><p class=MsoNormal><span style='font-size:10.0pt;color:black'>Agence / Station<o:p></o:p></span></p></HTML>" _
                    "HTML></td></HTML>" _
                    "HTML><td width=326 valign=top style='width:244.45pt;border:solid windowtext .5pt;border-left:none;mso-border-left-alt:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt'></HTML>" _
                    "HTML><p class=MsoNormal><span style='font-size:10.0pt;color:black'>123<o:p></o:p></span></p></HTML>" _
                    "HTML></td></HTML>" _
                    "HTML></tr></HTML>" _
                    "HTML><tr></HTML>" _
                    "<HTML></table></HTML>
        .Display
        .send
    End With
     
    Set objOutlook = Nothing
    Set objMsg = Nothing
    Set objRecipient = Nothing
    Set objMailAttachment = Nothing

Error_Handler:
   If Err.Number = "287" Then
      MsgBox "You clicked No to the Outlook security warning. " & _
      "Rerun the procedure and click Yes to access e-mail" & _
      "addresses to send your message. "
   End If

End Sub

Keawee

robjones01 · Nov 18, 2004

Inserting table into Body of E-Mail

I have used the following code to insert the body into a message.

Code:

Public Function Auto_Escalation()

DoCmd.SetWarnings False

' Make table with Escalation Accounts
DoCmd.RunSQL ("SELECT tblHand_Off.DBReference, tblHand_Off.Customer_Reference, tblHand_Off.Meter_Reference, tblHand_Off.Product, tblHand_Off.Query_Type, tblHand_Off.Form_Type, tblHand_Off.Call_Date, tblHand_Off.Move_Type, tblHand_Off.Sent_Date, Null AS Age INTO tblAuto_Escalations FROM tblAccount_Details INNER JOIN tblHand_Off ON tblAccount_Details.DBReference = tblHand_Off.DBReference WHERE (((tblAccount_Details.Job_Complete)=False) AND ((tblHand_Off.Escalate)=True) AND ((tblHand_Off.Complete)=False));")

' Update Age
DoCmd.RunSQL ("UPDATE tblAuto_Escalations SET tblAuto_Escalations.Age = DateDiff('d',[Sent_Date],Date());")

Set Rs = CurrentDb.OpenRecordset("tblAuto_Escalations")
With Rs
    .MoveLast
    .MoveFirst
    Count = .RecordCount
    .Close
End With

' Message Box Stating Total Number of Imports
MsgBox "There are " & Count & " records to be Escalated by Secondary Processing", vbOKOnly, "SP Import Complete"

Set Rs = CurrentDb.OpenRecordset("SELECT tblAuto_Escalations.Product AS Product, tblAuto_Escalations.Form_Type AS [Query Type], Count(*) AS Total, Min(tblAuto_Escalations.Age) AS [Oldest Item] FROM tblAuto_Escalations GROUP BY tblAuto_Escalations.Product, tblAuto_Escalations.Form_Type;")
With Rs
    .MoveLast
    .MoveFirst
    Count = .RecordCount
End With

BodyTxt = "Product - Query Type - Total - Age"

For Counter = 1 To Count

BodyTxt = BodyTxt & vbNewLine & Rs![Product] & " - " & Rs![Query Type] & " - " & Rs![Total] & " - " & Rs![Oldest Item]

Rs.MoveNext

Next Counter

Rs.Close

Message = MsgBox("Do you wish to send an e-mail to JT to advise of Escalation Accounts?", vbInformation + vbYesNo, "Send E-mail")

If Message = vbYes Then
    ' Send Message to JT to tell him how many Escalations there are
    DoCmd.SendObject acSendQuery, "Escalation_Report", acFormatRTF, "To E-mail", "CC E-mail", , "Escalation Report - Auto Processing - " & Date, "Please find attached the breakdown of accounts that require Escalating to Process Owners. This will be done automatically by MI Database Auto Processing shortly. Please see below for summary of the number of accounts that are being sent to the Process Owners" & vbNewLine & vbNewLine & BodyTxt & vbNewLine & vbNewLine & "This message is for Information Only.", False
End If

DoCmd.SetWarnings True

End Function

It works by counting the number of lines and appending each field to a variable so that I can use it in the message body.

Not ideal I know, but it's effective & works.

Hope this helps.

Rob

Insert a table in the body in the E-mail (1 Viewer)

keawee

Registered User.

Attachments

daveUK

Registered User.

keawee

Registered User.

robjones01

Access 97 + / VB6.0 +

Similar threads

Users who are viewing this thread