Old question but new twist.... (1 Viewer)

mgmercurio

Member
Local time
Today, 10:10
Joined
Jan 18, 2021
Messages
55
Vlad, you said earlier for me to put your Outlook Automaton Function in a module and then just replace my Docmd.sendobject with a call to your function...I put the following code in my email button and it is returning a compile error "Argument Not Optional"
Private Sub bt_SendEmail_Click()

Dim strEmail As String
Dim var As Variant

With Me.EmailListBx1
For Each var In .ItemsSelected
strEmail = strEmail & .ItemData(var) & ";"
Next
Call vcSendEmail_Outlook_With_Attachment
End With

(commented out) 'MsgBox strEmail

(commented out) 'DoCmd.SendObject acSendNoObject, , , strEmail, , , "This is a test message subject", "This is the body of my email message. Hello World.", True


End Sub
 

bastanu

AWF VIP
Local time
Today, 07:10
Joined
Apr 13, 2010
Messages
1,401
I think we need to slow down a bit, sorry if I tried to push you to fast.

To go back to the previous problem, the arlRefreshCheckBoxes() sub needs to be in the form's module (the main form that has the checkboxes and everything) as a stand alone sub, paste it at the end. Because it is using Me. it cannot be a Public Sub (like Arnel's function), it has to be Private which means it has be reside in the same (class) module from where is being called from, in your case the form's Load event and the five check boxes AfterUpdate event.

Now for the email part, you should take the Call vcSendEmail_Outlook_With_Attachment out from within the With statement and put it before or after your commented out Docmd.SendObject.

Please review this link for some hints on how to pass arguments to a sub or function:

You need to provide the sub (vcSendEmail...) with its required (and optional) arguments, see the color coding:

Function vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)

To simulate your old code it would look something like this:
Call vcSendEmail_Outlook_With_Attachment ("This is a test message subject",strEmail,"","",dLookup("DocPath","tbl_Attachtments","[AttachFile]=True),"This is the body of my email message. Hello World.")

It would help if you could get the sample db uploaded.

Cheers,
Vlad
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:10
Joined
May 7, 2009
Messages
19,175
i modified the code of vlad:
Code:
Function vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional vAttachment As Variant, Optional sBody As String)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim i As Integer
 
    Set OutApp = CreateObject("Outlook.Application")
    'OutApp.Session.Logon
 
    Set OutMail = OutApp.CreateItem(0)
 
    OutMail.To = sTo
    If sCC <> "" Then OutMail.CC = sCC
    If sBcc <> "" Then OutMail.BCC = sBcc
    OutMail.Subject = sSubject
    If sBody <> "" Then OutMail.HTMLBody = sBody
    
    'MsgBox VarType(vAttachment)
    ' 9 = recordset (object)
    ' 8 = string
    ' 8200 =  array of strings
    
    Select Case VarType(vAttachment)
        Case Is = 9
            With vAttachment
                If Not (.BOF And .EOF) Then
                    .MoveFirst
                End If
                Do Until .EOF
                    OutMail.Attachments.Add .Fields(0).Value
                    .MoveNext
                Loop
            End With
            
        Case Is = 8
            OutMail.Attachments.Add (vAttachment)
            
        Case Is = 8200
            For i = LBound(vAttachment) To UBound(vAttachment)
                OutMail.Attachments.Add vAttachment(i)
            Next
    End Select
    
    OutMail.Display  'Send | Display
'    OutMail.Inspector.Activate
    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

now you pass a recordset to the Email function:
Code:
Private Sub bt_SendMail_Click()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("select DocPath From tbl_Attachments Where [yesNoField] = -1")
    If Not (rs.BOF And rs.EOF) Then
        Call vcSendEmail_Outlook_With_Attachment("subject", "agpuzon@gmail.com", , , rs, "the Bodyshop")
    Else
        Call vcSendEmail_Outlook_With_Attachment("subject", "agpuzon@gmail.com", , , , "the Bodyshop")
    End If
    rs.Close
    Set rs = Nothing

End Sub
 

mgmercurio

Member
Local time
Today, 10:10
Joined
Jan 18, 2021
Messages
55
Sorry Arne and Vlad...I have been SWAMPED today! My phone started ringing at 6am...woke me up out of bed, and I have literally been on the phone dealing with clients all day....just wrapped up and it is almost 730pm here...plus it is end of month for us sales flunkies...so its crunch time trying to get our numbers in before deadline. Anyway, I am going to carve out some time later this evening...or maybe in the morning to take your suggestions and get them applied. I will report back once I get it all wrapped up. Thanks! -mgm
 

mgmercurio

Member
Local time
Today, 10:10
Joined
Jan 18, 2021
Messages
55
*****UPDATE*****

Vlad, I implemented your arlRefreshCheckBoxes() code and made the changes you instructed and it is now working perfectly.

Arne, I also implemented your updated code to Vlad's function ( Call vcSendEmail_Outlook_With_Attachment ) and it works. I can now attach the PDF from the first checkbox to the email and it works perfectly. However, one glitch....it won't attach any files as a single or multiple attachment from any of the other four check boxes. It will only attach a PDF from the first check box. I have include a screenshot of the checkboxes. Also, I tried to implement your code to pass the recordset and it throws an error...also attached. What am I doing wrong?

note - You will see in the screenshots attached, that I commented out the Docmd... statement to implement Arne's code. This was just a test. If I go back in and comment out Arne's code and reinstate the Docmd...it works (limited to one checkbox attachment). Also, I am not sure I am implementing Arne's code correctly ( and Im probably not) and that may be the entire issue all together ;)

Thank you both for your invaluable expertise.
-mgm
 

Attachments

  • 21.PNG
    21.PNG
    42.4 KB · Views: 135
  • 22.PNG
    22.PNG
    9.6 KB · Views: 129
  • 23.PNG
    23.PNG
    57 KB · Views: 129

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:10
Joined
May 7, 2009
Messages
19,175
the "yesNoField" is a place holder. Replace it with the correct Yes/No field of table tbl_Attachments.
 

mgmercurio

Member
Local time
Today, 10:10
Joined
Jan 18, 2021
Messages
55
the "yesNoField" is a place holder. Replace it with the correct Yes/No field of table tbl_Attachments.
Oh myyyyyyyyyy ...YES! That was it..... wow....just a dumb mistake......sorry....and thank you!!! It works perfectly.
 

Users who are viewing this thread

Top Bottom