none
How can attach excel query results directly in a generated access 2013 email? RRS feed

  • Question

  • Hi All,

    The script below is working but I wonder if I can include the query results directly in the email, without using the command "fDoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", "C:\Users\john.peacock\Documents\Data-base\Test.xlsx", True, "Data"" first?  I thought about command "DoCmd.SendObject ac Report" but will that not generate another blank email?  any suggestion?

    Private Sub Command50_Click()

    Dim day As Integer

    day = Weekday(Date, vbSunday)

    Dim olApp As Outlook.Application

    Dim toMulti, waarde As String

    Dim mItem As Outlook.MailItem  ' An Outlook Mail item

    '********************************************************************************* command B38

    Dim dbs As Database

    Dim qdfTemp As QueryDef

    Dim qdfNew As QueryDef

    Dim originalSql As String

    Dim Identified_name As Recordset

    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb

    '********************************************************************************* command E38

    Set olApp = CreateObject("Outlook.Application")

    Set mItem = olApp.CreateItem(olMailItem)

    Dim rs  As Recordset

       Set rs = CurrentDb.OpenRecordset("Q200_email_contact")  ‘ Get name for the email distro

       If rs.RecordCount > 0 Then

       rs.MoveFirst

       Do Until rs.EOF

          With mItem

           'Set olApp = CreateObject("Outlook.Application")

              Set mItem = olApp.CreateItem(olMailItem)

              .BodyFormat = olFormatHTML

              toMulti = rs![Name product manager]   ‘ Find name in column name product manager

              waarde = toMulti

    '********************************************************************************* command B38

              For Each qdf In dbs.QueryDefs

               If qdf.Name = "test" Then

                   dbs.QueryDefs.Delete "test"

                   Exit For

               End If

              Next

              Set qdfTemp = dbs.CreateQueryDef("test")

               With dbs

                  'Run query on selected Name product manager

                 qdfTemp.SQL = " SELECT * FROM  Identified_name " _

                             & " WHERE [Name product manager] = " & As_text(waarde) _

                             & " ORDER BY [email product manager]"

                 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "test", "C:\Users\john.peacock\Documents\Data-base\Test.xlsx", True, "Data"

            End With

    '********************************************************************************* Command E38

              .To = toMulti

              MsgBox toMulti

              .CC = ""

              .Subject = "Wireless providers date - (" & WeekdayName(day) & " - " & Date & ")"

                 strbody = "Hi All,<br><br>" & _

                        "<B>Please check out the new simplified template <font face=""Times New Roman"" size=""3"" color=""blue""><I>'Template_PTT.xlsx'</I></Font> for International </B>" & _

                        "<br>" & _

                        "<br>" & _

                        "<B>Explanation file 'Template_PTT.xlsx':</B><br>" & _

                        "<br>" & _

                        "<br>" & _

                        "Regards,<br>" & _

                        "<br>" & _

                        "John"

              .HTMLBody = strbody & "<br>" & .HTMLBody

              .Display

             

              .Attachments.Add ("C:\Users\John.peacock\Documents\Data-base\ Test.xlsx")

      End With

        rs.MoveNext

    Loop

        Else

              MsgBox "No email address!"

        End If

        olApp.Quit

         Set olApp = Nothing

        Exit Sub

    End Sub


    Anri


    Anri

    Tuesday, May 3, 2016 5:09 PM

Answers

All replies

  • Hi Anri,

    DoCmd.SendObject won't necessarily create a "blank" email because you can either use it or the Outlook Application object you're using now. In other words, you can use the SendObject method to attach the result of the query in an email without having to create the attachment first. However, the email won't be in HTML format. If it must be in HTML format, you can set the Edit Message argument of the SendObject method to True and manually change the format of the email to HTML before sending it.

    Hope that helps...

    Tuesday, May 3, 2016 6:07 PM
  • Excellent!! It works

    Best Regards,

    Anri


    Anri

    • Marked as answer by Anri2018 Wednesday, May 4, 2016 2:51 PM
    Wednesday, May 4, 2016 2:51 PM
  • Hi Anri. Congratulations! Glad to hear you got it to work. Good luck with your project.
    Wednesday, May 4, 2016 3:01 PM