ACCESS FEED: RE: [MS_AccessPros] VBA to send email, error on certian addresses?

Senin, 14 April 2014

RE: [MS_AccessPros] VBA to send email, error on certian addresses?

Hello Graham,

You know, I went looking through the members of the recipient object and totally missed that.

I tested to make sure the user was still encountering the error. Once she verified that the error was being thrown, I added in code to run the resolve method. I caught the return value and set up code to store the offending record into the audit table if resolve returned false.

Strangely enough, simply running the resolve method seemed to fix the issue. resolve always returned true and the 2 records that were previously kicking errors did not after running resolve.

Thank you for the heads up to that method. I am good to go now. I will need to add that to a bunch of email code I have. =)

Mike D

---In MS_Access_Professionals@yahoogroups.com, <graham@...> wrote :

Hi Michael

Try resolving each recipient after the .Add:

objOutlookRecip.Resolve

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Friday, 11 April 2014 19:07
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] VBA to send email, error on certian addresses?

Michael-

Interesting. I would have thought it would die at the .Recipients.Add if it didn't like the address. Would be interesting to Debug.Print the recipients in your error trap to see what it's not liking. Are these just plain old email addresses? No funny punctuation or < > or ; characters?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Apr 10, 2014, at 11:33 PM, <Michael.P.Devlin@...> <Michael.P.Devlin@...> wrote:

I have been using the same code to send emails from MS Access (using automation) through Outlook. A few weeks ago, I ran into an issue with one database that would encounter an error on a particular email address. I recreated the message that was to be sent to that user, copied the email address into the TO line in a new Outlook message and the email was sent. I removed the email address from my source table, re-ran the job and a few addresses later, another bad email address. Repeated the previous "cure" and moved on. I encountered about 5 addresses that the automation found displeasing, but Outlook did not.

I moved on with my life, until a different database, used by a different user, using a different set of email addresses started encountering the same issue.

The error message is the generic: Error -2147024809: Could not complete the operation. One or more parameter values are not valid.

The error is thrown at the .send line.

For now, I am just catching the error, writing the offensive record to an audit table and resuming the code.

Any ideas?

Win 7, Office 2010, 64 bit

Incomplete code follows, but you get the idea...
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Set objOutlook = CreateObject("Outlook.Application")

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        With objOutlookMsg
            .Subject = strSubjectline
            .Body = strMessageBody
            Set objOutlookRecip = .Recipients.Add(strEmailAddress)
            objOutlookRecip.Type = olTo

            'To preview the e-mail instead of automaticially sending it...
            'Uncomment the "Display" line
            'And comment the "Send" line

            '.Display
            .Send
        End With