Database & E-mail question

--amadeus--

Hi folks,

I have a client who we offered a stylish web based solution to but who instead demanded one based around Excel and Access... :rolleyes:

So I'm trying to get some things to work. There were a few hurdles - writing data from an Excel form to a database, automating an email from an Excel file and creating an email from Access.

The first two I solved without too much heartache but I am a bit stuck on teh 3rd. Using this method I can create an email based on form inputs. But what I want to do is run a query and put the results into an email as a table. Writing and executing the SQL for teh query I can do but how do I put that output into an email?

All help gratefully appreciated!

Tom Dunne

--amadeus-- wrote: »

But what I want to do is run a query and put the results into an email as a table. Writing and executing the SQL for teh query I can do but how do I put that output into an email?

All help gratefully appreciated!

Would you not be better putting the results into a table, query the table and then send the e-mail based on the results?

That's the way I do it in Oracle.

--amadeus--

Thanks for a fast reply!

I can create a temp table to hold teh results of teh query but its getting those results into the email that I'm struggling with.

For example in Excel VBA I can write this:

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)


With OutMail
.To = variableEmailaddress
.Subject = variableSubject
.Body = variableString
.Attachments.Add ActiveWorkbook.FullName
.Display
End With

That creates an email and attaches the currently open workbook. What I want to do in Access VBA is the same thing, but either putting the contents of teh table into the body of the email or attaching the table to teh mail (probably as an excel workbook)

FSL

Why not create a Word Template with a table. Then from within access you can load the template, fill the table with the results of your query, add any additional information, then save as a PDF and attach it to your email.

I did this for a client in a purchase order module. The information was extracted from a database. I could resize the table and add any additional details the user entered in a free form basis after the table.

You could do the same from within Excel. The advantage is the recipient does not require Microsoft office to read the attachment.

--amadeus--

Thanks - I'll give that a try...

--amadeus--

Back again

I've made progress but there is something that must be really small and simple that is screwing me up.

Mule code as follows:

Private Sub Command0_Click()

 Dim db As Database

      Dim XLTable As TableDef
      Dim strSQL As String

      
      Set db = OpenDatabase("C:\Users\Admin\Desktop\x\x\test.mdb", False, False, "MS Access;PWD=password")

     
      Set tdfNew = db.CreateTableDef("Temp")

   With tdfNew
      .Fields.Append .CreateField("Colour", dbText)
      .Fields.Append .CreateField("Shape", dbText)
    End With

db.TableDefs.Append tdfNew

strSQL = "Insert into Temp select * from main where [colour]='red'"

db.Execute strSQL

DoCmd.OutputTo acOutputTable, "Temp", acFormatRTF, "Agenda.doc", True

  Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = "test@test.ie"
        .CC = ""
        .BCC = ""
        .Subject = "Subject Test"
        .Body = "Body text"
        .Attachments.Add "C:\Users\Admin\Desktop\x\x\agenda.doc"
        .Display
                
    End With

    'On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
      db.TableDefs.Delete "Temp"
      
End Sub

Idea at the moment is that teh administrator will amend a table and tehn push a button to send an email. Button will call a form that will prompt for a variable which will be used in teh SQL in place of teh current hard coded colour.

As you can see a Temp table is created and that is sent to a file which is in turn attached to an email. I'll add some fancy formatting and an autosave later.

Now if I run teh DoCmd.OutputTo using the Table Main it works fine, but as soon as I use the Temp table it says that it can't find teh table. When I close teh code teh table is there so it seems to not be refreshing teh table collection or something - it has teh table, it just doesn't know it!

Any bright ideas?

Thanks!

--amadeus--

Just for teh sake of closure and in case anyone else is ever trying this...

I ended up splitting it into 2 pieces of code and working off a permanent rather than temporary table.

First piece of code is an on lost focus event:

Dim db As Database
      Dim rs As Recordset
      Dim XLTable As TableDef
      Dim StrSQL As String
Set db = OpenDatabase("C:\Users\Admin\Desktop\x\x\test.mdb", False, False, "MS Access;PWD=password")
varpick = Me.Text1.Value
     
varpick = "'" & varpick & "'"
StrSQL = "insert into Temp select * from main where [colour]=" & varpick

db.Execute StrSQL

This takes the value from the textbox and populates the table with the matching values

Then the on click code:

DoCmd.OutputTo acOutputTable, "Temp", acFormatRTF, "Agenda.doc", True

  Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = "test@test.ie"
        .CC = ""
        .BCC = ""
        .Subject = "text"
        .Body = "Submission of agenda"
        .Attachments.Add "C:\Users\Admin\Desktop\x\x\agenda.doc"
        .Display
                
    End With

    'On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
     Dim StrSQL2 As String

StrSQL2 = "Delete * from Temp;"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL2
DoCmd.SetWarnings True

Writes contents of teh table to a word doc (which I'll format in teh production version of teh code, along with an auto save with a nominated name). Then creates an email and attaches the doc to it before clearing out all the values in teh table, ready for next running.

I'm sure there is an easier way but this works so I'll stick with it!