asked on

Emailing from access table

I am working in Access 2003.I have a table of customers tblCustomer. I wish to filter my customers on the "DateLastEmailSent" field and send them a group email with product updates. I also wish to record the date when this email was sent in the "DateLastEmailSent" field. The email address is in the "EmailAddr" field. I would be selecting the customers on a date range for when the last email was sent i.e from date to date type query.
Is this possible and how might I go about it. Any help is appreciated. Thank you.

gincho

ASKER

I am using Outlook 2003 as my email client.

Chris Bottomley

Assuming you have an event, button or whatever that you use as the trigger then an outline solution is to collect email addresses as a string, fire off 1/many emails as necessary from the VBA event and update the sent field on the table.

Options in regard to send are many but basically boil down to:

1. Send using CDO, sent mail will not be recorded in sent mail box but can be copied to your email inbox by adding you as a recipient. Advantage is that security messages are not triggered.

2. Send using redemption, (there are alternatives) will appear as a sent message as normal but the add-in carries a cost, (for the sender) for commercial usage.

ANy thoughts?

Chris

gincho

ASKER

Thank you Chris. Your outline is perfect. CDO sounds like what I need. I do not want each recipient to see that other recipients have received the same email. Can BCC be used or is there a simpler way?
Can you help me with the code for the procedure . I do not have much VBA experience and mostly us Access wizards. Your expert help is greately appreciated. Thhanks again.
gincho

Chris Bottomley

copying from a previous question: the general form for sending is as follows, (str_BCC will be populated from your table before calling the routine newCDOMessage and the the emailed date set at much the same time):

Sub SendEmail()
Dim str_BCC As String

strHeader = "Hi," & Chr(10) & "Here are your details:" & Chr(10)
strFooter = "Please check and let me know if all details are correct." & Chr(10) & "Regards," & Chr(10) & "Gincho"

strbody = "Some random text to be communicated"
str_BCC = "Fred@fredmail.com" & ":" & "doris@dorismail.com"
NewCDOMessage "Personal Data", strHeader & strbody & strFooter, "", "", str_BCC, False

End Sub

Put the following in a module:

Public Sub NewCDOMessage(strSubject As String, strbody As String, Optional strTo As String, _
Optional strCC As String, Optional strBCC As String, Optional SendYN As Boolean, _
Optional AttachYN As Boolean = False, Optional Attach1 As String)

Dim iMsg As Object
Dim iConf As Object
' Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1 ' CDO Source Defaults
' Set Flds = iConf.Fields
' With Flds
' .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
= "MYIPSMTPSERVER"
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
' .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "MTIPACCOUNT"
' .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "MYACCTPASSWORD"

' .Update
' End With

With iMsg
Set .Configuration = iConf

.To = strTo
If strCC <> "" Then .CC = strCC
If strBCC <> "" Then .BCC = strBCC
.From = "### NEEDS TO BE ENTERED AS APPROPRIATE ###"
.Subject = strSubject
.TextBody = strbody
If AttachYN And Attach1 <> "" Then .AddAttachment Attach1
.Send
End With
End Sub

Regards
Chris

gincho

ASKER

Thyank you Chris. This looks great. How would I concatenated the email addresses in my table so that I could use them as the BCC?
gincho

ASKER CERTIFIED SOLUTION

Chris Bottomley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gincho

ASKER

Thank you Chris

Chris Bottomley

Thats great, thanks for the A grade.

Chris