Solved MS Access Outlook VBA fixing

nector

Member
Local time
Today, 06:43
Joined
Jan 21, 2020
Messages
544
I'm trying to work on this VBA which helps to send invoices to customers silently without opening outlook, but on the .Edit I'm getting an error message what could be the reason for that and how to fix it. However, this is not my code I got it on the internet, Im simply trying to see if it can solve my problem

Code:
Function GenerateEmail(MySQL As String)
On Error GoTo Exit_Function:
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
Dim MyEquipment As String
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(MySQL)
If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do Until rs.EOF
    If IsNull(rs!Email) Then
        rs.MoveNext
        
    Else
        'Set oOutlook = GetObject(,"Outlook.Application")
        If oOutlook Is Nothing Then
         Set oOutlook = New Outlook.Application
        End If
        Set oEmailItem = oOutlook.CreateItem(olMailItem)
        With oEmailItem
                MyEquipment = DLookup("Equipment", "tblMaintenance", "[id] = " & rs!Equipment)
                .To = rs!Email
                .Subject = "Reminder - PM Due in 3 days for " & MyEquipment
                .Body = "Task ID: " & rs!ID & vbCr & _
                        "Period: " & rs!Period & vbCr & _
                        "Equipment: " & MyEquipment & vbCr & _
                        "Task Description: " & rs!TaskDescription & vbCr & _
                        "PM Due: " & rs!Duedate & vbCr & vbCr & _
                        "This email is auto generated from the Maintenance Database. Please Do Not Reply!"
                '.Display email in outlook
                '.Display
                'Send email out without open Outlook, silent sent behind the screen.
                 .Edit
                .Send
                rs!DateEmailSent = Date
                rs.Update
        End With
        Set oEmailItem = Nothing
        Set oOutlook = Nothing
        rs.MoveNext
    End If
    Loop
Else
    ' do nothing
End If
rs.Close
Exit_Function:
    Exit Function
End Function
 
send invoices to customers silently without opening outlook,
Without opening outlook, your mail won't be sent.
If outlook is closed, .send saves the mail in outbox folder and will be executed only when you open outlook the next time, manually or with vba.
 
use:

rs.Edit
See the error down

1756461771481.png
 
change the definition of rs from:
Code:
Dim rs As Recordset

To
Code:
Dim rs As DAO.Recordset
 

Users who are viewing this thread

Back
Top Bottom