Is this idea possible?

  • Thread starter Thread starter Lisa Zimmerman
  • Start date Start date
L

Lisa Zimmerman

This isn't a problem, just a pondering about something
that might make my work processes a bit easier. In our
Access 2000 database, we store student email addresses. Of
course, when we click on the link in the individual
record, we are automatically taken to our email program to
write an email to the student. Is there some way to have
Access auotmatically send a group of email addresses to
our email program for a "bulk" email? For example, if we
have a set of students who have indicated that they will
complete their degrees in the Fall of 2004 and who have
not filed for graduation, we set up a query to pull those
students out and set up mailing labels for them. Rather
than print mailing labels, I'm wondering if it's possible
to set something up that could send all the email
addresses for the group to an open "Mail To" screen in our
email program, then we could write the email ourselves,
rather than having an automatically generated email.

Thanks for your suggestions, thoughts, ideas!!!

Lisa
 
It's possible, have a look at the code to get an idea,
regards John Buckett

Private Sub SendNoteCommand_Click()
Dim count As Integer, AttCountTemp As Integer
Dim varAttach As Variant, Who As String, BCList As
Variant
Dim SendString As String, BuiltNote As String
Dim AttachString As String
Dim myOlapp As Object
Dim OutlookWasNotOpen As String, NumbereMails As Integer
Dim PersonEmail As Variant
On Error GoTo Err_SendNoteCommand_Click
Dim VarSysInd As String, Counter As Integer
NumbereMails = Nz(Me.DistributionList.ListCount, 0)
If NumbereMails <= 0 Then
MsgBox "Nobody to email on the distribution list"
Exit Sub
End If
If Nz(Note, "") = "" And Nz(Attach, "") = "" Then
MsgBox "Short note or an attachment required"
Exit Sub
End If
On Error Resume Next
Set myOlapp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
OutlookWasNotOpen = True
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo Err_SendNoteCommand_Click
Set myOlapp = CreateObject("Outlook.Application")
End If
Dim myItem As Object
Dim myAttachments As Attachments
VarSysInd = SysCmd(acSysCmdInitMeter, "Mail been sent,
please wait", NumbereMails)
DoCmd.Hourglass True
For Counter = 0 To NumbereMails - 1
Set myItem = myOlapp.CreateItem(olMailItem)
If Me.AttachList.ListCount >= 1 Then
Set myAttachments = myItem.Attachments
AttCountTemp = Me.AttachList.ListCount
AttCountTemp = AttCountTemp - 1
For count = AttCountTemp To 0 Step -1
myAttachments.Add "" & Me.AttachList.Column(1,
count) & "", _
olByValue, 99999, "" & Me.AttachList.Column(0,
count) & ""
Next
End If
If Nz(Me.Subject, "") <> "" Then
myItem.Subject = "" & Me.Subject & ""
End If
If Nz(Me.Note, "") <> "" Then
BuiltNote = Me.Note
End If
If Nz(Me.SignOff, "") <> "" Then
BuiltNote = BuiltNote & Chr(10) & Chr(13) &
Me.SignOff & Chr(10) & Chr(13) & " "
Else
BuiltNote = BuiltNote & Chr(10) & Chr(13) & " "
End If
If Me.BCCCheck = -1 Then
BCList = Me.DistributionList.Column(3, 0)
For count = 1 To NumbereMails - 1
BCList = BCList & ";" & Me.DistributionList.Column
(3, count)
VarSysInd = SysCmd(acSysCmdUpdateMeter, count)
Next
myItem.Body = BuiltNote
myItem.BCC = BCList
myItem.Send
GoTo Exitemail
Else
If Nz(Me.Greeting, "") <> "" Then
If Nz(Me.FirstNameCheck, 0) = -1 Then Who =
Me.DistributionList.Column(2, Counter)
If Nz(Me.SurnameCheck, 0) = -1 Then
Who = Me.DistributionList.Column(2, Counter)
& " " & Me.DistributionList.Column(1, Counter)
End If
BuiltNote = Me.Greeting & " " & Who & ", " & Chr
(10) & Chr(13) & BuiltNote
End If
myItem.Recipients.Add Me.DistributionList.Column(3,
Counter)
myItem.Body = BuiltNote
myItem.Send
VarSysInd = SysCmd(acSysCmdUpdateMeter, Counter)
End If
Next
Exitemail:
If OutlookWasNotOpen = "True" Then myOlapp.Quit
VarSysInd = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False
MsgBox "Your note/s has/have been sent"
' DoCmd.Close acForm, "eMailComp"

Exit_SendNoteCommand_Click:
Exit Sub

Err_SendNoteCommand_Click:
VarSysInd = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False
MsgBox Err.Description
Resume Exit_SendNoteCommand_Click
End Sub
 
That is a great product! But I cannot even get my
supervisor to approve sending me to an advanced Access/
database class so I can support our database myself (since
I set it up) rather than trust our not-always-reliable IT
dept. I don't see that she is going to approve an
expenditure of $500 to $1500 to use this product. It does
do exactly what I want, and more, though. Sigh.

I just need to learn how to set some of that up myself.

Thanks!
 
Lisa Zimmerman said:
That is a great product! But I cannot even get my
supervisor to approve sending me to an advanced Access/
database class so I can support our database myself (since
I set it up) rather than trust our not-always-reliable IT
dept. I don't see that she is going to approve an
expenditure of $500 to $1500 to use this product. It does
do exactly what I want, and more, though. Sigh.
Click to expand...

Do some more digging. If you already know how to send one EMail it is a short
trip to sending a bulk EMail.
 
Back
Top