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. The answer for the second question here — user2140173
– user2140173, Commented Aug 23, 2013 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
– Machinegon, Commented Aug 23, 2013 at 13:33

Machinegon · Accepted Answer · 2013-08-23 13:51:34Z

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, 2013 at 13:51

answered Aug 23, 2013 at 13:44

Machinegon

1,8852 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Katana24 Over a year ago

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 Over a year ago

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

Machinegon Over a year ago

@Katana24 See my edit above, I removed the reset part and just set the object inside the loop instead

Collectives™ on Stack Overflow

Working with Outlook using MS Access 2003 - VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related