Email database to users

[JudyJones]

I have used the SendObject command in the past to email a report to a list of users but what I would like to do this time is use the list of email addresses in the database I have open to email another Access database to each user as an attachment. Can this be done?

[HansV]

You can't do that with SendObject. You'll have to automate Outlook from Access (it won't work with Thunderbird, Windows Live Mail etc.)
Before sending, you must zip the database, for Outlook blocks Access databases as attachments.

Here is some sample code:

Code: Select all

Sub SendIndividualMails()
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim olApp As Object
  Dim olMailItem As Object
  Dim blnStartOutlook As Boolean
  Dim strBody As String

  On Error Resume Next
  Set olApp = GetObject(, "Outlook.Application")
  If olApp Is Nothing Then
    Set olApp = CreateObject("Outlook.Application")
    If olApp Is Nothing Then
      MsgBox "Can't start Outlook", vbExclamation
      Exit Sub
    End If
    blnStartOutlook = True
  End If
  On Error GoTo Err_SendMail

  Set dbs = CurrentDb
  ' Substitute the name of the table or query that provides the recipients
  Set rst = dbs.OpenRecordset("tblMyTable")
  ' Loop through records
  Do Until rst.EOF
    Set olMailItem = olApp.CreateItem(0)
    ' Substitute the name of the field containing the e-mail address
    olMailItem.Recipients.Add rst!EMail
    ' Substitute an appropriate subject
    olMailItem.Subject = "Notice"
    ' Substitute an appropriate message
    strBody = "Please see the attached database"
    olMailItem.Body = strBody
    ' Substitute the full path and filename of the zip file
    olMailItem.Attachments.Add "C:\Access\MyDatabase.zip", 1
    olMailItem.Send
    rst.MoveNext
  Loop

Exit_SendMail:
  On Error Resume Next
  rst.Close
  Set rst = Nothing
  Set dbs = Nothing
  Set olMailItem = Nothing
  If blnStartOutlook Then
    olApp.Quit
  End If
  Set olApp = Nothing
  Exit Sub

Err_SendMail:
  MsgBox Err.Description, vbExclamation
  Resume Exit_SendMail
End Sub

A separate message will be sent to each recipient.

[JudyJones]

Will this work with GroupWise email?

[HansV]

No, this code will work only with Outlook.

I think that it is possible to do something similar with GroupWise, but I cannot help you with that, I have no experience with GroupWise whatsoever. Sorry!

[JudyJones]

Thanks for your response. I think I will use the following code to send an email to each person

DoCmd.SendObject acSendNoObject, , , Employee, , , "Instructions to Users", Me.txtMsg, True

By using True at the end for the syntax [Edit Message] then each email would appear on my screen and I could paste the database in as attachment before clicking send. At least then I would not have to type in each email address.

[HansV]

Would it be possible for you to place the database on a website, or if this is within your own organization, on a shared network folder? If so, you could send a link to the database so that the recipients can download / copy it themselves.

[JudyJones]

The problem is that this needs to go to each school principal and each school has its own server. There is no one place that all principals could access. The alternative would be to copy the database to each school server using a batch file and then the email would just give them the instructions and the link to the database.

[HansV]

It's up to you to decide - I don't know how much work it'd be to attach the file manually to each e-mail message.

[JudyJones]

It is the very same file so I could copy it to the clipboard and simply paste it into each email as an attachment.

[HansV]

Oh, OK.