Automate Emails from Query - SendObject

[Kennertoy]

I have a Access 2000 DB where we would like to send emails to recipients from our data base with an accompanying letter. We had hoped that we could create and run a query to filter recipients, then using that data send a word document to all recipients listed in the query (in groups (50) emails at a time to the server. Our server blocks any more as spam). We have up to groups of 250 to send. Also, the mailserver will not accept the bulk email if there is a mistake in any of the email address destined for a local recipient (on the same mail server). In other words I have to trap the error and correct the incorrect email address before the job will complete thru the mail server.
It is a bit beyond my expertise to program. And maybe it's a bit of pie in the sky.
Would someone from the forum would have had this challenge?


Your help would be appreciated.

[June7]

It could be done. This link shows how to send a single email http://forums.aspfree.com/microsoft-...ro-447084.html

Sending multiple emails would require using coded SQL recordset, read records from the recordset and either send one at a time or compile a string of addresses for the To property. With either will require looping code structure.

[Kennertoy]

Thanks Jane, I found dmoe code that works but only to one email address. Now I'll have to find a way to capture all my email addresses out of the qry and patch them into this code. Any ideas?

[Kennertoy]

Sorry about that 'JUNE7'

[Kennertoy]

A bit new with this posting. June7 I forgot to show the code:
The recipient 'Add("Nancy Davolio")' is where I have to make a chnage send the email to numerous people.
------------------------------------------
Sub sbSendMessage(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

On Error GoTo ErrorMsgs

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message. Substitute
' your names here.
Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
objOutlookRecip.Type = olCC
' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "Last test." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing

Exit Sub

ErrorMsgs:
If Err.Number = "287" Then
MsgBox "You clicked No to the Outlook security warning. " & _
"Rerun the procedure and click Yes to access e-mail " & _
"addresses to send your message. For more information, " & _
"see the document at http://www.microsoft.com/office" & _
"/previous/outlook/downloads/security.asp. "
Else
MsgBox Err.Number & " " & Err.Description

-------------------------------------

[June7]

Google: VBA send multiple emails

Here is one on how to send multiple individual emails http://www.access-programmers.co.uk/...d.php?t=180507

In that loop you can have code to verify the email address and correct if needed or not send and move on to the next record.