Email using Access

[gsbatch1]

Moved to Forms::::::Email using Access

I wanted to know if it is possible to embed a pivot table (from a query) in the body of an email. I know how to use VBA to embed data from a table in the body, but not sure how to incorporate an entire pivot table, or if it can be done.

 

[Guus2005]

it should be the same as a regular table. If you know how to embed a table in the body of a mail, a pivot table is more or less the same. So it should work.

HTH

 

[gsbatch1]

I did not exactly embed the table, as I said I embedded data from the table.


Private Sub Command45_Click()
Dim strMsg As String
strMsg = "Are you sure you want to send email?"

If MsgBox(strMsg, vbYesNo) = vbYes Then

Set rstAM = New ADODB.Recordset
rstAM.CursorLocation = adUseClient
rstAM.Open "Select * From qryEmail", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic


Dim r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11, r12, r13, r14, r15, r17, r18, r19, r20, r21 As String

Do While Not rstAM.EOF
r1 = rstAM![AMPM]
r2 = rstAM![NDT]
r3 = rstAM![NDT Oldest]
r4 = rstAM![NDT MTTR]
r14 = rstAM![NDT>24Hrs]
r5 = rstAM![CQ]
r6 = rstAM![CQ Oldest]
r7 = rstAM![CQ MTTR]
r15 = rstAM![CQ>48Hrs]
r8 = rstAM![Feature]
r9 = rstAM![Feature Oldest]
r10 = rstAM![Feature MTTR]
r11 = rstAM![AG]
r12 = rstAM![AG Oldest]
r13 = rstAM![AG MTTR]
r17 = rstAM![Linked]
r18 = rstAM![Linked Oldest]
r19 = rstAM![Linked MTTR]
r20 = rstAM![Totals]
r21 = rstAM![Total>48Hrs]
rstAM.MoveNext

Loop
DoCmd.SendObject , "", "", _
"Email Address", _
"", "", _
Date & " " & r1 & " Ticket Count ", vbCrLf & Chr(13) & "NDT = " & r2 & " (" & r3 & ") TTR " & r4 & " # NDT > 24 Hrs: " & r14 & vbCrLf & Chr(13) & "CQ = " & r5 & " (" & r6 & ") TTR " & r7 & " # CQ > 48 Hrs: " & r15 & vbCrLf & Chr(13) & "Feature = " & r8 & " (" & r9 & ") TTR " & r10 & vbCrLf & Chr(13) & "Auto Generated = " & r11 & " (" & r12 & ") TTR " & r13 & vbCrLf & Chr(13) & "Linked =" & r17 & " (" & r18 & ") TTR " & r19 & vbCrLf & vbCrLf & "Total = " & r20 & " Total > 48 Hrs: " & r21 & vbCrLf & vbCrLf & vbCrLf & "* Parked Tickets are no longer calculated as part of this count and will be a separate report sent out on Fridays.", False, ""
DoCmd.SetWarnings False
DoCmd.OpenQuery "04-qryTktCntArchive", acViewNormal, acEdit
DoCmd.Close acQuery, "04-qryTktCntArchive", acSaveYes
DoCmd.Quit
End If
DoCmd.Quit
End Sub


The above works with single row tables. I need to get a pivot or an entire table into the body.

Thanks,

 

[Guus2005]

Private Sub Command45_Click()
    Dim strMsg As String
    strMsg = "Are you sure you want to send email?"
    
    If MsgBox(strMsg, vbYesNo) = vbYes Then
   
    Set rstAM = New ADODB.Recordset
    rstAM.CursorLocation = adUseClient
    rstAM.Open "Select * From qryEmail", _
    CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
[COLOR="Red"]' In de next line only r21 is declared a string, the rest is variant.[/COLOR]
    Dim r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11, r12, r13, r14, r15, r17, r18, r19, r20, r21 As String
[COLOR="Red"]    Dim strMailBody as string
    strMailBody = ""[/COLOR]
    
    Do While Not rstAM.EOF
        r1 = rstAM![AMPM]
        r2 = rstAM![NDT]
        r3 = rstAM![NDT Oldest]
        r4 = rstAM![NDT MTTR]
        r14 = rstAM![NDT>24Hrs]
        r5 = rstAM![CQ]
        r6 = rstAM![CQ Oldest]
        r7 = rstAM![CQ MTTR]
        r15 = rstAM![CQ>48Hrs]
        r8 = rstAM![Feature]
        r9 = rstAM![Feature Oldest]
        r10 = rstAM![Feature  MTTR]
        r11 = rstAM![AG]
        r12 = rstAM![AG Oldest]
        r13 = rstAM![AG  MTTR]
        r17 = rstAM![Linked]
        r18 = rstAM![Linked Oldest]
        r19 = rstAM![Linked MTTR]
        r20 = rstAM![Totals]
        r21 = rstAM![Total>48Hrs]

[COLOR="Red"]        strMailBody = strMailBody & _
     Date & " " & r1 & " Ticket Count ", vbCrLf & Chr(13) & "NDT = " & r2 & "  (" & r3 & ")  TTR " & r4 & "       # NDT > 24 Hrs: " & r14 & vbCrLf & Chr(13) & "CQ = " & r5 & "  (" & r6 & ")  TTR " & r7 & "       # CQ > 48 Hrs: " & r15 & vbCrLf & Chr(13) & "Feature = " & r8 & "  (" & r9 & ")  TTR " & r10 & vbCrLf & Chr(13) & "Auto Generated = " & r11 & "  (" & r12 & ")  TTR " & r13 & vbCrLf & Chr(13) & "Linked =" & r17 & " (" & r18 & ") TTR " & r19 & vbCrLf & vbCrLf & "Total = " & r20 & "       Total > 48 Hrs: " & r21 & vbCrLf & vbCrLf & vbCrLf [/COLOR]

       rstAM.MoveNext    
    Loop
     DoCmd.SendObject , "", "", _
    "Email Address", _
    "", "", _
[COLOR="Red"]strMailBody [/COLOR]& "* Parked Tickets are no longer calculated as part of this count and will be a separate report sent out on Fridays.", False, ""
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "04-qryTktCntArchive", acViewNormal, acEdit
    DoCmd.Close acQuery, "04-qryTktCntArchive", acSaveYes
    DoCmd.Quit
    End If
    DoCmd.Quit
End Sub

The above works with single row tables. I need to get a pivot or an entire table into the body.

Thanks,

Above my additions are in red.
the result of the records are in the message body.

HTH

 

[gsbatch1]

Thanks, it worked with one little tweak. I moved the purple section from where you had it in the string creation down to the Body creation. This was the subject line, and did not need to be created more than one time.

THANK YOU... This saved me alot of wasted coding.



strMailBody = strMailBody & _
(Date & " " & r1 & " Ticket Count ", ) vbCrLf & Chr(13) & "NDT = " & r2 & " (" & r3 & ") TTR " & r4 & " # NDT > 24 Hrs: " & r14 & vbCrLf & Chr(13) & "CQ = " & r5 & " (" & r6 & ") TTR " & r7 & " # CQ > 48 Hrs: " & r15 & vbCrLf & Chr(13) & "Feature = " & r8 & " (" & r9 & ") TTR " & r10 & vbCrLf & Chr(13) & "Auto Generated = " & r11 & " (" & r12 & ") TTR " & r13 & vbCrLf & Chr(13) & "Linked =" & r17 & " (" & r18 & ") TTR " & r19 & vbCrLf & vbCrLf & "Total = " & r20 & " Total > 48 Hrs: " & r21 & vbCrLf & vbCrLf & vbCrLf
rstAM.MoveNext
Loop
DoCmd.SendObject , "", "", _
"Email Address", _
"", "", _
Date & " " & r1 & " Ticket Count ", strMailBody & "* Parked Tickets are no longer calculated as part of this count and will be a separate report sent out on Fridays.", False, ""
DoCmd.SetWarnings False
End If
DoCmd.Quit
End Sub

 

[gsbatch1]

One final thing,
Is there a way to Bold Face the fonts of the items so they stand out from the actual #'s?

 

[Guus2005]

Not sure, perhaps you could use rtf codes but you'll have to try or you can send a html message instead.

HTH