Solved

Batch email from access query

Posted on 2002-05-30
9
394 Views
Last Modified: 2012-05-04
I need to query a DB and return a list of email addresses.  Then I'd like to be able to put all those addresses into the "To" field of an email.  
I can see how to send individual mails, but I can't figure out how to build a single mail with many recipients.
0
Comment
Question by:bkosterm
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7044677
You have of course the problem of your query, but you will also come to a quite small limit of emails in the TO/CC/BCC field.
I suggest that you don't try to put more than 10 recipients automatically...

The question is (as you know how to send individual mails), where is your problem putting more than 1 recipient to the TO field?
CHeers
0
 
LVL 12

Accepted Solution

by:
James Elliott earned 200 total points
ID: 7044708
Try something like this

Dim ol As New Outlook.Application
Dim olMail As Outlook.MailItem
Dim strSubject As String
Dim strMSG As String

strSubject = "Subject goes here"
strMSG = "Dear Person" & vbCrLf & vbCrLf
strMSG = strMSG & "Message Body goes here"

Set olMail = ol.CreateItem(olMailItem)


With olMail
    .To = "Address@Email.com"
    '.Attachments.Add "c:\sometextfile.txt"
    '.Attachments.Add "c:\anothertextfile.txt"
    .Subject = strSubject
    .Body = strMSG
    '.Display
    '.Importance = olImportanceHigh
    .Send
End With

Set olMail = Nothing
Set ol = Nothing

you could then replace the email address above with a variable or number or variables that you would have defined from your query. You would have to be careful of the syntax though. The whole line must be a string but contained within that string must be the ';' symbol between the addresses.

Alternatively you could a loop that ran this script for each mail address in your query. It would take up a lot more resources though. Again you would use a variable for the Email address and just loop the process through for every record in question.

Does this help?

Thanks

Jell



0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 7044914
In the past I've had trouble simply adding a semi-colon delimited list of addresses to the Recipents object, so I resorted to using this routine to add the addresses one by one...

Sub AddToRecipients(sToEmail As String, objMailMessage As Outlook.MailItem, objOutlookRecip As Outlook.Recipient)
  '============================================================
  '     Purpose: Add a semi-colon delimited list of email address to the To: header
  '  Parameters: sToEmail - the delimited list. Each email address is delimited by a semi-colon
  '              objMailMessage - an initialised MailItem object
  '              objOutlookRecip - the recipient object that recieves the emails addresses
  '============================================================
Dim vTemp As Variant
Dim n As Integer
   
    n = 1
    vTemp = Field(sToEmail, ";", n)
    With objMailMessage
        While Not IsNull(vTemp)
            If bVerifyEmailAddress(CStr(vTemp)) Then
                Set objOutlookRecip = .Recipients.Add(CStr(vTemp))
                objOutlookRecip.Type = olTo
            Else
                MsgBox "The email address " & vTemp & "@appears to be invalid.@Please check it and add it manually to the message.", vbInformation, "Email Address Error"
            End If
            n = n + 1
            vTemp = Field(sToEmail, ";", n)
        Wend
    End With
End Sub

So to use this you first create a MailItem and a Recipients object, build a semi-colon delimited list of email addresses, and then call AddToRecipients.

The above routine uses two other helper functions,

Field() which picks out elemets from a delimited list, and bVerifyEmailAddress() which does what it says.

Here is Field()

Function Field(ByVal strSource As String, strSep As String, intN As Integer) As Variant
'Purpose:
'   Returns the Nth element in a delimited list.
'   Input: strSource - the list to search
'             strSep - the delimiter of the list - can be more than one character
'             intN - the ordinal value of the element to be returned
'
' Null is returned if either string parameter is null, or intN <=0 or
' If the separator string is not found
'
'Str = Field("Chuck*Roberts","*",1) would return "Chuck".
Dim strResult As String
Dim strSearch As String
Dim I As Long
Dim lSep As Long
Dim lRightChars As Long

    lSep = 0: I = 0
    If IsNull(strSource) Or strSource = "" Or IsNull(strSep) Or strSep = "" Or intN <= 0 Then
        strResult = ""
    Else
        strSearch = strSource
        While I < intN
            lSep = InStr(strSearch, strSep)
            If lSep > 0 Then ' we found the delimiter string
                I = I + 1 ' count occurance
                If I = intN Then ' this is the one we want
                    strResult = Left$(strSearch, lSep - 1)
                End If
                ' strip off i'th field
                strSearch = Right(strSearch, Len(strSearch) - (lSep + Len(strSep) - 1))
            Else ' did not find our separator string, so return the remainer of the string if the count is ok
                If I = intN - 1 Then '  we have seen N-1 separator strings, so this is the field we want
                                                ' at the end of the search string
                    I = I + 1 ' to terminate the While loop
                    strResult = strSearch
                Else    ' there were less than N-1 fields in the input to return Null
                    strResult = ""
                    I = intN
                End If
            End If
        Wend
    End If
    If strResult = "" Then
        Field = Null
    Else
        Field = strResult
    End If
End Function

and here is bVerifyEmailAddress()

Function bVerifyEmailAddress(CM_Email As String)
' Verify if email address is valid
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
       
    If Not isblank(CM_Email) Then
        ' 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.
             Set objOutlookRecip = .Recipients.Add(CM_Email)
            ' Resolve each Recipient's name.
            For Each objOutlookRecip In .Recipients
                If Not objOutlookRecip.Resolve = True Then
                    objOutlookRecip.Delete
                    bVerifyEmailAddress = False
                Else
                    bVerifyEmailAddress = True
                End If
            Next
        End With
        Set objOutlookRecip = Nothing
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing
    Else
        bVerifyEmailAddress = False
    End If
End Function
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Expert Comment

by:j1skinner
ID: 7045657
You stated that you dont have a problem creating the email to send it is just creating a list of multiple recipients.  This will work to create the distribution list based on your query.



Private Sub Command6_Click()
'[me.emailaddress] is the individuals email address on my form.
'[me.text4] is just a text box I used to test what would output.
'This works I am using it now for a distribution list and it is simple.


Dim emaillist 'This will be your email list.
DoCmd.GoToRecord , , acFirst 'Locate the first record in a record set.

'I used this next line to tell me when I was at the end of my records.  It works for me.
Do While Me.NewRecord = False

'This adds records email address and will loop through all records until it reaches the last record(new).
emaillist = emaillist & "; " & Me.EmailAddress
DoCmd.GoToRecord , , acNext
emaillist = emaillist & "; " & Me.EmailAddress
Loop

'I simply am outputting this to a text box on my form but you could easily put this in you "TO:" recipients.

Me.Text4 = emaillist

End Sub
0
 
LVL 12

Expert Comment

by:Paurths
ID: 7049003
by now (june 2, 2002) , using the outlook library is pretty much 'asking for trouble', since versions of ms office change more often than Bill Gates changes underware.

Instead i would go for the real job, sent your mails directly via SMTP

In the next link u will find a component u have to register on your comp before u can use it; http://www.freevbcode.com/ShowCode.Asp?ID=109

if u have trouble using it, just ask away,

cheers
Ricky
0
 
LVL 12

Expert Comment

by:Paurths
ID: 7049006
j1skinner,

hi, welcome to EE (u almost are a dinosaur here)
In many TA (not only the Access TA) it is common practice to only post comments.
That way the Q dont get 'lost' in the 'Locked questions', not very frequently visited by experts, thus decreasing the chance for the questioner to receive the help(s)he needs.

not meant to offend, only to help,

cheers
Ricky
0
 
LVL 7

Expert Comment

by:ildc
ID: 7135938
Question dead ?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7242268

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - Answered by: jell
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7269226
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now