UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Probably Another Access Email Question, Access 2010    
 
   
tjroen
post Sep 10 2019, 07:05 AM
Post#1



Posts: 4
Joined: 1-February 19



Greetings all,
First let me say i am not a coder. I piece together what I can find on the internet in order to make life easier.

I've written several queries to check the quality of data we collect and to flag instances that need further review. I would like to send the results of either the query results or records in a table via email without using Outlook.

Below is the VB I have so far. I can get the email to send but it doesn't include any of the records in the table. I get an email with the header row only



Public Function send_TimesMissing_emailv2()




Dim msg As Object

Set msg = CreateObject("CDO.Message")
msg.From = "from@gmail.com"
msg.To = "to@email.com; "

msg.Subject = "Times Missing without Explanation"
msg.htmlbody = "Thank you!"
msg.CC = ""
msg.BCC = ""
msg.ReplyTo = ""
' msg.AddAttachment "c:\invoices\invoice.pdf"

msg.configuration.Fields("http://schemas.microsoft.com/CDO/configuration/smtpserver") = "smtp.gmail.com"
msg.configuration.Fields("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = 465
msg.configuration.Fields("http://schemas.microsoft.com/CDO/configuration/sendusing") = 2
msg.configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/sendusername") = "from@gmail.com"
msg.configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/sendpassword") = "PWD"
msg.configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/smtpusessl") = True
msg.configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/smtpauthenticate") = 1
msg.configuration.Fields.Update

msg.htmlbody = "<font size='2' face='Verdana, Arial, Helvetica, sans-serif'>"
msg.htmlbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">Track&nbsp;</p></Font></TD>" & _
"<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">Date&nbsp;</p></Font></TD>" & _
"<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">Race#&nbsp;</p></Font></TD>" & _
"<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">Distance&nbsp;</p></Font></TD>" & _
"<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">F1&nbsp;</p></Font></TD>" & _
"<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">F2&nbsp;</p></Font></TD>" & _
"<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">F3&nbsp;</p></Font></TD>" & _
"<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">F4&nbsp;</p></Font></TD>" & _
"<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">F5&nbsp;</p></Font></TD>" & _
"<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">WT&nbsp;</p></Font></TD>" & _
"<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">Note&nbsp;</p></Font></TD>" & _
"</TR>"

Set rs = CurrentDb.OpenRecordset("TimesMissingWithoutReason", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF

mailbody = mailbody & "<TR>" & _
"<TD ><center>" & rs.Fields![Track].Value & "</TD>" & _
"<TD><center>" & rs.Fields![Date].Value & "</TD>" & _
"<TD><center>" & rs.Fields![Distance].Value & "</TD>" & _
"<TD><center>" & rs.Fields![F1].Value & "</TD>" & _
"<TD><center>" & rs.Fields![F2].Value & "</TD>" & _
"<TD><center>" & rs.Fields![F3].Value & "</TD>" & _
"<TD><center>" & rs.Fields![F4].Value & "</TD>" & _
"<TD><center>" & rs.Fields![F5].Value & "</TD>" & _
"<TD><center>" & rs.Fields![WT].Value & "</TD>" & _
"<TD><center>" & rs.Fields![Note].Value & "</TD>" & _
"</TR>"
rs.MoveNext
Loop
rs.Close




msg.Send
MsgBox "Message Sent"


Exit Function

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"


End Function

Go to the top of the page
 
DanielPineault
post Sep 10 2019, 07:36 AM
Post#2


UtterAccess VIP
Posts: 6,848
Joined: 30-June 11



I see you building your mailbody based off of the query, but you don't seem to ever assign it to be part of the actual body of the e-mail.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
tjroen
post Sep 10 2019, 07:45 AM
Post#3



Posts: 4
Joined: 1-February 19



How would I assign it to be part of the actual body of the e-mail?
Go to the top of the page
 
DanielPineault
post Sep 10 2019, 07:52 AM
Post#4


UtterAccess VIP
Posts: 6,848
Joined: 30-June 11



Perhaps something more along the lines of

CODE
Public Function send_TimesMissing_emailv2()
    Dim msg                   As Object
    Dim rs                    As DAO.Recordset
    Dim sBody                 As String

    On Error GoTo Error_Handler

    'Build the actual E-mail Body
    '****************************************************************************
***********
    sBody = "<font size='2' face='Verdana, Arial, Helvetica, sans-serif'>"
    sBody = sBody & "<TABLE Border=""1"" Cellspacing=""0"">" & _
            "<TR>" & _
            "<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">Track&nbsp;</p></Font></TD>" & _
            "<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">Date&nbsp;</p></Font></TD>" & _
            "<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">Race#&nbsp;</p></Font></TD>" & _
            "<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">Distance&nbsp;</p></Font></TD>" & _
            "<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">F1&nbsp;</p></Font></TD>" & _
            "<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">F2&nbsp;</p></Font></TD>" & _
            "<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">F3&nbsp;</p></Font></TD>" & _
            "<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">F4&nbsp;</p></Font></TD>" & _
            "<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">F5&nbsp;</p></Font></TD>" & _
            "<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">WT&nbsp;</p></Font></TD>" & _
            "<TD Bgcolor="""", Align=""Center""><Font Color=#154360><b><p style=""font-size:14px"">Note&nbsp;</p></Font></TD>" & _
            "</TR>"

    Set rs = CurrentDb.OpenRecordset("TimesMissingWithoutReason", dbOpenDynaset)
    rs.MoveFirst
    Do While Not rs.EOF
        sBody = sBody & "<TR>" & _
                "<TD ><center>" & rs.Fields![Track].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![Date].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![Distance].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![F1].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![F2].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![F3].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![F4].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![F5].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![WT].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![Note].Value & "</TD>" & _
                "</TR>"
        rs.MoveNext
    Loop
    'Don't forget to close the table and original font tags!
    sBody = sBody & "</table></font>"


    'Create and send our e-mail
    '****************************************************************************
***********
    Set msg = CreateObject("CDO.Message")
    msg.configuration.Fields("http://schemas.microsoft.com/CDO/configuration/smtpserver") = "smtp.gmail.com"
    msg.configuration.Fields("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = 465
    msg.configuration.Fields("http://schemas.microsoft.com/CDO/configuration/sendusing") = 2
    msg.configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/sendusername") = "from@gmail.com"
    msg.configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/sendpassword") = "PWD"
    msg.configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/smtpusessl") = True
    msg.configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/smtpauthenticate") = 1
    msg.configuration.Fields.Update

    msg.From = "from@gmail.com"
    msg.To = "to@email.com; "
    '    msg.CC = ""
    '    msg.BCC = ""
    '    msg.ReplyTo = ""
    msg.Subject = "Times Missing without Explanation"
    msg.HTMLBody = sBody
    '    msg.AddAttachment "c:\invoices\invoice.pdf"
    msg.send

    MsgBox "Message Sent"

Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not msg Is Nothing Then Set msg = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: send_TimesMissing_emailv2" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function


For the table heading row, you may prefer to use th tags instead of standard td tags.

Also, be careful with your HTML coding, there are no commas as separators in the table tags
CODE
<TABLE Border=""1"", Cellspacing=""0"">

should be
CODE
<TABLE Border=""1"" Cellspacing=""0"">


I also notice that your table heading row has 11 columns, but you data row only have 10 columns? It doesn't have the Race # column in the data.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
tjroen
post Sep 10 2019, 02:11 PM
Post#5



Posts: 4
Joined: 1-February 19



That worked.

Many thanks!
Go to the top of the page
 
DanielPineault
post Sep 10 2019, 02:44 PM
Post#6


UtterAccess VIP
Posts: 6,848
Joined: 30-June 11



My pleasure, any time! thumbup.gif

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
tjroen
post Sep 11 2019, 07:35 AM
Post#7



Posts: 4
Joined: 1-February 19



One more question.

If there are no records in the query I am currently getting an error saying no records exist. How can I avoid the error? I plan to combine this with other queries into one email so sending an email with a blank table is fine.
Go to the top of the page
 
DanielPineault
post Sep 11 2019, 08:03 AM
Post#8


UtterAccess VIP
Posts: 6,848
Joined: 30-June 11



Instead of
CODE
    Set rs = CurrentDb.OpenRecordset("TimesMissingWithoutReason", dbOpenDynaset)
    rs.MoveFirst
    Do While Not rs.EOF
        sBody = sBody & "<TR>" & _
                "<TD ><center>" & rs.Fields![Track].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![Date].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![Distance].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![F1].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![F2].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![F3].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![F4].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![F5].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![WT].Value & "</TD>" & _
                "<TD><center>" & rs.Fields![Note].Value & "</TD>" & _
                "</TR>"
        rs.MoveNext
    Loop


Normally, I'd code things more like
CODE
    Set rs = CurrentDb.OpenRecordset("TimesMissingWithoutReason", dbOpenSnapshot)
    With rs
        If .RecordCount <> 0 Then
            Do While Not rs.EOF
                sBody = sBody & "<TR>" & _
                        "<TD ><center>" & Nz(![Track], "") & "</TD>" & _
                        "<TD><center>" & Nz(![Date], "") & "</TD>" & _
                        "<TD><center>" & Nz(![Distance], "") & "</TD>" & _
                        "<TD><center>" & Nz(![F1], "") & "</TD>" & _
                        "<TD><center>" & Nz(![F2], "") & "</TD>" & _
                        "<TD><center>" & Nz(![F3], "") & "</TD>" & _
                        "<TD><center>" & Nz(![F4], "") & "</TD>" & _
                        "<TD><center>" & Nz(![F5], "") & "</TD>" & _
                        "<TD><center>" & Nz(![WT], "") & "</TD>" & _
                        "<TD><center>" & Nz(![Note], "") & "</TD>" & _
                        "</TR>"
                rs.MoveNext
            Loop
        End If
    End With


Note, you don't need to use dbOpenDynaset unless you plan on editing the recordset in some way. When just reading use dbOpenSnapshot.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th September 2019 - 03:14 AM