Send Email with multiple attachments using several fields as file name. All in same directory.

[pbaldy]

In the VBA editor, go into Tools/References and check the MS Outlook library.

[kierandobson]

In the VBA editor, go into Tools/References and check the MS Outlook library.

Thanks, but now I am getting a new error?

Thanks again

[June7]

Ooops. I did not test or even run debugger on code. My Bad.

Instead of Wend, use Loop or remove 'Do' so the While...Wend will work. I am always getting those mixed up.

Review http://www.worldbestlearningcenter.c...2010-Loops.htm

[kierandobson]

Ooops. I did not test or even run debugger on code. My Bad.

Instead of Wend, use Loop or remove 'Do' so the While...Wend will work. I am always getting those mixed up.

Review http://www.worldbestlearningcenter.c...2010-Loops.htm

Excellent, this works!

Now, sometimes, the files have not saved so occasionally the file it is looking for may not be there. All I need now is some form of error handling within the loop that will simply move onto the next record if said file does not exist. I have tried a few methods on google but not getting anywhere. What and where do I need in this code to do this?

Private Sub EndDay_Click()
        Dim appOutLook As Outlook.Application, mailOutLook As Outlook.MailItem, rs As DAO.Recordset
    Set appOutLook = CreateObject("Outlook.Application")
    Set mailOutLook = appOutLook.CreateItem(olMailItem)
    Set rs = CurrentDb.OpenRecordset("SELECT SubNo FROM SubconListT WHERE OrderDate = Date();")
    With mailOutLook
        Do While Not rs.EOF
            .Attachments.Add "C:\Users\Ryan\Desktop\" & rs!SubNo & ".pdf"
            rs.MoveNext
        Loop
        .To = "k.dobson93@gmail.com"
        .Subject = "subject text here"
        .HTMLBody = "message body here"
        .Display
    End With
End Sub

Finally getting somewhere, thanks for all your help

[June7]

If Dir("C:\Users\Ryan\Desktop" & rs!SubNo & ".pdf") <> "" Then .Attachments.Add "C:\Users\Ryan\Desktop" & rs!SubNo & ".pdf"

[kierandobson]

If Dir("C:\Users\Ryan\Desktop" & rs!SubNo & ".pdf") <> "" Then .Attachments.Add "C:\Users\Ryan\Desktop" & rs!SubNo & ".pdf"

Thanks, no error message but now there are no attachments on the email. I feel we are on the right track though.

Thanks again

[June7]

Google: Access vba references early late binding

Here is one:

http://excelmatters.com/2013/09/23/v...-late-binding/

The disadvantage to late binding is don't get IntelliSense popup help. So build your code with early binding and then if you want to distribute db with late binding just change the Dim statements and remove the library reference before distribution.

[kierandobson]

Ah my bad, it does work. Now for hopefully my final question.

The query I am basing this off is paramater based, and the paramater is selected from a combo box in a form. I know this involves query defs and such but how would I slot this in to my code? The combo box is in a form called MainMenuF. The combo box is labelled SupplierLookup and the parameter is all of my different suppliers.

Private Sub EndDay_Click()

        Dim appOutLook As Outlook.Application, mailOutLook As Outlook.MailItem, rs As DAO.Recordset
    Set appOutLook = CreateObject("Outlook.Application")
    Set mailOutLook = appOutLook.CreateItem(olMailItem)
    Set rs = CurrentDb.OpenRecordset("SELECT SubNo FROM SupplierLookupQ WHERE OrderDate = Date();")
    With mailOutLook
        Do While Not rs.EOF
            If Dir("C:\Users\Ryan\Desktop\" & rs!SubNo & ".pdf") <> "" Then .Attachments.Add "C:\Users\Ryan\Desktop\" & rs!SubNo & ".pdf"
            rs.MoveNext
            Loop
        .To = ""
        .Subject = "Orders Raised Today"
        .HTMLBody = "Hello <br> <br> Please see attached all of the orders we have raised today with you. You will be receiving these the next working day. This is a new system I am trying out so you are aware of what you will be receiving off us, and perhaps any necessary preparation can be made prior to you receiving these orders. If you cannot hit the date requested on the Purchase Order, I would be grateful if you could advise me at the earliest opportunity. To follow this up I will send over a list of all of our orders with you once a week, as I am aware that things change and a date may have to change for whatever reason. You will be scored based on you meeting the dates you have told us that you will meet. If we have no update, you will be scored against the date we requested. <br> <br> My aim with this new system is to make both of our jobs easier, and to work more fluently together. <br> <br> Many Thanks. <br> <br> Kieran"
        .Display
    End With
End Sub

[June7]

I never user dynamic parameterized queries.

So SupplierLookupQ is a query. Post its SQL statement.

Where is the email procedure located?

[kierandobson]

I never user dynamic parameterized queries.

So SupplierLookupQ is a query. Post its SQL statement.

Where is the email procedure located?

I am not sure what you mean about the email procedure, but the SQL code is as follows:

SELECT SubconListT.Supplier, SubconListT.Explanation, SubconListT.SubNo, SubconListT.WorksOrder, SubconListT.DueDate, SubconListT.DateModified, SubconListT.TimeModified, SubconListT.PartNo, SubconListT.Customer, SubconListT.QTY, SubconListT.Notes, SubconListT.[Collected/Despatched], SubconListT.ProjectedCompletionDate, SubconListT.OrderDate, SupplierListT.SupplierNotes, SupplierListT.Contact, SupplierListT.ContactNo, SupplierListT.SupplierID, SubconListT.JavelinUpdate
FROM SupplierListT INNER JOIN SubconListT ON SupplierListT.SupplierID = SubconListT.Supplier
WHERE (((SupplierListT.SupplierID)=[Forms]![MainMenuF]![SupplierLookup]))
ORDER BY SubconListT.Supplier, SubconListT.SubNo;

[kierandobson]

I never user dynamic parameterized queries.

So SupplierLookupQ is a query. Post its SQL statement.

Where is the email procedure located?

The email procedure is located in a form called LookupF. Apologies, I think I have just understood your last reply.

[June7]

Set rs = CurrentDb.OpenRecordset("SELECT SubNo FROM SubconListT WHERE Supplier=" & [Forms]![MainMenuF]![SupplierLookup] & " AND OrderDate = Date();")

This will, of course, pull records only for the current day. If you want to be able to pull for any date, will have to modify this code to refer to a control on form where user can specify date.

[Homegrownandy]

answered - didn't see

[kierandobson]

Set rs = CurrentDb.OpenRecordset("SELECT SubNo FROM SubconListT WHERE Supplier=" & [Forms]![MainMenuF]![SupplierLookup] & " AND OrderDate = Date();")

This will, of course, pull records only for the current day. If you want to be able to pull for any date, will have to modify this code to refer to a control on form where user can specify date.

This is exactly what I want! It works perfectly, thank you very much for all your help.