S
Steve
Hi All:
As a general rule, I do not cross-post messages between newsgroups. However,
due to the lack of responses of any kind, I suspect I posted this message to
the wrong group. Therefor, I apologize up front for the cross posting this
may appear to be.
I should say that I am pretty new with working with modules in Access.
Should also say that I am running Windows XP and my version of Access is
2003.
I found some code at the MS Knowledge Base that purports to do what I would
like to do, and after fiddling with it a bunch, I do have it more or less
working with a couple of exceptions. Below is the code I am using (sorry
about posting all of it, but I am not sure what part of it is causing the
problems). As you can probably tell, I am trying to loop through a recordset
and send an email to each person in the recordset with information
pertaining only to that person. The table name is tblMailingList, and the
fields are emailAddress, subject, and content.
Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![emailAddress]
With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo
' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!ccAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!ccAddress)
objOutlookRecip.Type = olCC
End If
' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = MyRS![Subject]
.Body = "Some text: " & vbCrLf & _
MyRS![content] & vbCrLf & _
"Additional content."
.Importance = olImportanceHigh 'High importance
'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
OK...the problems are:
1. I am getting multiple copies of the same email sent but not on a
consistent basis.. For instance, I may have 3 records in the record set with
email addresses of (e-mail address removed), (e-mail address removed), and (e-mail address removed). Joe will
get one email with the correct information and kim and test will each get 2
emails, but both will have the correct information in them.
2. I would like to have the module run from a macro triggered by a control
button in a form. However, when I try it, all that happens is the code
window opens, and I have to use the Run command in the tool bar to make it
run.
3. This may be related to item 2. The MS article stated to add to the
immediate window the text sendMessages. When I do and hit enter, the code
runs. However, when I close the databazse and reopen it, that snippet of
code is missing from the module.
I suspect a lot of this is pretty basic, but the solutions are escaping me,
so any help I can get would be very much appreciated.
Steve
As a general rule, I do not cross-post messages between newsgroups. However,
due to the lack of responses of any kind, I suspect I posted this message to
the wrong group. Therefor, I apologize up front for the cross posting this
may appear to be.
I should say that I am pretty new with working with modules in Access.
Should also say that I am running Windows XP and my version of Access is
2003.
I found some code at the MS Knowledge Base that purports to do what I would
like to do, and after fiddling with it a bunch, I do have it more or less
working with a couple of exceptions. Below is the code I am using (sorry
about posting all of it, but I am not sure what part of it is causing the
problems). As you can probably tell, I am trying to loop through a recordset
and send an email to each person in the recordset with information
pertaining only to that person. The table name is tblMailingList, and the
fields are emailAddress, subject, and content.
Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![emailAddress]
With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo
' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!ccAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!ccAddress)
objOutlookRecip.Type = olCC
End If
' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = MyRS![Subject]
.Body = "Some text: " & vbCrLf & _
MyRS![content] & vbCrLf & _
"Additional content."
.Importance = olImportanceHigh 'High importance
'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
OK...the problems are:
1. I am getting multiple copies of the same email sent but not on a
consistent basis.. For instance, I may have 3 records in the record set with
email addresses of (e-mail address removed), (e-mail address removed), and (e-mail address removed). Joe will
get one email with the correct information and kim and test will each get 2
emails, but both will have the correct information in them.
2. I would like to have the module run from a macro triggered by a control
button in a form. However, when I try it, all that happens is the code
window opens, and I have to use the Run command in the tool bar to make it
run.
3. This may be related to item 2. The MS article stated to add to the
immediate window the text sendMessages. When I do and hit enter, the code
runs. However, when I close the databazse and reopen it, that snippet of
code is missing from the module.
I suspect a lot of this is pretty basic, but the solutions are escaping me,
so any help I can get would be very much appreciated.
Steve