CODEWITHSUNDEEP
ms-accessoutlookvbams-access-2003
Katana24
Katana24

Reputation: 8959

Working with Outlook using MS Access 2003 - VBA

I've made the following program that takes a list of email addresses from a table in MS Access and sends each a test email. Here is the code:

'Get data back from field birth date
    Set fld = rcdSet.Fields("Email Address")

    'Subject
    olMailItem.Subject = "Mailing List Test"

    'Loop through the records
    For i = 0 To intNumRecords
        'Recipient/s
        olMailItem.To = fld.Value

        'Body of email
        olMailItem.Body = strBodyText

        'Automatically send the email
        olMailItem.Send

       'Reset email item otherwise it won't work
        Set olMailItem = olFolder.Items.Add("IPM.Note")

        'Move to the next record
        rcdSet.MoveNext
    Next

And yes I opened a record set but haven't included that code above. So here's my questions:

  1. Is my approach above correct? I had to reset the olMailItem in the loop otherwise it would return a Type Error. Is there a better approach to sending multiple emails?

  2. I put in an invalid email to see what happens - it causes another Type Error. Is there anyway to detect the bounce back (the email outlook sends to you informing you that it was a bad address) email and send a message to the Immediate Window (For dev purposes at this point)

Thanks

Note - Have added declaration of mail items 

'Create Outlook object
Dim olApp As Outlook.Application

'Namespace
Dim olNS As Outlook.NameSpace
Dim olFolder As Outlook.MAPIFolder

'Create a reference to the email item you will use to send the email
Dim olMailItem As Outlook.MailItem

Set olApp = CreateObject("Outlook.Application")
Set olNS = olApp.GetNamespace("MAPI")

Set olFolder = olNS.GetDefaultFolder(olFolderInbox)
Set olMailItem = olFolder.Items.Add("IPM.Note")

If I don't re-set the olMailItem in the for loop an error occurs on the .To part of the program - the aforementioned Type Error

Upvotes: 0

Views: 701

Answers (1)

Machinegon
Machinegon

Reputation: 1885

I don't get the fact that you declare an object of type IPM.Note and try to send it, declare a mailitem instead.

this should work

'Loop through the records
For i = 0 To intNumRecords
     Set olMailItem = olFolder.Items.Add
    'Subject
    olMailItem.Subject = "Mailing List Test"

    'Recipient/s
    olMailItem.To = fld.Value

    'Body of email
    olMailItem.Body = strBodyText

    'Automatically send the email
    olMailItem.Send

    'Release new mailitem
    Set olMailItem = Nothing

    'Move to the next record
    rcdSet.MoveNext

Next

Checkout http://msdn.microsoft.com/en-us/library/office/bb220348(v=office.12).aspx for more info on the add method.

EDIT: see full loop

Upvotes: 1

Related Questions