Emailing from MS Access

Below is the code, which was provided on http://support.microsoft.com/kb/318881

I did what the website recommended and it comes up with an error on line

   ' Add the To recipients to the e-mail message.
        Set objOutlookRecip = .Recipients.Add(TheAddress)

The error states:
 RunTime Error 287
Application-Defined or Object-Defined Error


-----------------------------------------------------------------------------------------------------------
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 = Forms!frmMail!Subject
        .Body = Forms!frmMail!MainText
        .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
dpokerbearAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
When you get that error, click Debug, and then type this into the Immediate window and hit Enter:


?TheAddress


What value do you get?
0
peter57rCommented:
If you compile the code does it compile without any errors?
(and do you have Option Explicit at the top of the module?  if not then add it and then try compiling)
0
YohanFCommented:
this may be a silly suggestion, but does that record in the "tblMailingList" have an email address.. Just make sure that you are not assigning a null value in there... and just check the email is in correct format..
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Patrick MatthewsCommented:
YohanF,

That was my thought as well, thus the suggestion to get the value in the Immediate window.  Could be a Null, or a zero length string, or even a concatenated list of addresses, any one of which will generate an error...

Patrick
0
JezWaltersCommented:
You might like to know that you have a coding error too.
You should be checking the 'Resolved' property (past tense) not the 'Resolve' property (present tense) to confirm that the Recipient has successfully been resolved against the address book:
If Not objOutlookRecip.Resolved Then
    objOutlookMsg.Display
End If
0
YohanFCommented:
Hi Try the method listed here, you need to have an SMTP server for this. I use this method and its nice and simple and avoid all those messages poping up from outlook...

http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_24740857.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JezWaltersCommented:
Oh yeah, and you don't need to MoveFirst after you open your Recordset - you're already at the first record.
In fact if you don't remove this statement you'll get a run-time errorr if your tblMailingList table is ever empty.
0
AxxedeCommented:
Hi,
I have attached some code that I always use to send email through MS Access, it allows you to send emails without outlook having to open.

All you need to do is set your email username/password then to add your message to the email simply set .HTMLBody = "YOUR MESSAGE"

Hope this helps.
Dim objMsg As Object
Dim objCfg As Object
Dim pstrAttach As String

Set objMsg = CreateObject("CDO.Message")
Set objCfg = CreateObject("CDO.Configuration")

With objCfg.Fields
'Port
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = "2"
'Name or IP of remote SMTP server
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.internode.on.net"
'Authentication
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = "1"
'Start TLS
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = Flase
'user name
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youremail@internode.on.net"
'email address
.Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = "youremail@internode.on.net"
'password
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword"
'Server port
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = "25"
.Update
End With
'pstrAttach = "C:\file.zip" (UNCOMMENT THIS LINE IF YOU WANT TO ATTACH A FILE)
With objMsg
.Configuration = objCfg
.From = "youremail@internode.on.net"
.To = "test@hotmail.com.au"
.BCC = ""
.Subject = "YOUR SUBJECT"
.HTMLBody = "TEST"
If Len(pstrAttach) > 0 Then .AddAttachment pstrAttach
.send
End With
pstrAttach = vbNullString

Open in new window

0
dpokerbearAuthor Commented:
it seems to all work fine when outlook is opened, but the error comes up only when it's closed.

i read somewhere that this may have to do with the reference library. I've added a library called Microsoft Outlook 12 Object Library. I am running Outlook 2007.

Now, this last comment from Axxede, that seems interesting. Is it safe to go passing the password and all those details through remote servers? If yes, then that might be a better option because i am making an application for multiple users, where they can set-up these parameters themselves... But anyway, that's possibly another question.
0
JezWaltersCommented:
The code you have uses the current namespace - which doesn't exist if Outlook isn't running.
The following code uses the MAPI namespace and works whether Outlook is running or not:

Option Explicit
Option Compare Database

Public Sub SendMessages(Optional pvarAttachment As Variant)

    ' Declare variables
    Dim appOutlook As Outlook.Application    ' Output application
    Dim attAttachment As Outlook.Attachment  ' Attachment
    Dim fldDefault As Outlook.Folder         ' Default folder
    Dim dbsCurrent As DAO.Database           ' Current database
    Dim itmMailItem As Outlook.MailItem      ' Mail item
    Dim nmsMAPI As Outlook.NameSpace         ' MAPI namespace
    Dim rcpRecipient As Outlook.Recipient    ' Recipient
    Dim rstMailingList As DAO.Recordset      ' Mailing list record set

    ' Get MAPI namespace
    Set appOutlook = New Outlook.Application
    Set nmsMAPI = appOutlook.GetNamespace("MAPI")

    ' Process mailing list
    Set dbsCurrent = CurrentDb
    Set rstMailingList = dbsCurrent.OpenRecordset("tblMailingList")
    Do Until rstMailingList.EOF

        ' Create mail item
        Set fldDefault = nmsMAPI.GetDefaultFolder(olFolderOutbox)
        Set itmMailItem = fldDefault.Items.Add(olMailItem)
        With itmMailItem
        
            ' Add recipients
            Set rcpRecipient = .Recipients.Add(rstMailingList!EmailAddress)
            rcpRecipient.Type = olTo
            If Not IsNull(Forms!frmMail!CCAddress) Then
                Set rcpRecipient = .Recipients.Add(Forms!frmMail!CCAddress)
                rcpRecipient.Type = olCC
            End If

            ' Set Subject, Body, and Importance
            .Subject = Forms!frmMail!Subject
            .Body = Forms!frmMail!MainText
            .Importance = olImportanceHigh

            ' Add attachment, if specified
            If Not IsMissing(pvarAttachment) Then
                Set attAttachment = .Attachments.Add(pvarAttachment)
            End If

            ' Resolve recipients
            For Each rcpRecipient In .Recipients
                rcpRecipient.Resolve
                If Not rcpRecipient.Resolved Then
                    itmMailItem.Display
                End If
            Next

            ' Send mail item
            .Send
        End With
        rstMailingList.MoveNext
    Loop
    Set appOutlook = Nothing
End Sub

Open in new window

0
YohanFCommented:
Axxede's solution is the same as I have given in that link. In your instance you wont be passing those info via remote servers as this is simply an application that is going to run on your desktop.

0
dpokerbearAuthor Commented:
Jez, thank you. It works great.

Yohan, your solution might be even better for what I want, but I keep getting the error, which is attached when it tries to send.
I've specified SMTP server, but for some reason it can't connect to it. Any ideas?

Thanks.
error.JPG
0
YohanFCommented:
What is your security settings on the SMTP server, does it allow you to connect and send emails without giving username and password.

check the port your SMTP server uses as well. Try and telnet your SMTP server from command line and send an email. If it connects to your SMTP and send an email this should work. Also you need CDO for this to work, however if you have outlook installed that should not be a problem as outlook installs CDO as well.
0
dpokerbearAuthor Commented:
it needs username and password. i'll have to add those two lines in. will try it tomorrow and let you know. thanks.
0
JezWaltersCommented:
It may not be an issue for you, but having the account credentials explicitly listed in your code isn't very secure.
if anyone can gain access to the code, they'll be able to establish the SMTP server username and password.
0
dpokerbearAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.