sigh... attachments

[Ruegen]

docmd.sendobject won't allow you to add attachments will it?

I have to reference the outlook library don't I?

I'm pretty much locked into the staff using outlook to add attachments to an email yes?

[ItsMe]

You should not need any additional references using DoCmd. This is kind of a cross post. Add the Docmd.OpenReport within your DAO loop. RThen you can Docmd.SendObject. Open Report using Where Criteria if needbe

After you SendObject, you need to close report. Docmd likes to focus on one object at a time. So, Open, Send, Close within the loop.

[Ruegen]

I won't be sending reports but rather attachments outside of access, like a pdf or word document.

[ItsMe]

You can use CDO to email attachments. You do not need an Email client installed. Looks like I read the question wrong. Do you want to avoid Docmd?

[Ruegen]

You can use CDO to email attachments. You do not need an Email client installed. Looks like I read the question wrong. Do you want to avoid Docmd?

If that allows me to bring in files as attachments

[ItsMe]

Post # 7 has an example. If you need secure socket layer (SSL) you will need an additional line of code (schema)
https://www.accessforums.net/access/...ess-38401.html

[Ruegen]

Post # 7 has an example. If you need secure socket layer (SSL) you will need an additional line of code (schema)
https://www.accessforums.net/access/...ess-38401.html

Thanks

I got it working the way I like

My code is (if you spot something I should know that would be kind).

Private Sub Command87_Click()
If IsNull(Me.txtMessageBody) Or IsNull(Me.txtMessageBody) Or IsNull(Me.Text38) Then
MsgBox "Please ensure that you have the type of school, year, show and email message filled, once they have been selected as the minimum requirment for this form to work you can then email."
Else






Me.Filter = "[Area] Like '*" & Me.Text36 & "*'" & IIf(Not IsNull(Me.Text38), " and SchoolTypeID=" & Me.Text38, "") & IIf(Not IsNull(Me.txtStates), " and StateID=" & Me.txtStates, "")
Me.FilterOn = True




Dim attch1 As String
Dim attch2 As String
Dim messagebody As String
Dim emsubject As String
Dim emailcont As String
Dim thecount As String
Dim mresponse As Integer
Dim emailofsc As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem


Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)










Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone


If IsNull(Me.txtPath1) Then
Else
attch1 = Me.txtPath1
End If
If IsNull(Me.txtPath2) Then
Else
attch2 = Me.txtPath2
End If


rs.MoveFirst
rs.MoveLast
thecount = rs.RecordCount




messagebody = Me.txtMessageBody
emsubject = Me.txtSubject






If thecount <= 0 Then
Set rs = Nothing
Exit Sub
End If


mresponse = MsgBox("Are you sure you want to email " & thecount & " contacts?", vbYesNo, "Continue")


If mresponse = vbYes Then




rs.MoveFirst
Do Until rs.EOF






If Me.Frame63 = 1 Then
    If IsNothing(rs![1ContactEmail]) Then
    emailofsc = "noemail"
        Else
    emailofsc = rs![1ContactEmail]
    End If
ElseIf Me.Frame63 = 2 Then
  If IsNothing(rs![6EnglishEmail]) Then
    emailofsc = "noemail"
    Else
    emailofsc = rs![6EnglishEmail]
    End If
ElseIf Me.Frame63 = 3 Then
  If IsNothing(rs![2LibrarianEmail]) Then
    emailofsc = "noemail"
    Else
    emailofsc = rs![2LibrarianEmail]
    End If
ElseIf Me.Frame63 = 4 Then
  If IsNothing(rs![4MusicEmail]) Then
    emailofsc = "noemail"
    Else
    emailofsc = rs![4MusicEmail]
    End If
ElseIf Me.Frame63 = 5 Then
  If IsNothing(rs![3DramaEmail]) Then
    emailofsc = "noemail"
    Else
    emailofsc = rs![3DramaEmail]
    End If
ElseIf Me.Frame63 = 6 Then
  If IsNothing(rs![5WelfareEmail]) Then
    emailofsc = "noemail"
    Else
    emailofsc = rs![5WelfareEmail]
    End If
ElseIf Me.Frame63 = 7 Then
  If IsNothing(rs![SchoolEmail]) Then
    emailofsc = "noemail"
    Else
    emailofsc = rs!SchoolEmail
    
    End If
End If
If emailofsc = "noemail" Then
rs.MoveNext
Else
On Error GoTo errHandler
'open Outlook, attach zip folder or file, send e-mail
With MailOutLook
    .BodyFormat = olFormatHTML
    .To = "email@email.com.au"
    ''.cc = ""
    ''.bcc = ""
    .Subject = msubject & " " & emailofsc
    .HTMLBody = messagebody
    If IsNothing(attch1) Then
    Else
    Set myAttachments = MailOutLook.Attachments
    myAttachments.Add (attch1)
    End If
     If IsNothing(attch2) Then
    Else
    Set myAttachments = MailOutLook.Attachments
    myAttachments.Add (attch2)
    End If
    .DeleteAfterSubmit = False 'This would let Outlook send the note without storing it in your sent bin
    .ReadReceiptRequested = True
    .send
End With
rs.MoveNext
End If
Loop
MsgBox "all done"


Else
MsgBox "You have cancelled emailing"
End If




rs.Close
Set rs = Nothing










End If


errHandler:
Select Case Err
Case 440
MsgBox "The attachment path for your email you have is missing a file - please check and try again."


End Select








End Sub

[ItsMe]

Does the filter work? You are assigning empty strings if null. If your code comes across these empty strings at a later time, will it know how to handle it?

The loop will work. You might get better performance querying the valid/relevant records prior to iterating through them. But it seems good.

What is going on with your error handler? I did not see an On error go to line. So there are some more lines you will need for your error handler... resume, exit, etc.

have you found a way to test it without emailing the entire world?

[Ruegen]

Does the filter work? You are assigning empty strings if null. If your code comes across these empty strings at a later time, will it know how to handle it?

The loop will work. You might get better performance querying the valid/relevant records prior to iterating through them. But it seems good.

What is going on with your error handler? I did not see an On error go to line. So there are some more lines you will need for your error handler... resume, exit, etc.

have you found a way to test it without emailing the entire world?

Yeah filter works

Which part of empty strings?

On Error GoTo errHandler
'open Outlook, attach zip folder or file, send e-mail
With MailOutLook

error handler is here, if someone puts c: instead of the path it comes up with that error so I put a handler on it.


yeah I test it by pulling in the email into the subject field - if that subject has a different email each time then it is looping correctly (later I change that)

I sent off 600 emails successfully this morning to 600 schools and it worked but that was using docmd.sendobject - which now since using outlook I can do richtext/html and add attachments (as well as read receipts, not necessary on these but essential later for another form).

[ItsMe]

Which part of empty strings?

Your Instant If statements are assigning "" in the case where Not IsNull is false. For instance, if Text38 is Null then it will become ""

Just wondering what effect that may have on your code.

[Ruegen]

Your Instant If statements are assigning "" in the case where Not IsNull is false. For instance, if Text38 is Null then it will become ""

Just wondering what effect that may have on your code.

Where it filters the form, it doesn't filter the form at all - it doesn't affect the code as you have to select text38 in order to do anything - all schools have a type, and the form filters accordingly. since the rs is the current record source what ever the record source is the rs becomes.

for example the messagebody won't appear for edit until you choose text38 (it does a dlookup and makes the text38 visible).

I haven't noticed any problems. *crosses fingers*

[ItsMe]

I think it looks good. That should be a huge help to the office to have that functionality.

[Ruegen]

I think it looks good. That should be a huge help to the office to have that functionality.

They've never had it this good