Email database to users
-
- StarLounger
- Posts: 72
- Joined: 08 Mar 2010, 13:05
- Location: Manassas, VA
Email database to users
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?
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email database to users
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:
A separate message will be sent to each recipient.
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
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 08 Mar 2010, 13:05
- Location: Manassas, VA
Re: Email database to users
Will this work with GroupWise email?
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email database to users
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!
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!
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 08 Mar 2010, 13:05
- Location: Manassas, VA
Re: Email database to users
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.
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.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email database to users
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 08 Mar 2010, 13:05
- Location: Manassas, VA
Re: Email database to users
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.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email database to users
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 08 Mar 2010, 13:05
- Location: Manassas, VA
Re: Email database to users
It is the very same file so I could copy it to the clipboard and simply paste it into each email as an attachment.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands