E-mailing excel files from an access module

[Piers]

I use this code to create some excel files from my DB, I would like a process that will send each indivdual file to a specific e-amil address, I will hold the e-mais in a table linked to the fundgroup name. This is the code i use to create the excel files, can anyone help with the code to send these e-mails, I will be creating these files each month so also need to pass through the period through the code to ensure i pick up the correct months file to send.

Private Sub Command4_Click()
 
  Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
  Set db = CurrentDb
 
  Set rs = db.OpenRecordset("SELECT distinct FundGroup FROM tbl_EFMMI_ReportFeeder ORDER By FundGroup;")
  '**** fieldname, consider this to be how the records are grouped within the dataset.
 
  strDt = [Forms]![Form1]![Period]
 
  rs.MoveLast
  rs.MoveFirst
 
  Do While Not rs.EOF
 
  strCrt = rs.Fields(0)
 
  Set str1Sql = db.CreateQueryDef("" & strCrt, "SELECT tbl_EFMMI_ReportFeeder.*  FROM tbl_EFMMI_ReportFeeder WHERE tbl_EFMMI_ReportFeeder.FundGroup = '" & strCrt & "';")
 
 
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "" & strCrt, "C:\H\EFMMI\ " & strCrt & "_" & strDt & ".xls", True
 
  DoCmd.DeleteObject acQuery, "" & strCrt
 
  rs.MoveNext
 
  Loop
 
  rs.Close
  Set rs = Nothing
  Set db = Nothing
 
 
End Sub

[patjones]

Hi Piers,

The basic code that I use for emailing from VBA is the following:

    Dim olApp As New Outlook.Application, olMail As Outlook.MailItem
    Set olMail = olApp.CreateItem(olMailItem)
 
    With olMail
        .To = strSendTo
        .Subject = strSubject
        .ReadReceiptRequested = False
        .Body = strBody
        .Attachments.Add strAttachFile
 
        .Send
    End With
 
    Set olApp = Nothing
    Set olMail = Nothing

Here olMail is the object that represents the email that you're going to send, and the methods of this object correspond to various parameters that you're already familiar with from Outlook such as .To, .Subject, and so forth. The key method for you is .Attachments, which allows you to attach a file(s) to the message. Here, the variable strAttachFile holds the path to the file that you're sending.

You would want to implement this code after you have a file to actually send...namely after your TransferSpreadsheet call.

Let us know if this is helpful at all.

Pat