Help with Email VBA (1 Viewer)

Jonny45wakey

Member
Local time
Today, 13:58
Joined
May 4, 2020
Messages
40
Hi all

I have the following code behind the On Click Event of a command button on a form, the code basically generates an email and looks up email addresses in a query called "qryGATE2_Email", currently there is only one email address in the query but if there is more than 1 email i get an error message.

Run-time error '91'
Object variable or With block variable not set

The email still sends but throws up the above error

Any ideas?

Thanks

Jonny

Private Sub Command96_Click()
Dim olLook As Object 'Start MS Outlook
Dim olNewEmail As Object 'New email in Outlook
Dim strContactEmail As String 'Contact email address
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("qryGATE2_Email", dbOpenSnapshot)

Set olLook = CreateObject("Outlook.Application")
Set olNewEmail = olLook.createitem(0)

With rsEmail
.MoveFirst

Do Until rsEmail.EOF
If IsNull(.Fields(2)) = False Then


stEnquiryID = Me.EnquiryID
stCustomer = Me.Customer


olNewEmail.To = .Fields(2)
olNewEmail.SentOnBehalfOfName = "BusinessManagementSystem@doitwell.co.uk"
olNewEmail.body = "ENQUIRY No:" & " " & Me.EnquiryID.Value & " " & "CUSTOMER:" & " " & Me.Customer.Value & " " & "Requires Credit Checks to be carried out, please liaise with the Sales Department in relation to this Customer Enquiry - - - - - PROJECT MOVED TO GATE 2 (FEASIBILITY)"
olNewEmail.Subject = "GATE 1 Pre-Order Enquiry Approved"
'If Me.chkAttachXLS = True Then olNewEmail.attachments.Add strFilepath
olNewEmail.display
olNewEmail.send
Set olLook = Nothing
Set olNewEmail = Nothing
End If
.MoveNext
Loop
End With
Me.LIVEGATE.Value = "GATE 2"
Me.Gate1Approved = Environ("USERNAME")
Me.Gate1DateApp = Now()
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:58
Joined
Sep 21, 2011
Messages
14,052
And the line where the error happens is? :(
 

Minty

AWF VIP
Local time
Today, 13:58
Joined
Jul 26, 2013
Messages
10,355
Put the value into a variable then use that - the outlook object doesn't understand the recordset fields reference.

SQL:
 Option Compare Database
Option Explicit

Private Sub Command96_Click()
    Dim olLook As Object 'Start MS Outlook
    Dim olNewEmail As Object 'New email in Outlook
    Dim strContactEmail As String 'Contact email address
    Dim MyDb As DAO.Database
    Dim rsEmail As DAO.Recordset
    Dim sToName As String
   
    Dim strEmailTo As String



    Set MyDb = CurrentDb()
    Set rsEmail = MyDb.OpenRecordset("qryGATE2_Email", dbOpenSnapshot)

    Set olLook = CreateObject("Outlook.Application")
    Set olNewEmail = olLook.createitem(0)

    With rsEmail
        .MoveFirst

        Do Until rsEmail.EOF
            If IsNull(.Fields(2)) = False Then
                stEnquiryID = Me.EnquiryID
                stCustomer = Me.Customer
               
                strEmailTo = .Fields(2)

                olNewEmail.To = strEmailTo
                olNewEmail.SentOnBehalfOfName = "BusinessManagementSystem@doitwell.co.uk"
                olNewEmail.body = "ENQUIRY No:" & " " & Me.EnquiryID.Value & " " & "CUSTOMER:" & " " & Me.Customer.Value & " " & "Requires Credit Checks to be carried out, please liaise with the Sales Department in relation to this Customer Enquiry - - - - - PROJECT MOVED TO GATE 2 (FEASIBILITY)"
                olNewEmail.Subject = "GATE 1 Pre-Order Enquiry Approved"
                'If Me.chkAttachXLS = True Then olNewEmail.attachments.Add strFilepath
                olNewEmail.display
                olNewEmail.send
                Set olLook = Nothing
                Set olNewEmail = Nothing
            End If
            .MoveNext
        Loop
    End With
    Me.LIVEGATE.Value = "GATE 2"
    Me.Gate1Approved = Environ("USERNAME")
    Me.Gate1DateApp = Now()
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:58
Joined
Sep 21, 2011
Messages
14,052
Really would be better if you indented and used code tags? :(
Some variables are also not declared?

I get
1669387505643.png


Try
Code:
olNewEmail.To = .Fields(2).Value
Code:
Private Sub Command96_Click()
    Dim olLook As Object    'Start MS Outlook
    Dim olNewEmail As Object    'New email in Outlook
    Dim strContactEmail As String    'Contact email address
    Dim MyDb As DAO.Database
    Dim rsEmail As DAO.Recordset
    Dim sToName As String

    Set MyDb = CurrentDb()
    Set rsEmail = MyDb.OpenRecordset("qryGATE2_Email", dbOpenSnapshot)

    Set olLook = CreateObject("Outlook.Application")
    Set olNewEmail = olLook.createitem(0)

    With rsEmail
        .MoveFirst

        Do Until rsEmail.EOF
            If IsNull(.Fields(2)) = False Then


                stEnquiryID = Me.EnquiryID
                stCustomer = Me.Customer


                olNewEmail.To = .Fields(2)
                olNewEmail.SentOnBehalfOfName = "BusinessManagementSystem@doitwell.co.uk"
                olNewEmail.body = "ENQUIRY No:" & " " & Me.EnquiryID.Value & " " & "CUSTOMER:" & " " & Me.Customer.Value & " " & "Requires Credit Checks to be carried out, please liaise with the Sales Department in relation to this Customer Enquiry - - - - - PROJECT MOVED TO GATE 2 (FEASIBILITY)"
                olNewEmail.Subject = "GATE 1 Pre-Order Enquiry Approved"
                'If Me.chkAttachXLS = True Then olNewEmail.attachments.Add strFilepath
                olNewEmail.display
                olNewEmail.send
                Set olLook = Nothing
                Set olNewEmail = Nothing
            End If
            .MoveNext
        Loop
    End With
    Me.LIVEGATE.Value = "GATE 2"
    Me.Gate1Approved = Environ("USERNAME")
    Me.Gate1DateApp = Now()
End Sub
 

Users who are viewing this thread

Top Bottom