Working with Outlook using MS Access 2003 - VBA

Question

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:

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?
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

your first question may get an answer at the [code review](http://codereview.stackexchange.com/). The answer for the second question [here](http://stackoverflow.com/questions/2533066/vba-outlook-mail-display-recording-when-if-sent-manually) — , Aug 23 '13 at 10:51
There's something weird there, first how do you declare that mailitem? And why do you set your mailItem object as a new note? What's happening if you dont — Machinegon, Aug 23 '13 at 13:33

Machinegon · Accepted Answer · 2013-08-23T13:51:34.037

1

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

edited Aug 23 '13 at 13:51

answered Aug 23 '13 at 13:44

Machinegon

1,855
1
28
45

I did it like this because I was following an example from the Access 2007 VBA Programmers Reference by Hennig, Cooper etc. Will investigate and try your code – Katana24 Aug 23 '13 at 13:47
I am I just haven't posted that part of the code - also when adding your code instead of mine and removing the reset part in the loop I still hit the same error. That link was a good read though... – Katana24 Aug 23 '13 at 13:53
@Katana24 See my edit above, I removed the reset part and just set the object inside the loop instead – Machinegon Aug 23 '13 at 13:55

Working with Outlook using MS Access 2003 - VBA

1 Answers1