Program crashes when sending email

[Gkirkup]

I am sending an email using SendObject. Sometimes it works, and sometimes it makes the computer freeze up with no error message. I have tried this with Outlook running or not running, seems to make no difference.

[FONT=Arial]'The sub procedure below sends e-mail in response to a click on the Send button.[/FONT]
[FONT=Arial]Private Sub SendMessagesButton_Click()[/FONT]
[FONT=Arial] [/FONT]
[FONT=Arial]    'For Access, define some object variables and make connections.[/FONT]
[FONT=Arial]    Dim myConnection As ADODB.Connection[/FONT]
[FONT=Arial]    Set myConnection = CurrentProject.Connection[/FONT]
[FONT=Arial]    Dim myRecordSet As New ADODB.Recordset[/FONT]
[FONT=Arial]    myRecordSet.ActiveConnection = myConnection[/FONT]
[FONT=Arial]   [/FONT]
[FONT=Arial]    'Define some object variables for Outlook[/FONT]
[FONT=Arial]    Dim appOutlook As Outlook.Application   'Refers to Outlook's Application object.[/FONT]
[FONT=Arial]    Dim appOutlookMsg As Outlook.MailItem   'Refers to an Outlook e-mail message.[/FONT]
[FONT=Arial]    Dim appOutlookRecip As Outlook.Recipient    'Refers to an Outlook e-mail recipient.[/FONT]
[FONT=Arial]        [/FONT]
[FONT=Arial]    'General variables.[/FONT]
[FONT=Arial]    Dim mySQL As String, eMailAddress As String, whereClause As String[/FONT]
[FONT=Arial]    Dim myMsg As String[/FONT]
[FONT=Arial]      [/FONT]
[FONT=Arial]    'Create an Outlook session in the background.[/FONT]
[FONT=Arial]    Set appOutlook = CreateObject("Outlook.Application")[/FONT]
[FONT=Arial]      [/FONT]
[FONT=Arial]        'Get the e-mail address from current record of myRecordset.[/FONT]
[FONT=Arial]        eMailAddress = "accounting@sectorsupply.com"[/FONT]
[FONT=Arial]    [/FONT]
[FONT=Arial]        'Create a new, empty e-mail message.[/FONT]
[FONT=Arial]        Set appOutlookMsg = appOutlook.CreateItem(olMailItem)[/FONT]
[FONT=Arial]    [/FONT]
[FONT=Arial]        With appOutlookMsg  'Using the new, empty message...[/FONT]
[FONT=Arial]            ' Address the new message.[/FONT]
[FONT=Arial]            MsgBox ("Five")[/FONT]
[FONT=Arial]            Set appOutlookRecip = .Recipients.Add(eMailAddress)[/FONT]
[FONT=Arial]            MsgBox ("Six")[/FONT]
[FONT=Arial]            appOutlookRecip.Type = olTo 'Sets message to normal outgoing e-mail message.[/FONT]
[FONT=Arial]            MsgBox ("Seven")[/FONT]
[FONT=Arial]            ' Fill in the Subject line and main body of message.[/FONT]
[FONT=Arial]            MsgBox ("Eight")[/FONT]
[FONT=Arial]            .Subject = Me.Subject     'Fill in the subject line.[/FONT]
[FONT=Arial]            MsgBox ("Nine")[/FONT]
[FONT=Arial]            .Body = Me.MessageBody  'Fill in the message body.[/FONT]
[FONT=Arial]            MsgBox ("Ten")[/FONT]
[FONT=Arial]            [/FONT]
[FONT=Arial]            .Send 'Send the completed message.[/FONT]
[FONT=Arial]            MsgBox ("Eleven")[/FONT]
[FONT=Arial]      End With[/FONT]
[FONT=Arial]      [/FONT]

I have added some MsgBox () to narrow down where it crashes. It is after 'Five' and Before 'Six'. On the line:

Set appOutlookRecip = .Recipients.Add(eMailAddress)

I am mystified as to why it works OK sometimes, and not others. The email address being used is valid.
Any suggestions?

Robert

 

[jdraw]

I set up your procedure as a standard proc. I ran it a couple of times and got an error. I then added the iterator(i) and tried to run it in a loop
(I have since xxx'd out the email address). It goes through the routine then fails on a another iteration with the error as per the attached jpg. If I rerun it repeats the error condition. Not sure why???? I'm running access 2003 Outlook 2003 on XP sp3.

Good luck.

'---------------------------------------------------------------------------------------
' Procedure : SendMessagesButton
' Author    : Jack
' Date      : 11-11-2012
' Purpose   :  Trying to repeat/resolve an error condition
'see http://www.access-programmers.co.uk/forums/showthread.php?t=236559
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
 Sub SendMessagesButton()

Dim i as integer

    'For Access, define some object variables and make connections.
    Dim myConnection As ADODB.Connection
   On Error GoTo SendMessagesButton_Error

    Set myConnection = CurrentProject.Connection
    Dim myRecordSet As New ADODB.Recordset
    myRecordSet.ActiveConnection = myConnection

    'Define some object variables for Outlook
    Dim appOutlook As Outlook.Application   'Refers to Outlook's Application object.
    Dim appOutlookMsg As Outlook.MailItem   'Refers to an Outlook e-mail message.
    Dim appOutlookRecip As Outlook.Recipient    'Refers to an Outlook e-mail recipient.

    'General variables.
    Dim mySQL As String, eMailAddress As String, whereClause As String
    Dim myMsg As String

    'Create an Outlook session in the background.
    Set appOutlook = CreateObject("Outlook.Application")

        'Get the e-mail address from current record of myRecordset.
        eMailAddress = "XXXXXXXXXXXX@gmail.com" ' "accounting@sectorsupply.com"

        'Create a new, empty e-mail message.
        Set appOutlookMsg = appOutlook.CreateItem(olMailItem)
  For i = 1 To 5
        With appOutlookMsg  'Using the new, empty message...
            ' Address the new message.
            MsgBox ("Five")
            Set appOutlookRecip = .Recipients.Add(eMailAddress)
            MsgBox ("Six")
            appOutlookRecip.Type = olTo 'Sets message to normal outgoing e-mail message.
            MsgBox ("Seven")
            ' Fill in the Subject line and main body of message.
            MsgBox ("Eight")
            [COLOR="Blue"].Subject = "TEST SUBJECT JED " & i  [/COLOR]  'Fill in the subject line.
            MsgBox ("Nine")
            .body = " Mock up of body text" 'Fill in the message body.
            MsgBox ("Ten")

            .send 'Send the completed message.
            MsgBox ("Eleven")
      End With
      Next i

   On Error GoTo 0
   Exit Sub

SendMessagesButton_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure SendMessagesButton of Module SendEmailEtc"
 End Sub

 

[Gkirkup]

Jdraw: Thanks. But I found a better way to send emails. I now use DoCmd.SendObject and it works every time. It also only gives you one pop up warning message.

Robert