Sending email with Access 2007

eacollie · Mar 13, 2014

How would I set up an Outlook email using a list of recipients from an Access table? I'd like to be able to run this from Access 2007 and not Outlook. However, once the email is set up, I'd like to be able to go into the email and add subject, body, etc. before sending the email.

Thank you.

pr2-eugin · Mar 13, 2014

You need is DoCmd.SendObject method. To get the list of email form the table, you might need to use Recordset object to loop through and get a long list of emails.

eacollie · Mar 13, 2014

Thanks pr2-eugin.
I've discovered that Access 2007 has a wizard you can use to set this up. "Collect Data." Would you possibly know if the code is available to customize? This process is what I am interested in, but I don't want to have to go through the wizard each time.

Thanks much

willknapp · Mar 13, 2014

I'm not familiar with the wizard you're referring to, but if you're looking to create and customize an email, you'll want to use the Outlook.MailItem object. You'll have to add the MS Outlook Reference in the VB Editor to make this available.

Here's a link to more info on the MailItem Object: http://msdn.microsoft.com/en-us/library/office/bb219953(v=office.12).aspx

Working with the Mailitem object is a piece of cake. The above suggestion of creating a loop to populate the To field is easy enough. The code below assumes you have a table called "Contacts" with a field called "EmailAddress":

Code:

Dim myMessage as Outlook.Mailitem
Dim db as DAO.Database
Dim rs as DAO.Recordset
 
set myMessage = Outlook.CreateItem(olMailItem)
 
Set db = Access.CurrentDB
Set rs = db.OpenRecordset("Contacts")
 
rs.MoveFirst
Do
    myMessage.To = myMessage.To & rs.EmailAddress & "; "
Loop until rs.EOF
 
myMessage.Subject = "This is my email subject
myMessage.Body = "This is the body of the email."
myMessage.Display

eacollie · Mar 13, 2014

Thanks!
I'll give it a try.

Insane_ai · Mar 14, 2014

Here is another method. I use this with a IIS6 SMTP relay that communicates with hosted Exchange services.

The strTo variable is populated by a dlookup function in a table that lists recipients in a field like this: user@domain.tld;user2@domain.tld

The other method I use to loop through a table is so close to WillKnapp's example I won't post it.

Code:

Public Sub SendEmail(ByRef strTo As String, _
                     ByRef strFrom As String, _
                     ByRef strSubject As String, _
                     ByRef strBody As String, _
                     ByRef strReplyTo As String, _
                     ByRef strSender As String, _
                     ByRef strUserName As String, _
                     ByRef strP As String, _
                     Optional ByRef strCC As String, _
                     Optional ByRef strAttachmentPath As String)
 
Dim imsg As Object
Dim iconf As Object
Dim flds As Object
Dim schema As String
Dim strProcName As String
 
    On Error GoTo SendEmail_Err
 
    strProcName = "SendEmail"
    Set imsg = CreateObject("CDO.Message")
    Set iconf = CreateObject("CDO.Configuration")
    Set flds = iconf.Fields
    ' send one copy with SMTP server (with autentication)
    schema = "[URL]http://schemas.microsoft.com/cdo/configuration/[/URL]"
    flds.item(schema & "sendusing") = cdoSendUsingPort
    flds.item(schema & "smtpserver") = "[myserver.domain.tld]"   'Internal SMTP Relay using IIS 6
    flds.item(schema & "smtpserverport") = 25
    flds.item(schema & "smtpauthenticate") = cdoAnonymous
    flds.item(schema & "sendusername") = strUserName
    flds.item(schema & "sendpassword") = strP
    flds.item(schema & "smtpusessl") = False
    flds.Update
    With imsg
        .To = strTo
        .FROM = strFrom
        .Subject = strSubject
        .HTMLBody = strBody
        .Sender = strSender
        .CC = strCC
        .Organization = "[Your Organization Name here]"
        .ReplyTo = strReplyTo
        If strAttachmentPath <> "" Then .AddAttachment strAttachmentPath
        Set .Configuration = iconf
        .Send
    End With
 
    Set iconf = Nothing
    Set imsg = Nothing
    Set flds = Nothing
 
SendEmail_Exit:
    Exit Sub
SendEmail_Err:
 
    MsgBox "Error occurred" & vbCrLf & vbCrLf & _
    "In Function:" & vbTab & strProcName & vbCrLf & _
    "Err Number: " & vbTab & Err.Number & vbCrLf & _
    "Description: " & vbTab & Err.Description, vbCritical, _
    "Error in " & Chr$(34) & strProcName & Chr$(34)
    Resume SendEmail_Exit
End Sub

Sending email with Access 2007

eacollie

Registered User.

pr2-eugin

Super Moderator

eacollie

Registered User.

willknapp

Registered User.

eacollie

Registered User.

Insane_ai

Not Really an A.I.

Similar threads

Users who are viewing this thread