Help with sending automated emails via Contact DB (1 Viewer)

[sleigh701]

Hi All,

I am new to Access and haven't used VB in over 15 yrs! I am trying to create automated emailing with the click of a button in Access. I had success with a previous database; however this one is giving me a headache. :banghead:

Option Compare Database
Option Explicit
Private Sub CleanUp()
' Clean up public object references.
Set outlookNamespace = Nothing
Set outlookApp = Nothing
End Sub

Private Sub Command29_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Eml_Reminder_RecordSet")
With rs
If .EOF And .BOF Then '(No Records found for this query.)
MsgBox "There are no records from the query 'Eml_Reminder_RecordSet' "
Else
Do Until .EOF 'DO UNTIL END OF RECORDSET FILE

If Label72.Name = "X" Then

DoCmd.SendObject acSendNoObject, , , ![eml], , , "Access Eml", "Hello " & ![CompanyName] & ", We currently have...", False

.Edit
![EMl_Sent] = Now()
'.Update
.MoveNext

End If
Loop

End If
End With

'CLEAN UP MEMORY AT END
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
End Sub

I appreciate any and all help with this!

Thanks,
Sleigh

Note: Since I am a new member, I cannot spell out the e m a i l word; therefore where there is eml know it is actually spelled out...

 

[PNGBill]

Good idea to declare your Access version.

What issues are you facing ?
Recordset not what you expect and or problem with sending email ?

 

[Frothingslosh]

Okay, here's your code formatted a little more legibly, with some basic error handling added. Thoughts after.

Option Compare Database
Option Explicit

Private Sub CleanUp()
    [COLOR="SeaGreen"]' Clean up public object references.[/COLOR]
    Set outlookNamespace = Nothing
    Set outlookApp = Nothing
End Sub

Private Sub Command29_Click()

On Error GoTo Command29_Click_Err

    Dim rs As DAO.Recordset                 [COLOR="SeaGreen"]'Recordset used to generate emails.[/COLOR]
    Dim strProcName As String               [COLOR="SeaGreen"]'Procedure name[/COLOR]
    
    strProcName = "Command29_Click"
    
    Set rs = CurrentDb.OpenRecordset("Email_Reminder_RecordSet")
    
    With rs
        If .EOF And .BOF Then               [COLOR="SeaGreen"]'No Records found for this query.[/COLOR]
            MsgBox "There are no records from the query 'Email_Reminder_RecordSet' "
            
        Else
            Do Until .EOF                   [COLOR="SeaGreen"]'DO UNTIL END OF RECORDSET FILE[/COLOR]
            
            If [COLOR="Red"]Label72.Name[/COLOR] = "X" Then
                
                DoCmd.SendObject acSendNoObject, , , ![email], , , "Access Email", "Hello " & ![CompanyName] & ", We currently have...", False
                
                .Edit
                ![EMail_Sent] = Now()
                [COLOR="Red"]'.Update[/COLOR]
                [COLOR="Red"].MoveNext[/COLOR]
            
            End If
            Loop
            
        End If
    End With
    
Command29_Click_Exit:

   [COLOR="SeaGreen"] 'CLEAN UP MEMORY AT END[/COLOR]
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If

    Exit Sub
 
Command29_Click_Err:
    Select Case Err
        'Case YourErrNumber
 
            'Resume Command29_Click_Exit
        Case Else
            MsgBox "Error occurred" & vbCrLf & vbCrLf & _
            "In Function:" & vbTab & strProcName & vbCrLf & _
            "Err Number: " & vbTab & Err.Number & vbCrLf & _
            "Description: " & vbTab & Err.Description, vbCritical, _
            "Error in " & Chr$(34) & strProcName & Chr$(34)
            Resume Command29_Click_Exit
    End Select
    
End Sub

First off, I'd rename the controls something a little more comprehensible, but that's just me. I prefer my control names to tell me at a glance what they're for - it makes it easier to follow stuff, especially for whoever comes after me.

I'm not sure of the need for your cleanup subroutine at the top - you don't call it anywhere in the code, and it's honestly not needed for emailing stuff. I'm assuming those are two public variables declared elsewhere?

The Label72.Name seems to be self-referential. If it's a label called Label72, then its Name value will be Label72, never X. That means that the SendObject command will never fire.

You've commented out .Update. That means that when you move to the next record with .MoveNext, you lose the change you just made in Email_Sent.

You should verify that Label72 is indeed what you mean to be checking for a value (I'm guessing you wanted its linked textbox instead), and uncomment .Update. Then let us know what happens then.

Edit: Oh, hell. You need to move that .MoveNext out of the If block, too, or the first time your Label72 doesn't equal X, you enter an endless loop! It needs to get moved to just before the Loop statement.

 

[sleigh701]

Hi PNGBill,

I apologize for not replying until now; however we had a sudden and unexpected death in the family.


I am currently working in Access 2003 at work and 2007 at home. I am having a problem with getting the automated email sent when clicking on the Command29 button.


Thanks,
Sleigh701

 

[sleigh701]

Hi Frothingslosh,


I am just now replying because we had a sudden and unexpected death in the family.

I renamed the controls to something that can tell anyone at a glance what they are for.


Yes, the cleanup subroutine are two public variables declared eslewhere. Yes, now I see the Label72.Name is self-referential and have changed it since I really meant for it to be checking for a value in the linked textbox instead. Doh!


Unfortunately I am now receiving compile errors... I have opened my References and do not see any references that start with "MISSING" and I have unchecked and rechecked references hoping that will fix the problem, but it didn't work... I am beginning to think that I have forgotten most of the vb I learned many years ago! Reminds me of the saying, "if you don't use it, you lose it"...


Thanks,
Sleigh701

 

[PNGBill]

If you create a Comand Button to do a task but it doesn't work then it you need to confirm where the problem first appears.
In this case I suggest you test for your recordset working ie does the code return the correct data (what emails to send) and if this is Ok then test the Email send part.

When you build VBA, test sql's first before including in the vba code.
Insert MsgBox's into your vba to confirm the code reached a certain point and variables hold an expected value.

 

[Frothingslosh]

Well, there is one reference called 'Microsoft Office 12.0 Object Library' (12.0 is for Access 2007). I don't THINK you need it active in order to use DoCmd.SendObject, but it can't hurt to turn that one on if it isn't.

You say you're getting compile errors - when those pop up, the VBA editor takes you to the precise spot of the error. So...what is the error message and what line is getting hilighted as incorrect?

Once it compiles, then follow PNGBill's steps to find out where the procedure is breaking if it still doesn't work.

 

[sleigh701]

Hi PNGBill & Frothinslosh,

Thank you Very much guys! (Jumping for joy here!) I finally got it to working correctly after taking your advice! Woo Hoo!

Sleigh701

 

[Frothingslosh]

Glad to hear it!