Sending Emails through Access

  • Thread starter Thread starter aali
  • Start date Start date
A

aali

Hi there,
This is my first post in the forum so I will try to explain the
problem as well as I can. Now here is what I am trying to do. I have
created an Access DB to keep track of different project estimates.

What I want to do is to create a form where I can go check off using
the checkbox the estimators that have entered estimators and then send
reminder emails to the ones that haven't entered in their estimates
yet.

So far I have created four tables,
1. ScopingMeeting (project ID, meeting ID and meeting date)
2. MeetingAttendee (MeetingAttendeeID, DeptID, AttendeeEmail,
AttendeeName)
3. ScopingFunctionalDept (FunctionalDeptID, FunctionalDept,
ManagerName, ManagerEmail)
4. MeetingAttendeeMM (AttendeeID, Meeting IDFK, AttendeeIDFK, Estimate
(checkbox) )

Now I don't know what code should be used to create this form where I
can just select everyone who has entered their estimates and then hit
a button to send a reminder email to everyone else including their
dept manager. Any help would be greatly appreciated.
Thanks!
 
1. Create a query that lists everyone--Output should be Attendeename,
Attendeeemail, Manageremail, Estimate. ("qryEmailList")

2. Create a form ("frmEmailList") and add a list box control ("lstEmail).
using the listbox wizard, select the query you just created ("qryEmailList")
as the Row Source

3. In the property window for the list box control, go to the "Other" tab
and select "Simple" next to the Multi Select property.

4. Add a Command Button to your Form

5. Add the following code for the OnClick Event of you Command Button
-----
Dim ctlList As Control, varItem As Variant
Dim AAddress As String, MAddress As String

'see if there are any items selected
If lstEMail.ItemsSelected.count = 0 Then
MsgBox "Please select at least one contact to send a notice to."
lstEMail.SetFocus
Else
Set ctlList = Forms!frmEmaillist!lstEMail
'for each item in the list that is selected...
For Each varItem In ctlList.ItemsSelected
' Get value of columns.
'Columns in the list box start with 0 - in this case the
AttendeeName
AAddress = ctlList.Column(1, varItem)
MAddress = ctlList.Column(2, varItem)
'SendEmail True, AAddress, MAddress
Next varItem

End If
-----------
6. Add a Reference to MS Outlook: Click ALT+F11 --> Tools --> References
&. Create a new module and add the following code
-------------
Public Function SendEMail2(RecipientTo As String, CopyTo As String)
'open MS Outlook with the TO: address already filled in

Dim objOutlook As Outlook.Application
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookmsg As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookmsg = objOutlook.CreateItem(olMailItem)

With objOutlookmsg

' Add the To recipient(s).
Set objOutlookRecip = .Recipients.Add(RecipientTo)
objOutlookRecip.Type = olTo

' Add the CC recipient(s).
Set objOutlookRecip = .Recipients.Add(CopyTo)
objOutlookRecip.Type = olCC

' Set the Subject, Body, and Importance.
.Subject = "Late Estimate"
.Body = "Please Send you Estimate ASAP."
.Importance = olImportanceHigh 'High importance

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

.Display ' UnComment this line to display the message in outlook - must
click Send in outlook
'.Send ' Uncomment this line to send the email without displaying it
in Outlook
End With
Set objOutlook = Nothing
End Function
 
1. Create a query that lists everyone--Output should be Attendeename,
Attendeeemail, Manageremail, Estimate. ("qryEmailList")

2. Create a form ("frmEmailList") and add a list box control ("lstEmail).
using the listbox wizard, select the query you just created ("qryEmailList")
as the Row Source

3. In the property window for the list box control, go to the "Other" tab
and select "Simple" next to the Multi Select property.

4. Add a Command Button to your Form

5. Add the following code for the OnClick Event of you Command Button
-----
Dim ctlList As Control, varItem As Variant
Dim AAddress As String, MAddress As String

'see if there are any items selected
If lstEMail.ItemsSelected.count = 0 Then
MsgBox "Please select at least one contact to send a notice to."
lstEMail.SetFocus
Else
Set ctlList = Forms!frmEmaillist!lstEMail
'for each item in the list that is selected...
For Each varItem In ctlList.ItemsSelected
' Get value of columns.
'Columns in the list box start with 0 - in this case the
AttendeeName
AAddress = ctlList.Column(1, varItem)
MAddress = ctlList.Column(2, varItem)
'SendEmail True, AAddress, MAddress
Next varItem

End If
-----------
6. Add a Reference to MS Outlook: Click ALT+F11 --> Tools --> References
&. Create a new module and add the following code
-------------
Public Function SendEMail2(RecipientTo As String, CopyTo As String)
'open MS Outlook with the TO: address already filled in

Dim objOutlook As Outlook.Application
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookmsg As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookmsg = objOutlook.CreateItem(olMailItem)

With objOutlookmsg

' Add the To recipient(s).
Set objOutlookRecip = .Recipients.Add(RecipientTo)
objOutlookRecip.Type = olTo

' Add the CC recipient(s).
Set objOutlookRecip = .Recipients.Add(CopyTo)
objOutlookRecip.Type = olCC

' Set the Subject, Body, and Importance.
.Subject = "Late Estimate"
.Body = "Please Send you Estimate ASAP."
.Importance = olImportanceHigh 'High importance

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

.Display ' UnComment this line to display the message in outlook - must
click Send in outlook
'.Send ' Uncomment this line to send the email without displaying it
in Outlook
End With
Set objOutlook = Nothing
End Function
Click to expand...


Hi Lisa,
Thanks for the reply, I have tried implementing your code. When I hit
the command button with the email address selected nothing happens. Do
I need to add a reference to the module code in the on_click event?
Any help would be greatly appreciated.
Thanks!
 
Back
Top